mysql数据库id自增不连续_MySQL自增id不连续

要修改的自增ID不是外键

解决article表主键id不连续

//先删除不连续的主键

ALTER TABLE article DROP COLUMN article_id;

//再重新建立自增的主键

ALTER TABLE article ADD article_id TINYINT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;

要修改的自增ID是外键

category表中category_id是article表的外键

这里解决category表id不连续不能先删除再重建

先获取category表行号

SELECT (@rowNum:=@rowNum + 1) AS 行号,a.* FROM (select category_id from category) as a,(SELECT @rowNum:=0) as b

将category表命名为 t1

将上面查询的表命名为 t2

使用MySQL UPDATE JOIN语句来执行跨表更新

先来看看内连接后的新表

select * from category as t1 JOIN (SELECT (@rowNum:=@rowNum + 1) AS 行号,a.* FROM (select category_id from category) as a,(SELECT @rowNum:=0) b) as t2 ON t1.category_id=t2.category_id

UPDATE JOIN语法

UPDATE T1 [INNER JOIN | LEFT JOIN] T2

ON T1.C1 = T2. C1

SET T1.C2 = T2.C2,

T2.C3 = expr

WHERE condition

更新原来category表，用行号代替category_id

UPDATE category as t1 INNER JOIN (SELECT (@rowNum:=@rowNum + 1) AS 行号,a.* FROM (select category_id from category) as a,(SELECT @rowNum:=0) b) as t2

ON t1.category_id=t2.category_id

SET t1.category_id=t2.行号

WHERE t1.category_id=t2.category_id

第一次写博客，多多包涵

参考以下两篇博客：

链接: https://blog.csdn.net/lili625/article/details/80252420.

链接: https://blog.csdn.net/a_fighting_bear/article/details/90446379.