时间：2021-07-01 10:21:17 帮助过：11人阅读
在数据库应用中，我们经常需要用到自动递增的唯一编号来标识记录。在MySQL中，可通过数据列的auto_increment属性来自动生成。可在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值，当然在设置的时候Mysql会取数据表中auto_increment列的最大值 + 1与n中的较大者作为新的auto_increment值。
Myql的auto_increment属性具有以下特性：
实际应用中发现，在delete掉某张innoDB表的全部数据并重启Mysql会导致该表的auto_increment列变为1。特测试多种情况下auto_increment列的变化并记录如下。
（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值也不会受到影响！
本节我们测试创建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。
本节讨论在执行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。
本节测试当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 au