19

【MySQL 8】Generated Invisible Primary Keys(GIPK) - abce

 2 years ago
source link: https://www.cnblogs.com/abclife/p/16530175.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

【MySQL 8】Generated Invisible Primary Keys(GIPK)

2022-07-28 21:43  abce  阅读(62)  评论(0)  编辑  收藏  举报

从MySQL 8.0.30开始，MySQL支持在GIPK模式下运行时生成不可见的主键。在这种模式下运行时，对于任何在没有显式主键的情况下创建的InnoDB表，MySQL服务器会自动将生成的不可见主键 (GIPK) 添加到表中。

GIPK模式由sql_generate_invisible_primary_key系统变量控制。默认情况下，该变量的值为OFF，表示禁用GIPK模式；要启用GIPK模式，请将变量设置为ON。 

为了说明GIPK模式如何影响表创建，我们首先创建两个相同的表，它们都没有主键，第一个(table 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变量的设置不会被复制，会被复制的applier线程忽略。这就意味着复制不会为主库没有主键的表生成主键。

使用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没有主键，GIPK模式为表增加了一个不可见的my_row_id列，并作为主键。而在创建表auto_0的时候，禁用了GIPK模式，所以没有生成额外的主键列。

GIPK模式增加的主键，列和key名字总是my_row_id。因此，开启了GIPK模式，用户就不可以列名字包含my_row_id的表，除非表已经显式创建了主键。

my_row_id列是不可见的列，使用select * 是不能查看到的。

在GIPK模式生效后，不能修改my_row_id，只能修改状态visible或invisible。比如：

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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> ALTER TABLE auto_1 ALTER COLUMN my_row_id SET INVISIBLE;
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 /*!80023 INVISIBLE */,
`c1` varchar(50) DEFAULT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql>

默认是不可见的。

GIPK模式只是对InnoDB有效。

缺省，show create table、show columns、show index都可以显示GIPK；在information_schema.columns和information_schema.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)
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)

设置成off后：

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模式支持在row-based下的create table ... select，在该情况下，该语句生成的binlog日志包含了GIPK信息，因而不会影响复制。如果是Statement-based下，则不支持。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK