MySQL数据库自增长AUTO_INCREMENT探讨

文章中所有操作均是在 MySQL 5.7 版本下进行的

MySQL 的自增列（AUTO_INCREMENT）和其它数据库的自增列对比，有很多特性和不同点（甚至不同存储引擎、不同版本也有一些不同的特性），让人感觉有点稍微复杂。下面我们从一些测试开始，了解一下这方面的特殊知识点。

自增列持久化问题

自增长在不同的存储引擎下，呈现的问题是不一样的，下面我们举例说明（MySQL 5.7.30 版本中实验）。

InnoDB存储引擎的问题

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
-- 创建一个表tbl_test，为InnoDB引擎的，并加入了6条数据
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
) ENGINE=InnoDB;
-- insert加入6调数据，省略
-- 查询数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
|  5 | val5 |
|  6 | val4 |
+----+------+
-- 删除几条数据
delete from tbl_test where id >= 5;
select * from tbl_test;
+----+------+
| id | info |
+----+------+
| id | info |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+

以上情况，非常简单，这时候我们重启 MySQL 服务，记得是重启 MySQL 的数据库服务。重启之后，我们再插入一条记录，字段 id 会从什么值开始呢？ 如下所示，别忘记我们这个测试表的存储引擎为 InnoDB。

-- 新增一条数据
insert into tbl_test (info) values ('new val');
select * from tbl_test;
+----+---------+
| id | info    |
+----+---------+
|  1 | val1    |
|  2 | val2    |
|  3 | val3    |
|  4 | val4    |
|  5 | new val |
+----+---------+

从上面会发现，最开始 id 已经自增长到了 6，删除了两条记录，然后我们重启了 MySQL 的数据库服务，再添加了一条数据，id 却是从 5 开始，不是我们理解的 7 开始。

MyISAM存储引擎对比

那我们继续测试，这是创建一个表存储引擎变为了 MyISAM。

-- 删除之前的tbl_test继续创建，为MyISAM引擎的，并加入了6条数据
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
) ENGINE=MyISAM;
-- insert加入6条数据，省略
-- 查询数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
|  5 | val5 |
|  6 | val4 |
+----+------+
-- 删除几条数据
delete from tbl_test where id >= 5;
select * from tbl_test;
+----+------+
| id | info |
+----+------+
| id | info |
+----+------+
|  1 | val1 |
|  2 | val2 |
|  3 | val3 |
|  4 | val4 |
+----+------+

操作都是一样，这时候我们继续重启 MySQL 的数据库服务。重启之后，我们再插入一条记录，存储引擎为 MyISAM 的表字段 id 会从什么值开始呢？

-- 新增一条数据，这个时候tbl_test2可是MyISAM的存储引擎
insert into tbl_test (info) values ('new val');
select * from tbl_test;
+----+---------+
| id | info    |
+----+---------+
|  1 | val1    |
|  2 | val2    |
|  3 | val3    |
|  4 | val4    |
|  7 | new val |
+----+---------+

会发现最开始 id 自增长到了 6，删除了两条记录，然后我们重启了 MySQL 的数据库服务，再添加了一条数据，这次 id 是从 7 开始了。

两者存储引擎的解释

那么为什么出现不同的两个结果呢？这个是因为 InnoDB 存储引擎中，自增主键没有持久化，而是放在内存中，关于自增主键的分配，是由 InnoDB 数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过“select max(id) from tbl_test for update”这样的 SQL 语句来初始化（不同表对应不同的 SQL 语句）, 官网也有相应的问题介绍 https://bugs.mysql.com/bug.php?id=199，查询了官网看到是到了 MySQL 8.0 才将自增主键的计数器持久化到 redo log 中。每次计数器发生改变，都会将其写入到 redo log 中，如果数据库发生重启，InnoDB 会根据 redo log 中的计数器信息来初始化其内存值。 MySIAM 存储引擎，自增主键的最大值存放在数据文件当中，每次重启 MySQL 服务都不会影响其值变化。

自增长列的细节

如何获取自增列的值

获取当前自增列的值，可以使用 last_insert_id 函数，它是一个系统函数，可获得自增列自动生成的最后一个值。但是需要注意的是，它不是可以获取指定哪个表的自增长列的值，它是获取服务器的中最后一次自增长列的值，不管是哪个表自增长列。如果服务器的中从没有 auto_increment 值，则该函数返回 0。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
创建了两个测试表tbl_test1和tbl_test2
*/
drop table if exists tbl_test1;
create table tbl_test1(
	id	 int auto_increment primary key,
	info varchar(50)
);
drop table if exists tbl_test2;
create table tbl_test2(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 执行last_insert_id函数，没有任何auto_increment值，返回0
select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
-- 执行添加数据，向tbl_test1增加3条数据
insert into tbl_test1 (info) values ('val1');
insert into tbl_test1 (info) values ('val2');
insert into tbl_test1 (info) values ('val3');
-- 再执行last_insert_id函数，这里最后一次auto_increment值增加了，返回3
select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
-- 继续，这次向向tbl_test2就增加1条数据
insert into tbl_test2 (info) values ('val1');
-- 紧接着就执行last_insert_id函数
-- 这里auto_increment值返回就是最后一次会话中的值1
select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

以上示例很明显，last_insert_id 函数不针对表，只针对最后一次 auto_increment。

把NULL值插入到一个auto_increment数据列

提到把 null 值数据查到自增长列中去，我觉得如果你熟悉 MS SQL Server 数据库自增长列的朋友来说，这怎么可能，事实上 MySQL 可行。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 插入null
insert into tbl_test (id, info) values (null, 'val');
-- 查询获取数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val  |
+----+------+

以上示例，其实是 MySQL 自动的生成下一个编号（测试是清除了所有的表然后重启服务器重新创建表测试的）。

能不能把0值插入到auto_increment数据列

还有一个奇葩问题，能不能给自增长列插入0？答案是可以的，默认的情况下，插入 0 是和插入 null 到自增长列是一样的效果，也是 MySQL 会自动的生成下一个编号。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 插入0
insert into tbl_test (id, info) values (0, 'val');
-- 查询获取数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val  |
+----+------+

有的朋友就是脾气倔强，就要让自增长列从 0 开始，行不行？！答案也是可以的。MySQL 数据库有一个叫 sql_mode 全局变量，它是有默认值的。这个全局变量在我的《only_full_group_by解决办法》中提到过。

select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+

如果其中没有设置 NO_AUTO_VALUE_ON_ZERO 的情况，也就是刚才说的默认情况下即使是插入 0，MySQL 也会自动的生成下一个编号。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 插入0
insert into tbl_test (id, info) values (0, 'val');
-- 查询获取数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val  |
+----+------+

我们设置一下 sql_mode 全局变量，为达到实验效果，请删除之前的表，重启服务器。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
-- 设置sql_mode全局变量
set sql_mode = 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 创建测试表
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 插入0
insert into tbl_test (id, info) values (0, 'val');
-- 查询获取数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  0 | val  |
+----+------+
-- 继续插入
insert into tbl_test (info) values ('val');
-- 查询获取数据
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  0 | val  |
|  1 | val  |
+----+------+

以上示例有个点需要了解，sql_mode 全局变量在服务器重启之后就恢复默认了，如果你还想继续让自增长列从 0 开始，可以修改 my.ini（对应 Linux 的 my.cnf） 文件，这里不做再深入分析了（在我另一篇文章《only_full_group_by解决办法》中提及的解决方法类似）。

自增长列能跳号吗

MySQL 的自增字段是可以跳号的，可以插入一条指定自增列值的记录（即使插入的值大于自增列的最大值），如下所示，当前自增列最大值为 1，我插入一个 id=100 的值，然后就会以 100 开始继续自增，而且我还可以继续插入 id=55 的记录（只要 55 这个 id 没有重复）。

/*
把之前的测试表删掉，重启服务器，为了更好的展示效果
*/
-- 创建测试表
drop table if exists tbl_test;
create table tbl_test(
	id	 int auto_increment primary key,
	info varchar(50)
);
-- 插入数据
insert into tbl_test (info) values ('val');
select * from tbl_test;
+----+------+
| id | info |
+----+------+
|  1 | val  |
+----+------+
-- 继续插入指定id=100的数据
insert into tbl_test (id, info) values (100, 'val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
| 100 | val  |
+-----+------+
-- 继续插入数据
insert into tbl_test (info) values ('val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
| 100 | val  |
| 101 | val  |
+-----+------+
-- 插入指定id=55的数据
insert into tbl_test (id, info) values (55, 'val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
|  55 | val  |
| 100 | val  |
| 101 | val  |
+-----+------+

以上示例很好理解。还有个问题需要稍微的解释一下，就是在事务里面，如果 insert 之后并没有提交，使用了事务回滚，自增长列也是会跳号的。

-- 继续使用刚才的测试表
-- 开启事务
begin;
insert into tbl_test (info) values ('val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
|  55 | val  |
| 100 | val  |
| 101 | val  |
| 102 | val  |
+-----+------+
-- 事务没有提交，回滚rollback
rollback;
-- 再添加一条数据
insert into tbl_test (info) values ('val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
|  55 | val  |
| 100 | val  |
| 101 | val  |
| 103 | val  |
+-----+------+

无论 MySQL 还是其它的关系型数据库，都会遇到这种逻辑跳号的情况，Oracle 的序列也会有这种跳号问题， MS SQL Server 也有此问题。一般情况下为提高自增列的使用效率，会将自增值的操作设计为非事务性操作，这样事务中生成的自增值不会被回滚产生跳号问题。

删除表truncate table的操作

truncate 和 delete 虽然都是删除表的数据，区别在于 delete 操作可以一条一条删除记录的，配合事务和回滚可以找回数据，重要的是自增长（auto_increment）不会重置。truncate 则是直接删除整个表，再重新创建一个一模一样的新表，表的以前设置的约束、触发器、索引等等吧都会删除，自增长（auto_increment）也会被重置，且数据也无法找回。

-- 继续使用刚才的测试表
truncate table tbl_test;
insert into tbl_test (info) values ('val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
+-----+------+

以上示例得知，自增长（auto_increment）被重置了。

修改auto_increment的自增起始值

-- 继续使用刚才的测试表
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
+-----+------+
-- 修改自增起始值
alter table tbl_test auto_increment = 100;
-- 添加一条记录
insert into tbl_test (info) values ('val');
select * from tbl_test;
+-----+------+
| id  | info |
+-----+------+
|   1 | val  |
| 100 | val  |
+-----+------+

自增长能创建在哪些数据类型的字段上

其实这个问题，没有讨论的必要。我们都已经讨论自增长这个事了，那基本就排除了字符串类型和其它没有自增概念的数据类型了，浮点类型也不行。

自增长可以创建在 int 类型上，包括 tinyint，smallint，mediumint，bigint，integer也可以。

MySQL数据库给非主键添加自增长

刚才我们讨论了很多关于自增长列的问题和细节，但是它都是建立在主键上的。那自增长能创建在普通的字段上吗？

-- 如下示例创建一个表
drop table if exists tbl_test;
create table tbl_test(
	id	 int primary key,
    num  int auto_increment,
	info varchar(50)
);

不管是用控制台还是数据库可视化操作工具，以上创建表结果的语句肯定是报错的。

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

上面的意思就是说”设为自增长的字段必须是做为一个键（key）“，其中并没有提必须是主键。那如何给非主键的普通字段上设置自增长，如下示例：

drop table if exists tbl_test;
create table tbl_test(
	id	 int primary key,
    num  int,
	info varchar(50)
);
-- 设置字段必须为not null，如果建表的已经not null了，这里就不需要设置了
alter table tbl_test modify column num int not null;
-- 给num字段添加一个任意key
alter table tbl_test add key numtest(num); -- numtest为任意的key名称
-- 给num普通的字段上设置自增长
alter table tbl_test modify column num int auto_increment;
-- 查看表结构
desc tbl_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    |                |
| num   | int(11)     | NO   | MUL | NULL    | auto_increment |
| info  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

具体插入数据这里就不再去试了，有兴趣的朋友可以自己试试。其实有些朋友疑问就是之前的那个报错提到的，为什么自增长要求字段必须是做为一个键（key），这里的操作就是给 num 字段一个 key，key 的名字我随意起的 numtest。其实很好理解，为什么之前能把自增长给主键，就是因为主键（primary key）也是个key。有关于 key 的讨论在另外一篇文章中有稍微详细的分析《MySQL数据库KEY与INDEX区别》。

结语

这篇文章讨论的挺多，谢谢您的观看。最后讲个好玩的事吧，作者曾经有过那种刚入职 IT 领域的小兄弟同事，表的主键如果不是自增长形式的，他基本上就是根本不会用，在他看来所有表的主键就应该是自增长才对，如果没有自增长就根本没法使用这个表。并没有嘲笑他的意思，只是善意的微笑，学数据库有点学魔怔认死理了，只是想告诉他自增长的列可以用来做主键，不是主键就必须是自增长，如果用 Oracle 该怎么办？！序列可不是小兄弟眼里的自增长，他曾经说过 Oracle 序列不好用又没有自增长怎么办啊，这不得要了亲命了，没法玩了O(∩_∩)O。

本文地址：https://blog.csdn.net/lili40342/article/details/107148770