MySQL 8.0 新特性之不可见主键_sql_generate_invisible_primary_

数据库设计通常需要满足一定的范式要求，其中主键更是最基本的要求。不过，数据库管理系统却允许我们创建没有主键的表。这样的表在 MySQL 中会带来查询性能低下、复制延迟甚至无法实现高可用配置等问题。

为此，MySQL 8.0.30 版本引入了一个新的功能，叫做不可见主键（Generated Invisible Primary Keys），它可以自动为没有显式指定主键的 InnoDB 表创建一个不可见的主键。

不可见主键

MySQL 通过系统变量 sql_generate_invisible_primary_key 控制是否启用 GIPK 特性，该变量的默认设置为 OFF。

以下示例创建了两个表，都没有指定主键。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> SET sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

创建 auto_0 表时，sql_generate_invisible_primary_key 设置为 OFF；创建 auto_1 表时，sql_generate_invisible_primary_key 设置为 ON。

使用 SHOW CREATE TABLE 语句查看两个表的区别：

mysql> SHOW CREATE TABLE auto_0\G
*************************** 1. row ***************************
       Table: auto_0
Create Table: CREATE TABLE `auto_0` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MySQL 自动为 auto_1 创建了一个不可见字段 my_row_id，并且将其设置为主键。

不可见字段也叫做隐藏字段，是 MySQL 8.0.23 版本增加的新功能。作为不可见字段，my_row_id 不会出现在 SELECT * 或者 TABLE 语句的结果中，查询该字段必须显式指定它的名字。

这种情况下，系统默认增加的不可见主键字段名称固定为 my_row_id，因此我们不能在创建表时指定其他字段名为 my_row_id，除非同时将其指定为主键。

修改属性

当我们启用了 GIPK 功能时，不可见主键 my_row_id 不能使用 ALTER TABLE 语句进行修改，只能设置它的可见属性。以下命令将 auto_1 表的不可见主键设置为可见字段：

mysql> ALTER TABLE auto_1 ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

当我们启用了 GIPK 功能时，如果删除不可见主键会导致以下任意情况发生，都不允许删除不可见主键：

• 该表没有主键；
• 删除主键而保留主键字段。

另外，GIPK 功能只支持 InnoDB 存储引擎，当我们使用 ALTER TABLE 语句修改这种表的存储引擎时，仍然会保留字段和主键约束，但是它会变成普通的主键字段。

字典信息

默认情况下，SHOW CREATE TABLE、SHOW COLUMNS 以及 SHOW INDEX 命令都会显示不可见主键信息。同时，information_schema 数据库中的 COLUMNS 和 STATISTICS 表中也包含了不可见主键字段。这一行为可以通过系统变量 show_gipk_in_create_table_and_information_schema 进行控制，默认值为 ON。

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

此时，我们查询系统表 COLUMNS 可以返回 auto_1 中的不可见主键 my_row_id：

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| my_row_id   |                1 | bigint    | PRI        |
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

如果将系统变量 show_gipk_in_create_table_and_information_schema 设置为 OFF，再次查询系统表 COLUMNS，不会显示不可见主键字段：

mysql> SET show_gipk_in_create_table_and_information_schema = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

复制与备份

系统变量 sql_generate_invisible_primary_key 的配置不会被复制，复制应用线程会忽略该变量。这就意味着源节点的设置不会对副本产生影响。从 MySQL 8.0.32 版本开始，我们可以在 CHANGE REPLICATION SOURCE TO 语句使用 REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE 选项设置副本节点启用 GIPK 功能，为指定复制渠道中的那些没有主键的表自动增加不可见主键。

对于 CREATE TABLE … SELECT 语句，GIPK 支持基于行的复制选项，此时二进制日志中包含了 GIPK 定义，可以正确地复制。如果是基于语句的复制，CREATE TABLE … SELECT 不支持 sql_generate_invisible_primary_key = ON。

如果启用了 GIPK 功能，使用 mysqldump 备份或者还原数据时，可以通过 --skip-generated-invisible-primary-key 选项排除 GIPK 信息。