龙空技术网

「MySQL 8」Generated Invisible Primary Keys(GIPK)

Java互联网技术栈 143

前言:

现在各位老铁们对“vs2017mysql”大约比较关怀,朋友们都想要了解一些“vs2017mysql”的相关文章。那么小编在网上收集了一些关于“vs2017mysql””的相关内容,希望看官们能喜欢,大家一起来了解一下吧!

从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_0Create Table: CREATE TABLE `auto_0` (  `c1` varchar(50) DEFAULT NULL,  `c2` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> SHOW CREATE TABLE auto_1\G*************************** 1. row ***************************       Table: auto_1Create 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_ci1 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: 0mysql> SHOW CREATE TABLE auto_1\G*************************** 1. row ***************************       Table: auto_1Create 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_ci1 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: 0mysql> SHOW CREATE TABLE auto_1\G*************************** 1. row ***************************       Table: auto_1Create 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_ci1 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下,则不支持。

标签: #vs2017mysql