为何要设置sql_require_primary_key参数？

bisal的个人杂货铺 2025-02-06

点击标题下「蓝色微信名」可快速关注

MySQL建表可能会提醒要求表带主键，该操作其实是受到了sql_require_primary_key参数的控制，虽然从范式角度，表应该带主键，但是没太理解它的必要性，技术社群的这篇文章《故障分析 | 为什么 MySQL 8.0.13 要引入新参数 sql_require_primary_key？》给了我们答案，主从复制的场景需要建主键，避免出现主从数据不一致。

1问题描述

客户在测试环境给一批无主键表新增自增主键，发现个别表在新增自增主键后出现了主从数据不一致。

为什么有些表正常而有些表会主从数据不一致呢？

本次故障分析案例，将解释为什么 MySQL 8.0.13 要引入新参数 sql_require_primary_key？

测试环境

MySQL：8.0.25
架构：1 Primary - 1 Secondary

2问题复现

根据客户描述的两种情况分别复现。

新建两张表。

mysql> create table t1(n int,m int);
Query OK, 0 rows affected (0.09 sec)

mysql> create table t2(n int,m int);
Query OK, 0 rows affected (0.01 sec)

在 t1
表中写入两行数据。

mysql> insert into t1 values(1,1),(2,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

在 t2
表里写入两行数据，与 t1
的写入方式区别：会话一先执行后提交，会话二后执行先提交。

分别给 t1
、t2
表新增自增主键。

mysql> alter table t1 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t2 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

分别在主从实例上查询 t1
、t2
表，看数据是否一致。

Primary

Secondary

从上面两图对比可以发现 t1
表主从数据是一致的，t2
表主从数据不一致。

问题复现！

如果是读者遇到这个问题会如何分析呢？

问题分析：

对于以上问题官方文档^[1] 相关说明：

大致意思是说：给表增加自增主键时，主从库表中是否能产生相同的自增值，这取决于主从插入数据的顺序。

如何理解这句话中的“主从插入数据的顺序”？我们还是以上面 t2
表作为例子来解释。会话一先执行后提交，会话二后执行先提交。

执行完以上操作分别在主从查看 t1
表有何不同。

Primary

Secondary

从上图可以发现主库和从库 t2
两条数据的顺序是相反的，为什么会出现这种情况？

因为在 InnoDB 表中，如果表没有指定主键并且表上也无非空唯一键时，InnoDB 会使用内部 RowID 作为数据行的主键，并且 RowID 是在语句执行时已经分配，所以语句先执行的数据行分配到的 RowID 就越小，后执行的语句分配的 RowID 就越大。

上面的测试会话一语句先执行所以分配的 RowID 是较小值，会话二是后执行所以分配的 RowID 是较大值，最终主库看到的顺序是 (1,1),(2,2)
。从库上的情况就刚好和主库相反，因为主库会话一先执行但并没有提交，会话二后执行先提交所以从库接收到的 binlog 顺序是会话二先执行分配的 RowID 更小，会话一后执行分配的 RowID 更大。

在 InnoDB 引擎中数据的排列是按照主键的大小顺序来排列的。最终导致在主从上查询 t2
表出现数据顺序不一致的情况。之后给 t2
添加自增主键时，自增主键的值是按照数据在表中的排列顺序赋值的，所以最终导致主从 t2
的数据不一致。

3问题解决

官方手册中提供了以下语句解决，该方案需要确保操作期间无新增数据，否则会导致新增数据丢失。

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
DROP TABLE t1;
RENAME TABLE t2 TO t1;

4因此

如果表较大，这个方法还是比较耗时的。

最好的方法还是前期建表就建好主键，因此 MySQL 8.0.13 引入参数 sql_require_primary_key
可以强制要求在建表指定主键，避免该案例的问题。

另外在 MySQL 8.0.30 引入了 GIPK(Generated Invisible Primary key) 不可见主键^[2]，可以将参数 sql_generate_invisible_primary_key
开启。如果表没有指定主键则会自动生成 my_row_id
做为表的主键。