无主键表添加自增主键后导致主备节点查询数据不一致

场景描述

在主备节点上使用添加的自增主键值进行查询，查询结果中出现数据不一致问题。

原因分析

对于无主键表，表中数据的顺序由存储引擎的RowID决定，无主键表中的数据在主备节点上的RowID可能不同，因此表中的数据在主备节点上的顺序不同。当对主备节点中数据顺序不同的表添加自增主键时，自增主键的值是按照表中数据的顺序初始化的，从而导致同样的数据对应的自增主键值不同，即用相同的自增主键值分别在主备节点上查询的数据不同。详情请见MySQL官方文档。

解决方案

为已有数据的表添加自增列时，请先创建相同表结构的新表，再在新表上添加自增列，将原表数据导入（导入数据时，请尽量保持原表无写入操作，否则会造成原表与新表数据不一致）。

按照如下步骤解决主备节点查询数据不一致问题。

1. 在主节点上创建一个与无主键表（称之为原无主键表t1）相同的新表t2，并为新表中添加自增主键。
   示例如下：

   CREATE TABLE t2 LIKE t1;
   ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;

2. 将原无主键表的数据全部插入到新表t2中。

   示例如下：

   INSERT INTO t2(col1, col2) SELECT col1, col2 FROM t1 ORDER BY col1, col2;

   

   为了确保主备节点对应表中数据的顺序相同，ORDER BY子句必须包含原无主键表的所有列。

3. 删除原无主键表t1，并将新表重命名为原无主键表名。

   示例如下：

   DROP TABLE t1;
   RENAME TABLE t2 TO t1;