欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库自增长AUTO_INCREMENT探讨

程序员文章站 2022-09-14 11:11:08
文章中所有操作均是在 MySQL 5.7 版本下进行的MySQL 的自增列(AUTO_INCREMENT)和其它数据库的自增列对比,有很多特性和不同点(甚至不同存储引擎、不同版本也有一些不同的特性),让人感觉有点稍微复杂。下面我们从一些测试开始,了解一下这方面的特殊知识点。自增列持久化问题自增长在不同的存储引擎下,呈现的问题是不一样的,下面我们举例说明(MySQL 5.7.30 版本中实验)。InnoDB存储引擎的问题/*把之前的测试表删掉,重启服务器,为了更好的展示效果*/-- 创建.....

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