从 MySQL 8.0.30 开始,MySQL 支持在 GIPK 模式下运行时生成不可见的主键。在这种模式下运行时,对于任何InnoDB
没有显式主键创建的表,MySQL 服务器会自动将生成的不可见主键(GIPK)添加到表中。本节介绍如何启用和禁用 GIPK 模式,以及CREATE TABLE
启用此模式时生成的主键的行为。
GIPK 模式由
sql_generate_invisible_primary_key
服务器系统变量控制。默认情况下,该变量的值为OFF
,表示禁用GIPK模式;要启用 GIPK 模式,请将变量设置为
ON
. 为了说明 GIPK 模式如何影响表创建,我们首先创建两个相同的表,它们都没有主键,第一个(表
auto_0
)禁用 GIPK 模式,第二个(auto_1
)启用它,如下所示:
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)
的设置
sql_generate_invisible_primary_key
不会被复制,并且会被复制应用程序线程忽略。这意味着副本不会为任何不是使用源上的主键创建的复制表生成主键。
用于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)
由于用于创建它auto_1
的语句没有指定主键CREATE TABLE
,GIPK 模式导致 MySQL 向该表添加不可见列
my_row_id
和该列的主键。由于在auto_0
创建时禁用了 GIPK 模式,因此没有对该表执行此类添加。
当服务器以 GIPK 方式将主键添加到表中时,列名和键名始终为
my_row_id
。因此,当启用GIPK模式时,
my_row_id
除非建表语句还指定了显式主键,否则无法创建列名为named的表。my_row_id
(在这种情况下,
您不需要命名列或键。)
my_row_id
是一个不可见的列,这意味着它不会显示在
SELECT *
or
的输出中TABLE
;该列必须按名称明确选择(请参阅
第 13.1.20.10 节,“不可见的列”)。
当 GIPK 模式生效时,生成的主键不能更改,只能在
VISIBLE
和之间切换INVISIBLE
。要使生成的不可见主键
auto_1
可见,请执行以下
ALTER TABLE
语句:
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)
要使此生成的主键再次不可见,请发出
ALTER TABLE auto_1 ALTER COLUMN my_row_id SET
INVISIBLE
.
GIPK 模式生成的主键默认是不可见的。
每当 GIPK 模式生效时,如果出现以下两种情况之一,则不能删除生成的主键:
该表没有主键。
删除主键,但不删除主键列。
GIPK 模式仅适用于使用
InnoDB
存储引擎的表。它对使用其他 MySQL 存储引擎的表没有影响。您可以使用
ALTER TABLE
语句更改具有生成的不可见主键的表所使用的存储引擎;主键和列保留在原位,但表和键不再接受任何特殊处理。
默认情况下,GIPK 显示在 、 和 的输出中
SHOW CREATE TABLE
,
SHOW COLUMNS
并且
在信息模式和
表SHOW INDEX
中可见。
在这种情况下,您可以通过将系统变量设置为 来隐藏生成的不可见主键
。默认情况下,此变量为,如下所示:
COLUMNS
STATISTICS
show_gipk_in_create_table_and_information_schema
OFF
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)
从以下针对
COLUMNS
表的查询可以看出,my_row_id
在 的列中可见auto_1
:
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)
show_gipk_in_create_table_and_information_schema
设置为
后
OFF
,表格中
my_row_id
就看不到了,如下图: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)
GIPK 模式支持基于行的复制
CREATE
TABLE ... SELECT
;在这种情况下,为该语句写入二进制日志的信息包括 GIPK 定义,因此可以正确复制。CREATE TABLE ... SELECT
GIPK 模式不支持
基于语句的复制。
创建或导入使用 GIPK 模式的安装备份时,可以排除生成的不可见 PK 列和值。mysqldump的
--skip-generated-invisible-primary-key
选项导致 GIPK 信息被排除在程序的输出中。如果您正在导入包含 GIPK 键和值的转储文件,您还可以使用
mysqlpump来抑制这些(因此不导入)。
--skip-generated-invisible-primary-key