龙空技术网

面试官:如何修改mysql数据库的一张普通表为自增表?

波波说运维 542

前言:

如今各位老铁们对“mysql中如何修改数据”大体比较珍视,兄弟们都需要学习一些“mysql中如何修改数据”的相关资讯。那么小编在网摘上网罗了一些对于“mysql中如何修改数据””的相关资讯,希望你们能喜欢,朋友们一起来了解一下吧!

概述

很多时候我们想要对mysql的一张普通表,修改为自增表,可以考虑怎么做呢?

修改普通表为自增表

将普通表修改为自增表,可分为两种类型:

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的方式修改,建议新建自增临时表,然后将数据导入到该表中,再对换表名。

后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

标签: #mysql中如何修改数据