背景

在云数据库 MySQL 版 20240615 及之后的内核小版本中， MySQL 8.0 默认开启隐藏主键（Generated Invisible Primary Keys，简称 GIPK）特性。开启该特性后，若在用户的 create table 语句中未识别到主键（Primary Key），则会为用户添加一个隐藏的主键 my_row_id。添加隐藏主键后，如果用户想使用 DDL 自定义新主键，可能会遇到以下报错：

• 在 ALTER TABLE 语句中定义新的主键时，可能会遇到以下报错：

  Error Message	说明
  Error 1068: Multiple primary key defined.	自定义主键与隐藏主键冲突。
  Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.	新自增列与隐藏主键冲突。

• 在 CREATE TABLE 语句中定义新的主键时，可能会遇到以下报错：

  Error Message	说明
  Error 4109: Failed to generate invisible primary key. Auto-increment column already exists.	开启 GIPK 时，自增列未定义为主键索引。

查看 GIPK

因为 GIPK 有“隐藏列”属性，用户无法直接通过 show create table 查看到 GIPK，需要按照以下方法查看 GIPK 。

1. 打开 show_gipk_in_create_table_and_information_schema 参数，即可在 show create table 中看到隐藏主键 my_row_id。

   mysql> set show_gipk_in_create_table_and_information_schema=on;
   
   mysql> show variables like '%gipk%';
   +--------------------------------------------------+-------+
   | Variable_name                                    | Value |
   +--------------------------------------------------+-------+
   | show_gipk_in_create_table_and_information_schema | ON    |
   +--------------------------------------------------+-------+
   1 row in set (0.01 sec)
   
   mysql> show create table t00;
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                  |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t00   | CREATE TABLE `t00` (
     `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
     `id` int DEFAULT NULL,
     `c` varchar(16) DEFAULT NULL,
     PRIMARY KEY (`my_row_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
   +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.01 sec)
   

2. 替换主键不需要关闭 GIPK，保持开启即可。

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

将 GIPK 替换为自定义主键

1. 将已有列升级为主键

操作描述： 将原表中已经含有的一个字段设置为主键。
报错 SQL 示例：

alter table table_name add primary key(id);

报错详情：Error 1068: Multiple primary key defined
变更前表结构示例： 含有隐藏主键列 my_row_id

mysql> show create table t00;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t00   | CREATE TABLE `t00` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

变更 SQL ：

• MySQL 8.0

alter table t00 drop column my_row_id, add primary key(id);

• MySQL 5.7 (20240615 及更新内核小版本的实例)

alter table t00 drop column byte_rds_ai_uk_row_id, add primary key(id);

变更后表结构： 隐藏主键 my_row_id 被移除，id 为新主键

mysql> show create table t00;
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| t00   | CREATE TABLE `t00` (
  `id` int NOT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 新加一个主键列

操作描述：新增一列作为主键。
报错 SQL 示例：

alter table table_name 
            add column new_id bigint primary key;

相关报错：Error 1068: Multiple primary key defined
变更前表结构示例：含有隐藏主键列 my_row_id

mysql> show create table t01;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01   | CREATE TABLE `t01` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `i` int DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

变更 SQL：
加列并替换 GIPK，新列无额外要求，这里展示新加一个 BIGINT 属性的 new_id 列。

• MySQL 8.0

alter table table_name 
            drop column my_row_id, 
            add column new_id bigint primary key;

• MySQL 5.7 (20240615 及更新内核小版本的实例)

alter table table_name 
            drop column byte_rds_ai_uk_row_id, 
            add column new_id bigint primary key;

变更后表结构： 隐藏主键my_row_id被移除，new_id 为新主键。

mysql> show create table t01;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01   | CREATE TABLE `t01` (
  `i` int DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  `new_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`new_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. 新增自增列

操作描述：用户新增一个自增列
报错 SQL示例：

alter table table_name 
            add column new_id bigint unsigned not null auto_increment key;

报错详情：Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key
变更前表结构示例：含有隐藏主键列 my_row_id

mysql> show create table t01;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01   | CREATE TABLE `t01` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `i` int DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

变更 SQL：
加自增列 new_id 并替换 GIPK。

• MySQL 8.0

alter table table_name 
            drop column my_row_id, 
            add column new_id bigint unsigned not null auto_increment key;

• MySQL 5.7 (20240615 及更新内核小版本的实例)

alter table table_name 
            drop column byte_rds_ai_uk_row_id, 
            add column new_id bigint unsigned not null auto_increment key;

变更后表结构： 隐藏主键my_row_id被移除，new_id为新主键

mysql> show create table t01;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01   | CREATE TABLE `t01` (
  `i` int DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  `new_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`new_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. 创建含有自增列的表

操作描述：创建一个含有自增列的表
报错 SQL 示例：

create table table_name (
             id bigint unsigned NOT NULL AUTO_INCREMENT [UNIQUE KEY],
             c varchar(16));

错误详情：Error 4109: Failed to generate invisible primary key. Auto-increment column already exists.
变更 SQL：
使用 PRIMARY KEY 或 KEY 关键字

create table table_name  (
             id bigint unsigned NOT NULL AUTO_INCREMENT [PRIMARY] KEY,
             c varchar(16));

变更后表结构： new_id 为新主键

mysql> show create table t01;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t01   | CREATE TABLE `t01` (
  `new_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`new_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5. 打开 GIPK 创建分区表

操作描述： 创建一个无主键的分区表
报错 SQL 示例：

CREATE TABLE t1 (f1 INT, f2 DATE) PARTITION BY KEY(f2) PARTITIONS 2;

错误详情： Error 1235: This version of MySQL doesn't yet support 'generating invisible primary key for the partitioned tables
变更操作： 提工单关闭 GIPK。