MySQL里AUTO_INCREMENT到底怎么用才不会出错，有些坑你得注意下

关于MySQL里AUTO_INCREMENT这个东西，说白了就是帮你自动给数据表加一个不断增长的数字编号，特别适合用作订单号、文章ID这种唯一的主键，用起来简单，但想用得踏实，不踩坑,有几个地方你得多留个心眼。

最基础的就是你怎么把它用在一张表上，你会在创建表的时候，把它设置在主键字段上，比如你建一个用户表，可以这么写（来源：MySQL常见表创建语法）： CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); 这样，你每次插入新用户，不用管id这个字段，MySQL会自动帮你填上一个比上一条记录大的数字,从1开始。

但这里第一个坑就来了：AUTO_INCREMENT的值是会“丢失”的，什么意思呢？比如你插入了id为1,2,3的三条记录，然后你把id=2的这条记录删掉了，接下来你再插入新记录，新记录的id会是4，而不是2，那个被删除的2，就像从来没出现过一样，再也不会被使用了，很多人以为会自动填补空缺，其实不会，这是设计如此，为了避免很多复杂问题，如果你的业务非常在意ID的绝对连续性（比如财务流水号），光靠AUTO_INCREMENT是不行的,可能需要更复杂的逻辑。

第二个需要注意的大坑是重复键错误，听起来很奇怪，既然是自动增长的，怎么会重复呢？这通常发生在你“手动作死”的时候，上面那个表，系统自动生成的id已经到了10，这时候你偏偏要手动插入一条记录，指定id=99，操作成功了，但当你下次不指定id，让系统自动生成时，MySQL会从当前最大的id（也就是99）之后开始增长，生成100，这没问题，但如果你手动插入了一个比当前AUTO_INCREMENT值小、但又已经存在的id，比如你强行插入id=5，那就会直接报错，因为主键冲突了，更隐蔽的情况是，如果你手动插入了一个非常大的数，比如id=1000000，这可能会一下子把AUTO_INCREMENT的计数器拔得很高,导致后面很长一段数字都被跳过了。

第三个要点是关于重置AUTO_INCREMENT的起始值，有时候你可能会清空表（比如用TRUNCATE TABLE命令），这个命令不仅清空数据，还会把AUTO_INCREMENT计数器重置回1，但如果你用的是DELETE FROM table来删除所有数据，AUTO_INCREMENT的计数器是不会重置的，它会接着上次的最大值继续增长，如果你想在DELETE后从头开始，就需要手动修改：ALTER TABLE table_name AUTO_INCREMENT = 1; 这个操作要小心,确保不会引发主键冲突。

第四个容易忽略的问题是在InnoDB引擎下的特殊情况（来源：MySQL官方文档关于InnoDB自增锁的说明），MySQL为了保证在并发插入时，AUTO_INCREMENT的值仍然是唯一的，会在处理语句时加一个特殊的锁，这个锁的行为根据你的MySQL版本和配置（特别是innodb_autoinc_lock_mode这个参数）有关，在早期版本或者某些模式下，这个锁可能会影响并发插入的性能，尤其是在像INSERT ... SELECT这种批量插入大量数据的时候，可能会阻塞其他插入操作，虽然在新版本中已经优化了很多，但如果你发现插入性能有问题,可以朝这个方向想想。

第五，主键的选择，AUTO_INCREMENT通常和主键（PRIMARY KEY）绑在一起用，但主键不一定非得是单列的AUTO_INCREMENT，也可以是复合主键（多个字段一起做主键），但AUTO_INCREMENT字段本身必须被索引，而且它通常是主键的一部分，强烈建议还是用一个单独的、无意义的自增ID做主键，这样最简单，性能也好，用业务字段（如身份证号、用户名）做主键，一旦业务规则变化,改动起来会很麻烦。

第六，最大值和类型限制，你给id字段定义的是什么整数类型，就决定了自增ID的最大值，比如你用TINYINT UNSIGNED，那最大只能到255，如果用INT UNSIGNED，能到约42亿，如果你做一个用户量或订单量巨大的应用，一开始就要考虑好，别等到快超了才手忙脚乱地去修改字段类型，那可是个大操作，通常建议直接使用BIGINT UNSIGNED,一劳永逸。

一个实践中的小技巧：获取刚刚插入记录的自增ID，在应用程序中，你插入一条记录后，往往需要立刻知道这条记录的ID是多少，你不能用SELECT MAX(id)的方式，因为在并发环境下这完全不靠谱，正确的方法是使用MySQL连接特有的函数，比如在PHP中用mysqli_insert_id()，在Java的JDBC中用getGeneratedKeys()，这些方法能确保你取到的就是你刚才那条插入语句所产生的ID,是绝对准确和线程安全的。

AUTO_INCREMENT是个好工具，但你要明白它的脾气：它不保证连续、讨厌被手动干预、重置它要分情况、在高并发下可能有锁的问题、并且受限于字段类型的最大值，理解了这些，你就能更好地驾驭它,避免在关键时刻掉链子。