MySQL 8.0.13 新参数 sql_require_primary_key

问题场景：无主键表加自增主键导致主从数据不一致

1. 测试环境：用 MySQL 8.0.25 搭建了一主一从架构。
2. 操作过程
   • 建了两张没有主键的表 t1 和 t2，往里面插入数据。
   • t1 表是直接插入后提交，t2 表用两个会话分别插入数据，一个先执行后提交，另一个后执行先提交。
   • 然后给两张表都添加自增主键（id 列）。
3. 结果：t1 表主从数据一致，但 t2 表主从数据不一致，从库的 id 顺序和主库相反。

二、原因分析：无主键时主从库数据顺序不同

1. InnoDB 的隐藏机制：当表没有主键且没有非空唯一键时，InnoDB 会用内部的 “RowID” 作为隐藏主键，RowID 在语句执行时分配，先执行的语句分配的 RowID 小，后执行的大。
   • 主库情况：t2 表中，会话 1 先执行插入（RowID 小），会话 2 后执行（RowID 大），但会话 2 先提交。主库数据按 RowID 顺序排列，是 (1,1)、(2,2)。
   • 从库情况：从库通过主库的 binlog 同步数据，binlog 中记录的是提交顺序。会话 2 先提交，所以从库先处理会话 2 的插入（RowID 小），再处理会话 1 的（RowID 大），数据顺序变成 (2,2)、(1,1)。
2. 添加自增主键的逻辑：添加自增主键时，主键值是按表中数据的物理顺序分配的。主库按 (1,1)、(2,2) 顺序分配 id 为 1 和 2；从库按 (2,2)、(1,1) 顺序分配 id 为 1 和 2，导致主从 id 对应的数据行不一致。

三、解决办法

1. 临时解决法（官方建议）：通过新建表、排序插入数据来确保主从顺序一致，但表大时耗时且操作复杂。
    

   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;  -- 替换原表
   

    
2. 根本预防法：在建表时强制要求有主键，避免后续问题。
   • MySQL 8.0.13 新增参数：sql_require_primary_key，开启后建表必须指定主键，否则报错。
   • MySQL 8.0.30 新功能：GIPK（Generated Invisible Primary Key，不可见主键），开启参数sql_generate_invisible_primary_key后，若表无主键，会自动生成隐藏的my_row_id作为主键，避免手动添加主键的麻烦。

四、总结

• 核心问题：无主键表在主从复制中，因数据顺序不同导致添加自增主键时主从数据不一致。
• 最佳实践：建表时必须添加主键，利用 MySQL 的参数强制约束（sql_require_primary_key）或自动生成主键功能（GIPK），从源头避免问题。