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.