首页 > 代码库 > Mysql数据库之auto_increment

Mysql数据库之auto_increment

一、概述

    在数据库应用中,我们经常需要用到自动递增的唯一编号来标识记录。在MySQL中,可通过数据列的auto_increment属性来自动生成。可在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值,当然在设置的时候Mysql会取数据表中auto_increment列的最大值 + 1与n中的较大者作为新的auto_increment值。

    Myql的auto_increment属性具有以下特性:

    • 具有auto_increment属性的数据列应该是一个正数序列,如果把该数据列声明为UNSIGNED,这样序列的编号个数可增加一倍。比如tinyint数据列的最大编号是127,如果加上UNSIGNED,那么最大编号变为255
    • auto_increment数据列必须有唯一索引,以避免序号重复;必须具备NOT NULL属性

    实际应用中发现,在delete掉某张innoDB表的全部数据并重启Mysql会导致该表的auto_increment列变为1。特测试多种情况下auto_increment列的变化并记录如下。

二、实验

   1、innoDB与MyISAM对比

  (1)首先,创建一张引擎为innoDB的表测试一下delete掉所有数据然后重启Mysql之后,auto_increment的情况:

mysql> CREATE TABLE `table1` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `create_time` datetime DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql> insert into table1(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table1(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table1(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table1(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table1(create_time) values (now());
Query OK, 1 row affected

mysql> select * from table1;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2017-02-28 16:25:11 |
|  2 | 2017-02-28 16:25:21 |
|  3 | 2017-02-28 16:25:23 |
|  4 | 2017-02-28 16:25:23 |
|  5 | 2017-02-28 16:25:24 |
|  6 | 2017-02-28 16:25:26 |
+----+---------------------+
6 rows in set

mysql> delete from table1;
Query OK, 6 rows affected

mysql> select * from table1;
Empty set

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table1;
 
+----------------+
| auto_increment |
+----------------+
|              7 |
+----------------+
1 row in set

    可见,执行delete操作清空表之后,表table1的auto_increment值仍然是正常的。重启数据库之后:

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table1; 
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set

     可见,table1表的auto_increment值变成了1。

     结论:innoDB引擎的表,在执行delete清空操作之后,表的auto_increment值不会受到影响;一旦重启Mysql数据库,那么auto_increment值将变成1!

  (2)下面我们创建一个引擎为MyISAM的表,测试delete掉所有数据,并重启数据库之后auto_increment的值如何变化:

mysql> CREATE TABLE `table2` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `create_time` datetime DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> 
mysql> 
mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table2(create_time) values (now());
Query OK, 1 row affected

mysql> select * from table2;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2017-02-28 17:05:22 |
|  2 | 2017-02-28 17:05:25 |
|  3 | 2017-02-28 17:05:26 |
|  4 | 2017-02-28 17:05:27 |
|  5 | 2017-02-28 17:05:28 |
|  6 | 2017-02-28 17:05:29 |
+----+---------------------+
6 rows in set

mysql> delete from table2;
Query OK, 6 rows affected

mysql> select * from table2;
Empty set

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table2; 
+----------------+
| auto_increment |
+----------------+
|              7 |
+----------------+
1 row in set

    delete清空操作并不会对table2的auto_increment产生任何影响。重启数据库之后:

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table2; 
+----------------+
| auto_increment |
+----------------+
|              7 |
+----------------+
1 row in set

   可见,表table2的auto_increment仍然为7。

   结论:MyISAM引擎的表,在执行delete操作之后,表的auto_increment值不会受到影响;重启Mysql数据库,auto_increment值也不会受到影响!

   2、创建表时指定auto_increment

   本节我们测试创建innoDB引擎的表时指定auto_increment会不会对auto_increment产生影响:

mysql> CREATE TABLE `table3` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `create_time` datetime DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB auto_increment=1000 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table3;
+----------------+
| auto_increment |
+----------------+
|           1000 |
+----------------+
1 row in set

mysql> insert into table3(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table3(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table3(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table3(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table3(create_time) values (now());
Query OK, 1 row affected

mysql> select * from table3;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
| 1000 | 2017-02-28 17:15:13 |
| 1001 | 2017-02-28 17:15:14 |
| 1002 | 2017-02-28 17:15:15 |
| 1003 | 2017-02-28 17:15:15 |
| 1004 | 2017-02-28 17:15:16 |
+------+---------------------+
5 rows in set

mysql> delete from table3;
Query OK, 5 rows affected

mysql> select * from table3;
Empty set

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table3;
+----------------+
| auto_increment |
+----------------+
|           1005 |
+----------------+
1 row in set

     可见,delete操作并不会影响到表table3的auto_increment值。重启数据库之后:

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table3;
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set

   表table3的auto_increment变成了1。

   结论:在创建innoDB表时,无论指定或不指定auto_increment,delete清空+重启数据库都会使表的auto_increment值变成1。

   3、delete的时候添加where 1

   本节讨论在执行delete操作时,加where 1:

mysql> CREATE TABLE `table4` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `create_time` datetime DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> 
mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table4(create_time) values (now());
Query OK, 1 row affected

mysql> select * from table4;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2017-02-28 17:21:33 |
|  2 | 2017-02-28 17:21:34 |
|  3 | 2017-02-28 17:21:35 |
|  4 | 2017-02-28 17:21:36 |
|  5 | 2017-02-28 17:21:36 |
|  6 | 2017-02-28 17:21:37 |
|  7 | 2017-02-28 17:21:38 |
+----+---------------------+
7 rows in set

mysql> delete from table4 where 1;
Query OK, 7 rows affected

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table4;
+----------------+
| auto_increment |
+----------------+
|              8 |
+----------------+
1 row in set

   重启数据库之后:

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table4;
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set

   可见,网上的所流传的delete清空操作时添加where 1并没用。

   结论:delete innoDB表时,添加或不添加where 1,在数据库重启之后auto_increment都会被重置为1。

    4、如果表中有数据,但是数据id小于auto_increment会怎么样?

   本节测试当innoDB表中有数据,但是auto_increment列最大的那个值小于表的auto_increment值会怎样。我们先插入一些数据到表中,然后删除末尾的几条数据:

mysql> CREATE TABLE `table5` (
    -> `id` bigint(20) NOT NULL auto_increment,
    -> `create_time` datetime DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> insert into table5(create_time) values (now());
Query OK, 1 row affected

mysql> select * from table5;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2017-02-28 17:29:29 |
|  2 | 2017-02-28 17:29:30 |
|  3 | 2017-02-28 17:29:30 |
|  4 | 2017-02-28 17:29:30 |
|  5 | 2017-02-28 17:29:31 |
|  6 | 2017-02-28 17:29:31 |
|  7 | 2017-02-28 17:29:32 |
|  8 | 2017-02-28 17:29:32 |
+----+---------------------+
8 rows in set

mysql> delete from table5 where id > 4;
Query OK, 4 rows affected

mysql> select * from table5;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2017-02-28 17:29:29 |
|  2 | 2017-02-28 17:29:30 |
|  3 | 2017-02-28 17:29:30 |
|  4 | 2017-02-28 17:29:30 |
+----+---------------------+
4 rows in set

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table5;
+----------------+
| auto_increment |
+----------------+
|              9 |
+----------------+
1 row in set

    重启数据库之后:

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name=table5;
+----------------+
| auto_increment |
+----------------+
|              5 |
+----------------+
1 row in set

    哇塞,奇迹发生了,table5的auto_increment居然变成了5。由此我们可以得出以下结论。

    结论:Mysql数据库在重启之后,innoDB表的auto_increment值将会被设置为表中auto_increment列的最大值 + 1。

深究

     为什么会出现上述情况呢?

     这是因为,Mysql数据库的的auto_increment值是保存在内存中的,innoDB引擎的表的auto_increment在数据库服务停止时并不会做持久化操作,Mysql会在下次数据库重启的时候,相当于通过执行语句:

select max(id) maxId from table;
alter table auto_increment = maxId + 1;

来设置表table的auto_increment值。

     严格意义上来说这是Mysql的一个bug。这个bug将会在8.0版本中得到修复。关于8.0版本的内容的情况,详见:MySQL 8.0.0 版本发布,亮点都在这了! 以及 MySQL 8.0发布,是时候与MyISAM说再见了

     因为目前8.0版本稳定版尚未发布,所以目前为了避免被这个bug坑到,只能将引擎换为MyISAM或者从程序上去控制。

Mysql数据库之auto_increment