文章中所有操作均是在 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。