MySQL数据库自增长AUTO_INCREMENT探讨

爱被打了一巴掌 2023-02-23 07:57 26阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 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][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 也会自动的生成下一个编号。

我们设置一下 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。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200629231107694.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg==,size_16,color_FFFFFF,t_70#pic_center
[https_bugs.mysql.com_bug.php_id_199]: https://bugs.mysql.com/bug.php?id=199