MYSQL中的auto_increment

 在MySQL中，经常使用 AUTO_INCREMENT对主键建立自增id的行为，MySQL会自行保证主键ID的自增与不重复，使程序不需对ID进行关注。

但是在使用时，MySQL对auto_increment对管理的一些特性可能会导致一些问题，所以需要手工对其进行修改。

修改AUTO_INCREMENT，MySQL只需一条非常简单的命令：

ALTER TABLE t1 AUTO_INCREMENT = 13;

需要注意，在InnoDB引擎下，如果修改的目标值小于当前数据库中ID的最大值，则数据库不会有任何变化。

在MyISAM引擎下，如果修改的目标值小于当前数据库中ID的最大值，则AUTO_INCREMENT会被设置为该最大值。

 

在前几天的工作中，为了保证ID的范围，对数据库的AUTO_INCREMENT进行了修改，发现了一些问题。

当在使用ALTER TABLE mytable AUTO_INCREMENT = N修改AUTO_INCREMENT值时，

即使没有对数据结构进行变化，MySQL服务器（操作的服务器为MySQL5.5，innodb引擎）也会自动建立一个新的表，然后将所有数据复制到新表中，

再将旧表删除，并重命名新表。

同时，在返回的结果中可以看到，所有的行都被affected。

例如：

mysql> alter table mytable auto_increment=1000000;
Query OK, 512691 rows affected (1 min 4.55 sec)
Records: 512691 Duplicates: 0 Warnings: 0

 

经过查询，发现这是在使用ALTER TABLE命令时常见的情况，同时AUTO_INCREMENT在MySQL5.5中不属于可以跳过重建操作的命令

这就带来一个问题，在进行修改AUTO_INCREMENT时，整个表会被加锁，不能修改，影响正常服务。如果表里的数据量非常大，则复制数据会相当的花时间。

同时也可能带来其他的不可预知的问题，例如硬盘空间被零时表大量占用的问题。

 

在查询了MySQL官方文档与相关资料后发现了一些可以快速解决的方案：

1、如果需要将AUTO_INCREMENT变大，则可以通过很方便的手段进行操作，首先插入一行傀儡数据，插入时将该行数据的ID设置为AUTO_INCREMENT的目标值-1。

进行此操作后，则AUTO_INCREMENT会自动变为你嗦需要设置的目标值。

但是这种方法仅适用于将AUTO_INCREMENT改大的情况，无法将AUTO_INCREMENT改小。

 

2、如果需要快速的将AUTO_INCREMENT改小，则需要利用innodb的特性。

在InnoDB中，使用的是内存自增计数器，也就是说，AUTO_INCREMENT不会写入到硬盘中。

每次重启数据库服务后，MySQL会自动运行命令

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

以获取最大的ID，并赋值到AUTO_INCREMENT中作为计数器的新值。

利用这一点，我们就可以快速的修改AUTO_INCREMENT的值：首先删除所有比目标值大的ID的行，然后重启数据库，再使用之前改大的方法设置AUTO_INCREMENT