MySQL--修改普通表为自增表

普通表转自增表技巧

最新推荐文章于 2025-04-10 20:43:37 发布

weixin_38169359

最新推荐文章于 2025-04-10 20:43:37 发布

阅读量823

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/gaogao67/p/10392891.html

本文探讨了在MySQL中将普通表转换为自增表的两种方法：修改现有列为自增列及新增自增列。详细分析了直接使用ALTERTABLEADDCOLUMN可能引发的主从数据不一致性问题，并提供了安全的转换建议。

修改普通表为自增表

将普通表修改为自增表，可分为两种类型：

1、将现有列修改为自增列，当数据类型为数值类型且表中数据唯一时，直接只用ALTER TABLE ALTER COLUMN命令进行修改，修改操作会保留现有列中数据。

2、对普通表新增自增列，如果直接使用ALTER TABLE ADD COLUMN来修改，会为每行记录生成新的自增值，但该操作可能会导致主从数据不一致，不建议使用。

新增自增列引发主从数据不一致

在MySQL中允许使用ALTER TABLE方式为普通表新增一个自增列，但由于ALTER操作为DDL语句，在主从复制时会将该DDL语句传递给从库执行，MySQL并不能保证相同记录在主从服务器上获得相同的自增ID，会导致主从数据差异。
模拟测试：

## 主库上创建表：
CREATE TABLE TB1001
(
  C1 INT
);

## 会话1开启事务并执行：
START TRANSACTION;
INSERT INTO TB1001(C1) SELECT 1;

## 会话2执行：
INSERT INTO TB1001(C1) SELECT 2;

## 会话1提交事务。

## 然后将表修改为自增表：
ALTER TABLE TB1001 ADD ID INT PRIMARY KEY AUTO_INCREMENT;

主库数据为：

从库数据为：

原因分析：
在主库上，C1=2的数据晚于C1=1的数据被插入，但由于C1=2的数据所在事务被先提交，因此C1=2的记录先于C1=1的记录在从库上执行，因此两条记录在主库和从库上的插入顺序不同，在生成自增ID时获得到自增ID不同，最终导致数据差异。

建议：在将普通表修改为自增表时，如果表中存在数据，请勿使用ALTER TABLE的方式修改，建议新建自增临时表，然后将数据导入到该表中，再兑换表名。