mysql 如何修改主键起始值？

1.情景展示

在实际开发中，往往会存在这种需求：

将A表当中的数据导入B表，后面继续使用B表。

由上一篇，我们了解到：

B表如果是通过create table b as select * from a的方式，将会导致的其中一个结果就是：

B表没有指定主键列。

2.具体分析

现在的问题在于：

假如，我们现在还将ID列作为主键，并且设置成自增列的话，该ID的起始值将是多少？

一起来实际操作一下吧：

第一步：将ID列设置成主键；

第二步：取消勾选“自动递增”；

第三步：将主键列的默认值清空。

点击保存。

使用建表语句，看一下序列的起始值；

序列的起始值为：9；

我们再来运行一下建表语句：

show CREATE TABLE cz_jkdic_bak;

添加主键前：

添加主键后：

\

我们可以看到：

建表语句，除了增加了主键列外，还设置了自增的起始值：AUTO_INCREMENT=9。

我们来看一下当前主键列的最大值：

最大值为8，这，也就是说：mysql会自动将现有主键列的最大值+1，当作自增的起始值；

这一点还是非常不错的，这样一来，我们就无需担心主键冲突的问题，也不用手动去改主键序列的起始值啦。

3.解决方案

如何修改主键起始值？

错误方式一：修改AUTO_INCREMENT；

语法：

ALTER TABLE 表名 AUTO_INCREMENT=起始值

显示执行成功，我们再可以查看一下建表语句；

2022年5月23日19:24:32

虽然，建表语句的自增长起始值发生了改变，但是，然并卵。

我们在插入数据的时候，主键依然按照原来的值的基础上+1（8+1）。

在mysql客户端当中，有一个bug会让我们这样的错觉：

误以为，这种方式值有效的。

诚然，在Navicat当中，进行新增的时候，是生效的。

但是，我们切换到对象，可以看到该表的自增值。

我们可以清楚的看到：

该表主键列的自增值并未发生改变。

而且，我们操作数据，一般是通过程序操作，而不是直接操作数据库。

同样地，在SQLYog当中，我们再试一次：

选中表，右键，改变表；

查看表主键。

我们可以看到：表主键的自增值，依旧没有改变。

结论：通过这种实现方式，只对当前客户端有效，并未修改表的主键值，无论是相较与当前数据主键的最大值，将主键值调大或者调小。

当自增值为NULL或0的时候，这种方式会起作用。

错误方式二：SQLyog工具。

鼠标选中要修改的表，快捷键：F6；

自动增量，这一栏就是序列的下一个值；

修改之后，保存。

虽然显示：已经修改成功，但是，我们会发现：自增值并未发生改变。

2022年5月23日19:59:07

mysql主键特性：

mysql的自增列，有以下特性：

每次增加的值为1；

在取自增值时，会先去查当前主键的最大值，然后和当前自增值进行比对：

如果，自增值>现有数据主键的最大值，则取自增值，作为新数据的主键；

反之，自增值<=现有数据主键的最大值，则取现有数据主键的最大值+1，作为新数据的主键。

mysql的自增列的值，一般情况下，只会越来越大，是无法人为手动变小的。

正确实现方式：

第一步：复制表；

CREATE TABLE meta_theme_bak as SELECT * FROM meta_theme

我们可以看到：

此时，备份表的自增列的值为0。 

注意：使用这种复制表的方式，会导致主键、索引等相关信息的丢失。

第二步：删除旧表；

DROP TABLE meta_theme

第三步：对备份表进行重命名；

第四步：重新设置表主键、索引、表名注释等相关信息。

添加主键，并勾选“自动递增”，保存。

随后，我们可以看到，表的主键自增值已经改成了在原有数据最大值的基础上+1。

还有一种实现方式：清空表数据

表数据被清空后，我们依然可以发现：

表的自增值并未发生任何变化。

select
  auto_increment 
from
  information_schema.`tables` 
where
  table_name = '表名' 
  and table_schema = '数据库名'

但是，我们在实际插入数据的时候，表主键已经从1重新开始了。

由此可见，表的AUTO_INCREMENT并不可信，只有在实际插入的时候，我们才知道主键会是多少！

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

作者：Marydon