• 【MySQL 8】Generated Invisible Primary Keys(GIPK)

    从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)在启用它之后，如下所示：

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    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命令查看上面的两个示例表的不同：

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    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。比如：

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    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)将其隐藏起来。

     

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    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后：

     

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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下，则不支持。

     

  • 相关阅读:
    docker 更新升级 nginx
    在 Next.js App目录中使用 Code Hike
    【C++】构造函数和析构函数
    Mybatis源码解析(四)：sql语句及#{}、${}的解析
    IDEA（安装和使用）
    【uni-app】uni-app编译运行，提示app.json:pages不能为空，怎么解决？
    【ROS2原理5】从ROS1到ROS2的变迁
    [Python]装饰器
    Element树形控件使用过程中遇到的问题及解决方法
    制作一个简单HTML电影网页设计（HTML+CSS）
  • 原文地址：https://www.cnblogs.com/abclife/p/16530175.html