MySQL中 auto_increment如何修改初始值和步长【亲测】

1、如何查看auto_increment的初始值和步长

       打开黑窗口，登录管理员账号和密码后，执行以下命令：

show variables like 'auto_inc%'; -- 查看当前数据库的自增长设置

     执行结果如下：

    

   即 auto_increment的初始值(offset)是1, 步长(increment)是1. (步长指每次的递增量)。即每次递增1.

 2、如何修改auto_increment的初始值和步长

        在黑窗口中执行以下命令：

set @@auto_increment_increment=2; --设置步长为2
set @@auto_increment_offset=2;    --设置初始值为3

   接下来做个测试：

   --创建测试表test
   create table test(
	id int(10) primary key auto_increment,
	name varchar(50)
   );
   -- 插入测试数据
   insert into test (name) values ('张三');
   insert into test (name) values ('李四');
   insert into test (name) values ('王五');

  我们看下执行结果：

    

  可以看到，修改auto_increment的初始值和步长成功 ！

3、auto_increment的问题

 这里以 auto_increment的默认情况为例(初始值为1，步长为1) 

 有一种特殊的情况，我们一起看以下示例。

  在黑窗口执行以下指令：

   -- 删除表test
   drop table test;
   -- 创建表test
   create table test(
	id int(10) primary key,
	name varchar(50)
   );
   insert into test values (1,'张三');
   insert into test values (3,'李四');
   insert into test values (5,'王五');

  执行结果如下图：

  

 

接下来，我们为test表的id列添加 auto_increment

alter table test change id tid int(10) auto_increment;

  因为test表中的数据 id 的值时 1、3、5 ， 所以我们希望通过修改auto_increment的初始值和步长，来补充中间空缺的id的值， 

  比如 2、4 ，所以 我们可以修改auto_increment的默认设置。如下指令：

   -- 修改auto_increment的初始值和步长
   set @@auto_increment_increment=2; --设置步长为2
   set @@auto_increment_offset=2;    --设置初始值

  查看修改后的auto_increment相关信息：

   

  接下来我们再次为test表插入测试数据：

   insert into test (name) values ('薛之谦');
   insert into test (name) values ('毛不易');
   insert into test (name) values ('邓紫棋');

 查询下最终的结果：

 

大家发现，并不是我们想要的结果。其实auto_increment在使用时，会对初始值与添加了auto_increment语法的列中的值进行比较，当 auto_increment的当前值(此处值是2)小于 添加了该语法的列的值时，auto_increment会拿当前值2 根据步长(2)递增，知道auto_increment的当前值大于添加了该语法的列(id)的值时，才会将最终的这个值作为初始插入数据的值。也就是说，执行以下流程：

auto_increment    初始值     2      步长2

插入第一条数据 '薛之谦 ' 时，会拿2跟表中数据的id的值进行比较  

2>1

2<3    auto_increment 此时会递增一次，递增后的结果是4

4>3

4<5  auto_increment 此时会继续递增一次，递增后的值是6

6>5  

此时表中已经没有其他的数据，6最大，此时才执行插入 第一条数据 '薛之谦',因此，'薛之谦' 的 id = 6，

因为步长是2，所以后面id的值依是 id = 8 ，id= 10 . 所以才会发生刚刚大家的那种情况。

 

auto_increment 在插入数据时，真正一开始可以插入表中的值得计算公式：

假设 a 为 表中id列的最大值, b为auto_increment的初始值，c 为步长，则auto_increment真正的初始值为：

auto_increment_real  =  int( a/b) * c + c  ;

即 auto_increment_real =（5 % 2）*2 + 2 = 6

参考mysql官方文档地址：官方文档在此，公式出自此处

 

 

此文希望可以帮助到大家。如有错误，请指教。                                                           

如果大家还有其他的情况或者好的解决方法，也望指教，感谢阅读。