MySQL8.0学习记录17 -Create Table

这里主要列举一些以前没有注意过的地方：

不可见列

不可见列通常对查询是隐藏的，但如果显式引用，可以被访问。

create table t_visible(
	a int,
	b int invisible
);

select * from t_visible ;
1
2
3
4
5
6

a|
-+
1
2

可以通过show columns from t_visible;查看Extra列来判断是否是隐藏列。通过select * from information_schema.COLUMNS c where EXTRA = ‘INVISIBLE’;可以查看所以得隐藏列。

创建临时表

CREATE TABLE会导致隐式提交，但是创建临时表并不会。

• 要创建一个临时表，必须有CREATE TEMPORARY TABLES权限。在一个会话创建了一个临时表后，服务器不再对该表进行权限检查。即使当前用户没有创建临时表的权限，会话也可以操作其临时表。比如，高权限的存储过程创建临时表之后，当前用户依然可以访问临时表。

CREATE TABLE LIKE

CREATE TABLE LIKE创建一个基于原始表的定义的空表，包括在原始表中定义的任何列属性和索引。

• 会保留生成列信息
• 会保留表达式的的默认值
• 会保留了原表的CHECK约束，只是所有的约束名称都被重新生成
• 如果原表是临时表，除非加关键字TEMPORARY ，否则新表不会是临时表

CREATE TABLE … SELECT

我们常见的SELECT语句创建表是这样的：

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
1

其实还可以先指定一部分列：

create table t_create_t0(
	a int
);
insert into t_create_t0 values(1);

create table t_create_t1(b int default 5) as select a from t_create_t0;

select * from t_create_t1;
1
2
3
4
5
6
7
8

b|a|
-+-+
5|1|
1
2
3

从MySQL 8.0.19开始，在CREATE TABLE … SELECT语句中可以使用VALUES语句作为一张表，这是一个很有用的功能。比如，有下面一张表t_book表：

select * from t_book tb ;
1

ID|NAME  |
--+------+
 0|Python|
 2|Java  |
1
2
3
4

给定一组用户名，希望将每个用户与全部的book数据关联并输出：

select t.*,tb.name from (values ROW('u1','上海'), row('u2','北京')) as t(name,address) 
join t_book tb
1
2

name|address|name  |
----+-------+------+
u2  |北京     |Python|
u1  |上海     |Python|
u2  |北京     |Java  |
u1  |上海     |Java  |
1
2
3
4
5
6

CREATE TABLE … SELECT并不会自动创建索引，但可以在CREATE TABLE 部分指定列属性，也可以创建索引。
CREATE TABLE … SELECT也不会保留生成列信息，但可以保留原表的表达式默认值。

CREATE TABLE … SELECT不会保留AUTO_INCREMENT ，但是会保留 NULL (NOT NULL) 、 CHARACTER SET, COLLATION, COMMENT以及DEFAULT属性。

在MySQL8.0.21前，表的创建与数据的插入是两部分。所以数据插入失败新表依然会创建，但是新的版本是一个事务，数据插入失败，创建的表也会被回滚。

外键

外键约束名称必须是数据库内唯一的，如果不指定的话，那么会自动生成。
外键的使用有很多约束：

• 外键的创建需要在父表上有REFERENCES权限
• 父表和子表必须使用相同的存储引擎，并且它们不能被定义为临时表
• 外键和引用键中对应的列必须有类似的数据类型，字符串的长度可以不同，但是字符集和校对规则必须是相同的。
• 外键和引用列可以是同一张表上的，这个在应该是在自引用查询中有用
• 外键列和引用列都必须有索引。在建立外键的时候，外键不存在索引的话，会创建索引。如果先建立外键，然后再建索引，这时候会悄悄删掉外键自己建的索引：

  create table t_parent(
  	id int primary key,
  	name varchar(100) ,
  	index idx_p_name (name)
  );
  
  create table t_child(
  	id int primary key,
  	name varchar(100),
  	p_name varchar(100),
  	FOREIGN KEY(p_name) REFERENCES t_parent(name) on DELETE CASCADE
  );
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

  这时候看下t_child索引：

  Table  |Non_unique|Key_name|Seq_in_index|Column_name|Collation
  -------+----------+--------+------------+-----------+---------
  t_child|         0|PRIMARY |           1|id         |A        
  t_child|         1|p_name  |           1|p_name     |A        
  1
  2
  3
  4

  然后新增一个索引再看看, 可以看到上面p_name索引居然消失了！

  alter table t_child  add index nfk_p_name(p_name);
  1

  Table  |Non_unique|Key_name  |Seq_in_index|Column_name
  -------+----------+----------+------------+-----------
  t_child|         0|PRIMARY   |           1|id         
  t_child|         1|nfk_p_name|           1|p_name     
  1
  2
  3
  4

• 外键列上的前缀索引还不支持，所以太大varchar不能用来做外键，还有BLOB以及TEXT
• InnoDB目前不支持具有用户定义的分区的表的外键
• 一个外键约束不能引用一个虚拟生成的列

外键的参照动作：

• CASCADE：从父表中删除或更新行，并自动删除或更新子表中的匹配行。ON DELETE CASCADE和ON UPDATE CASCADE都被支持。
• SET NULL：从父表中删除或更新记录，并将子表中的外键列设置为NULL。
• RESTRICT。拒绝对父表进行删除或更新操作。指定RESTRICT（或NO ACTION）与省略ON DELETE或ON UPDATE子句相同。
• NO ACTION：标准SQL的一个关键字。在MySQL中，相当于RESTRICT。
• SET DEFAULT：可以被MySQL解析器识别，但InnoDB和NDB都拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。也就是不能用。

外键检查是由foreign_key_checks变量控制的，这个默认是启用的。但是有时候是需要禁用的，禁用的好处有：

• 正常被引用的表不能被删除，禁用检查以后可以删
• mysqldump中自动包含禁用外键检查，可以按照任意顺序加载表，还能加快导入操作的速度

外键禁用后，仍然存在禁止的操作：

• 重新创建一个以前被放弃的表（表的定义不符合引用该表的外键约束）会返回一个错误
• 如果改变表会导致外键定义不正确，那么会出错
• 不能删除外键约束要求的索引，除非先删除外键约束

需要注意的是，禁用外键检查后，删除父表是被允许的，而且子表的外键约束自动删除。

查询已有外键的方式：

// 查询列是外键列：
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
       
// 查询外键,会给出引用表与列
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;

//查询表的外键列
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS
1
2
3
4
5
6
7
8
9
10

检查约束

在MySQL 8.0.16以前，检查约束的语法比较简单：

CHECK (expr)
1

MySQL8.0.16之后, 语法更加丰富：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
1

约束的名称（symbol）不指定的话，数据库会自动生成一个，名称的最大长度为64个字符，区分大小写但不区分重音。

约束条件（expr）需要指定为一个布尔表达式，表达式的值必须是TRUE或UNKNOWN（对NULL来说）。

默认约束具有ENFORCED属性，既强制执行。可以改为NOT ENFORCED，不强制执行约束。

约束可以是针对列的，也可以是针对表的。列的约束出现在一个列的定义中，并且只能参考该列。表约束不出现在列的定义中，它可以引用任何表的一个或多个列，允许向前引用出现在表定义后面的列。

之前说约束的名称是数据库内唯一的，但是有个例外，一个TEMPORARY表会隐藏一个同名的非TEMPORARY表，所以它也可以有相同的CHECK约束名称。这里提到TEMPORARY表可以重名以前不知道，验证一下：

create table t_not_tmp(
	id int primary key,
	c1 int CONSTRAINT `c1_positive` CHECK ((`c1` > 0))
);

create temporary table t_not_tmp(
	i1 int ,
	i2 int CONSTRAINT `c1_positive` CHECK ((`i2` > 0))
);

select * from t_not_tmp;
1
2
3
4
5
6
7
8
9
10
11
12

i1|i2|
--+--+
1
2

CHECK条件表达式必须遵守的规则：

• 非生成的和生成的列都是允许的，具有AUTO_INCREMENT属性的列不行
• 表达式可以使用字面量、已经结果确定的内建函数和操作。像connection_id(), current_user(), now()结果不确定的不行
• 不允许使用变量（系统变量、用户定义的变量和存储的程序局部变量）
• 不允许使用子查询
• 不允许使用自己创建的函数
• 不允许使用存储过程
• CHECK约束与外键的动作（ON UPDATE, ON DELETE）互斥，不能同时使用

对INSERT IGNORE, UPDATE IGNORE, LOAD DATA … IGNORE, 和 LOAD XML … IGNORE 语句，如果约束检查失败，则会出现警告，并跳过违规行的插入或更新。没有IGNORE的语句，约束检查失败的时候会报错。

需要注意：如果约束表达式中，出现与声明的列类型不同的数据类型，那么根据通常的MySQL类型转换规则，会发生对声明类型的隐式转化。

创建语句的一些隐含变化

• 主键或主键的一部分一定会被声明为 NOT NULL
• ENUM和SET成员值的尾部空格会被自动删除
• MySQL将其他SQL数据库供应商使用的某些数据类型映射为MySQL类型，比如BOOL类型，在MySQL中是TINYINT
• USING 限定索引类型的时候，如果当前数据库引擎不支持，那么会使用可以使用的索引类型
• 非严格模式下，长度规格大于65535的VARCHAR列会被转换为TEXT，而长度规格大于65535的VARBINARY列会被转换为BLOB
• 字符数据类型指定CHARACTER SET二进制属性会导致列被创建为相应的二进制数据类型。CHAR变成BINARY，VARCHAR变成VARBINARY，TEXT变成BLOB。

生成列

可以依据一个现有列的表达式，添加一个新的列，比如：

create table t_v_c(
	first_name varchar(20),
	last_name varchar(20),
	full_name varchar(40) as (concat(first_name,' ', last_name)),
	dateOfBirth datetime,
	birthday varchar(5) as (date_format(dateOfBirth, '%m-%d')) STORED
);
insert into t_v_c(first_name,last_name,dateOfBirth) values('A','B','2020-01-02');
select * from t_v_c;
1
2
3
4
5
6
7
8
9

first_name|last_name|full_name|dateOfBirth        |birthday|
----------+---------+---------+-------------------+--------+
A         |B        |A B      |2020-01-02 00:00:00|01-02   |
1
2
3

生成列完整的定义语法：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
1
2
3
4

• GENERATED ALWAYS，这个是可以省略的，但是建议带上，更加直观表示是生成列
• VIRTUAL | STORED，VIRTUAL 表示不需要额外存储，而是在读取的时候计算（在任何BEFORE触发器之后），可以在虚拟列上建立二级索引；STORED表示需要存储下来，在行插入或者更新的时候计算，可以建立索引。默认是VIRTUAL
• NOT NULL | NULL, 字面意义上就可以理解，即便是VIRTUAL 列NOT NULL, 如果插入时最终的表达式为null也会报错
• UNIQUE [KEY]：建立唯一索引，VIRTUAL 也可以，如果在插入失败时，仍然会报错
• [PRIMARY] KEY：建立主键索引，只有STORED的生成列才可以

生成列的表达式要求：

• 字面量、结果确定的内建函数和操作符是可以的
• 自定义的函数式不行的
• 存储过程和函数参数是不允许的
• 不允许使用变量
• 不允许使用子查询
• 生成列可以引用任何位置的普通列，但是不能引用在其后面定义的其他生成列，也就是说引用生成列时顺序是很重要的
• 生成列不能使用AUTO_INCREMENT属性，可以理解
• 具有AUTO_INCREMENT列也不能被生成列引用，这个没想明白，先记下来

外键约束不能引用一个虚拟生成的列，虚拟列也不能添加外键约束，但是STORED生成列可以加上外键约束引用其他列，而且其上的外键约束不能使用CASCADE、SET NULL或SET DEFAULT作为ON UPDATE参考动作，也不能使用SET NULL或SET DEFAULT作为ON DELETE参考动作。

生成列有哪些作用呢：

• VIRTUAL 生成列可以作为一种简化和统一查询的方式，比如将复杂的查询条件包装成生成列，这样就可以在多个查询中使用，模块化查询条件
• STORED生成列可以用于复杂的条件，尤其是这些条件在查询的时候的代价很高的话，相等于提前缓存结果
• 生成的列可以模拟功能索引，使用一个生成的列来定义一个函数表达式并为其建立索引。 比如JSON数据类型就可以通过生成列建立索引
• 如果生成的列是有索引的，优化器会识别与列定义相匹配的查询表达式，并在查询执行过程中适当地使用列的索引，即使查询没有直接引用列的名称。举个例子如果生成列c int as （d+1） STORED 上有建立索引，那么只要查询条件中匹配到了生成列的表达式（比如 where d+1 > 9），就可以使用到索引。

InnoDB支持虚拟生成列的二级索引，也叫虚拟索引。虽然虚拟列的值是在查询的时候被计算的，但是当一个二级索引在虚拟生成列上被创建时，生成的列值在索引的记录中被具体化。

在虚拟列上使用二级索引时，由于在INSERT和UPDATE操作过程中在二级索引记录中物化虚拟列值时进行的计算，需要考虑额外的写入成本。但是，文档说在虚拟列上添加或删除一个二级索引是一个 in-place 操作，这意味着应该会比较快。

自动生成的不可见主键

MySQL 8.0.30开始，如果创建表的时候没有指定主键，且开启了GIPK （generated invisible primary key）模式的话，mySQL会自动添加一个不可见的主键。
GIPK的开关由sql_generate_invisible_primary_key 变量控制，生成的不可见列将是这样的：

 `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  PRIMARY KEY (`my_row_id`)
1
2

my_row_id是固定的，所以该模式下，尽量不要给一个没有主键的表使用这个名称作为列名。

当GIPK模式生效的时候，除了在可见和不可见之间切换之外，不能改变生成的主键。

由于是不可见列，所以select * 查询不到，需要明确指明这一列才可以查出来。