一、引言
在关系型数据库MySQL中，AUTO_INCREMENT是数据库的一个属性，该属性使得在申明了AUTO_INCREMENT的列中可以自动生成唯一的递增值。
二、AUTO_INCREMENT使用示例
InnoDB引擎条件下使用示列：
通常情况，我们在定义数据表中为整数类型主键列时，应用AUTO_INCREMENT 属性，用以保证INSERT的所有记录都具有一个唯一的id，建表示列如下：
CREATE TABLE students ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(10) NOT NULL, PRIMARY KEY (id) )ENGINE=INNODB;
在建表成功后，我们执行如下SQL语句，由最终的查询结果可以看出在未申明主键id值时，AUTO_INCREMENT为我们自动生成了递增的主键id，并且默认偏移值从1开始递增。当然我们也可以在建表时主动申明初始偏移值从多少开始。
INSERT INTO students (name) VALUES ('Ross'),('Julie'),('Gloria'),('Carol'); SELECT * FROM students; id name 1 Ross 2 Julie 3 Gloria 4 Carol
此时我们再执行包含主动申明主键id值的如下语句：
INSERT INTO students (id,name) VALUES(0,'Joan'),(NULL,'Niki'),(20,'Betty'); INSERT INTO students (name) VALUES('Linda'); SELECT * FROM students; id name 1 Ross 2 Julie 3 Gloria 4 Carol 5 Joan 6 Niki 20 Betty 21 Linda
由上述执行语句及查询结果可以看出：
- 当我们申明新增记录主键id小于当前AUTO_INCREMENT根据偏移值应该自动生成的值时，则在写入数据时主动申明的id值也无效，数据库中最终写入的值依然是AUTO_INCREMENT生成的值；
- 当我们申明新增记录主键id为NULL时，数据库中最终写入的值将是AUTO_INCREMENT生成的值；
- 当我们申明新增记录主键id大于本次写入AUTO_INCREMENT根据偏移值应该主动生成的值时，则数据库中最终写入的值为我们主动申明的值，并且后续AUTO_INCREMENT将以此申明的值为偏移值进行递增生成；
现在我们删除一条记录并再写入一条记录看看数据库是如何表现,从下面的执行结果可以得出在执行DELETE语句时并不会影响AUTO_INCREMENT已记录的偏移值。
DELETE from students where name='Linda'; INSERT INTO students (name) VALUES('Linda'); SELECT * FROM students; id name 1 Ross 2 Julie 3 Gloria 4 Carol 5 Joan 6 Niki 20 Betty 22 Linda
注意：
- 在MySQL8.0版本以前当我们执行了上述DELETE语句重启数据库再写入一条记录时，此时id的值为21，而MySQL8.0及以后的版本相同操作的情况下id生成的值为22。因为在8.0以前的版本AUTO_INCREMENT的值保存在内存中，重启数据库后将偏移量初始化为当前表中的实际最大记录值，而8.0以后的版本AUTO_INCREMENT的值做了持久化，因此重启后偏移量依然为建表以来生成的过的最大值。
- InnoDB引擎中申明为AUTO_INCREMENT的列必须为索引列，否则建表时会报“1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for XXX”错误，并且在组合索引时申明为AUTO_INCREMENT的列为组合索引的第一列，否则会报“1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。
MyISAM引擎条件下使用示列
MyISAM引擎中申明为AUTO_INCREMENT属性的字段必须是索引字段，但与InnoDB引擎不同的是：在组合索引的情况下，申明为AUTO_INCREMENT的字段不要求为组合索引的第一列
CREATE TABLE countries_test1 ( id MEDIUMINT NOT NULL AUTO_INCREMENT, continent CHAR(20) NOT NULL, country_name CHAR(30) NOT NULL, PRIMARY KEY (id,continent) ) ENGINE=MyISAM; INSERT INTO countries_test1 (continent,country_name) VALUES ('Asia','中国'),('Asia','日本'), ('North America','美国'),('Asia','韩国'),('Europe','英国'), ('Europe','德国'); INSERT INTO countries_test1 (id,continent,country_name) VALUE (0,'Asia','越南'),(NULL,'Asia','泰国'),(10,'Asia','新加坡'); INSERT INTO countries_test1 (continent,country_name) VALUE ('Asia','老挝'); DELETE from countries_test1 where country_name='老挝'; INSERT INTO countries_test1 (continent,country_name) VALUE ('Asia','老挝'); SELECT * FROM countries_test1; id continent country_name 1 Asia 中国 2 Asia 日本 3 North America 美国 4 Asia 韩国 5 Europe 英国 6 Europe 德国 7 Asia 越南 8 Asia 泰国 10 Asia 新加坡 12 Asia 老挝
从上面的执行示列可以看出，在MyISAM引擎中，当申明为AUTO_INCREMENT字段的索引中，如果AUTO_INCREMENT字段在索引的第一列则执行效果和InnoDB引擎中效果一样，接下来我们将组合索引中申明为AUTO_INCREMENT的列不为第一列并查看实际执行效果。
CREATE TABLE countries_test2 ( continent CHAR(20) NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, country_name CHAR(30) NOT NULL, PRIMARY KEY (continent,id) -- 注意这里组合索引字段申明顺序 ) ENGINE=MyISAM; INSERT INTO countries_test2 (continent,country_name) VALUES ('Asia','中国'),('Asia','日本'), ('North America','美国'),('Asia','韩国'),('Europe','英国'), ('Europe','德国'); SELECT * FROM countries_test2 ORDER BY continent,ID; continent id country_name Asia 1 中国 Asia 2 日本 Asia 3 韩国 Europe 1 英国 Europe 2 德国 North America 1 美国
从上面的实验结果可以看出，在组合索引中申明为AUTO_INCREMENT列不为第一列时，只有当索引中第一列的值相同时，相同组内的AUTO_INCREMENT值才会自增，这种特性是的在将数据放入有序组的场景中特别有用。接下来在同一数据组内同样测试主动申明AUTO_INCREMENT建的值相关结果如下示例所示，从实际结果可看出同组内的自增表现与InnoDB引擎中一致。
INSERT INTO countries_test2 (id,continent,country_name) VALUE (0,'Asia','越南'),(NULL,'Asia','泰国'),(10,'Asia','新加坡'); INSERT INTO countries_test2 (continent,country_name) VALUE ('Asia','老挝'); SELECT * FROM countries_test2 ORDER BY continent,ID; continent id country_name Asia 1 中国 Asia 2 日本 Asia 3 韩国 Asia 4 越南 Asia 5 泰国 Asia 10 新加坡 Asia 11 老挝 Europe 1 英国 Europe 2 德国 North America 1 美国
特别需要注意的是：在这中表结构设计中，我们删除一条同组中的自增id最大的一条记录，自增id的偏移量也会随之减小，当再插入一条记录时，自增id所取的偏移量为当前同组中Max(id)++后的值。相关示例如下所示：
DELETE from countries_test2 where country_name='老挝'; INSERT INTO countries_test2 (continent,country_name) VALUE ('Asia','老挝2'); SELECT * FROM countries_test2 ORDER BY continent,ID; continent id country_name Asia 1 中国 Asia 2 日本 Asia 3 韩国 Asia 4 越南 Asia 5 泰国 Asia 10 新加坡 Asia 11 老挝2 Europe 1 英国 Europe 2 德国 North America 1 美国
三、AUTO_INCREMENT使用总结
- InnoDB和MyISAM引擎中申明为AUTO_INCREMENT的列均需为索引列，在参与组合索引定义时InnoDB中需要为组合索引第一列，而MyISAM中可以不为第一列；
- AUTO_INCREMENT默认偏移量从1开始，也可以在建表时自定义初始偏移量；
- 当向带有 AUTO_INCREMENT 的列插入 NULL 值时，MySQL 会忽略 NULL 值并自动生成一个自增值；
- 当写入数据时主动写入AUTO_INCREMENT的列的值，如果待写入的值大于AUTO_INCREMENT即将生成的偏移量的值则使用主动写入的值，否则MySQL 会忽略主动写入值并自动生成一个自增值；
- 使用 DELETE 或 TRUNCATE TABLE 语句删除表中的数据行不会重置 AUTO_INCREMENT的偏移值；
- MyISAM引擎中当AUTO_INCREMENT列为索引第一列时使用方式和InnoDB一样，但不为第一列时相同组内的AUTO_INCREMENT值才会自增；
- MySQL8.0版本以前版本AUTO_INCREMENT存在内存中，因此重启数据库AUTO_INCREMENT的偏移值会初始化为当前表中的最大值，而8.0及以后的版本AUTO_INCREMENT做了持久化，重启数据库AUTO_INCREMENT的偏移值会是建表以来生成过的最大值；
