mysql delete auto_increment列时的注意问题
1. 说明
在对带有AUTO_INCREMENT列的表delete掉所有数据时：
（1）对于MyISAM表，在delete表中所有数据时没有任何风险，随意折腾；
（2）对于InnoDB表，在delete表中所有数据时，是可能有风险的。（delete 完不能重启mysql）
2. MyISAM表
1 MySQL [bosco]> CREATE TABLE `t7` ( 2 -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 3 -> `name` varchar(20) NOT NULL DEFAULT '', 4 -> PRIMARY KEY (`id`) 5 -> ) ENGINE=MyISAM; 6 Query OK, 0 rows affected (0.05 sec) 7 8 MySQL [bosco]> insert into t7(name) values('GZ'),('SH'),('BJ'),('SZ'),('HZ'); 9 Query OK, 5 rows affected (0.03 sec) 10 Records: 5 Duplicates: 0 Warnings: 0 11 12 MySQL [bosco]> select * from t7; 13 +----+------+ 14 | id | name | 15 +----+------+ 16 | 1 | GZ | 17 | 2 | SH | 18 | 3 | BJ | 19 | 4 | SZ | 20 | 5 | HZ | 21 +----+------+ 22 5 rows in set (0.00 sec) 23 24 MySQL [bosco]> delete from t7; 25 Query OK, 5 rows affected (0.03 sec) 26 27 MySQL [bosco]> show create table t7\G 28 *************************** 1. row *************************** 29 Table: t7 30 Create Table: CREATE TABLE `t7` ( 31 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 32 `name` varchar(20) NOT NULL DEFAULT '', 33 PRIMARY KEY (`id`) 34 ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 35 1 row in set (0.00 sec) 36 37 MySQL [bosco]> insert into t7(name) values('NJ'); 38 Query OK, 1 row affected (0.07 sec) 39 40 MySQL [bosco]> select * from t7; 41 +----+------+ 42 | id | name | 43 +----+------+ 44 | 6 | NJ | 45 +----+------+ 46 1 row in set (0.00 sec) 47 48 49 如果在delete from t2后，重启了mysqld，AUTO_INCREMENT并不会被重置： 50 MySQL [bosco]> show create table t7\G 51 *************************** 1. row *************************** 52 Table: t7 53 Create Table: CREATE TABLE `t7` ( 54 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 55 `name` varchar(20) NOT NULL DEFAULT '', 56 PRIMARY KEY (`id`) 57 ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 58 1 row in set (0.00 sec)
3. InnoDB表
1 MySQL [bosco]> show create table t2\G 2 *************************** 1. row *************************** 3 Table: t2 4 Create Table: CREATE TABLE `t2` ( 5 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 6 `name` varchar(20) NOT NULL DEFAULT '', 7 PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 9 1 row in set (0.00 sec) 10 11 12 MySQL [bosco]> insert into t2(name) values('GZ'),('SH'),('BJ'),('SZ'),('HZ'); 13 Query OK, 5 rows affected (0.04 sec) 14 Records: 5 Duplicates: 0 Warnings: 0 15 16 17 MySQL [bosco1]> select * from t2; 18 +----+------+ 19 | id | name | 20 +----+------+ 21 | 1 | GZ | 22 | 2 | SH | 23 | 3 | BJ | 24 | 4 | SZ | 25 | 5 | HZ | 26 +----+------+ 27 5 rows in set (0.00 sec) 28 29 30 MySQL [bosco]> delete from t2; ## 直接将t2表中的数据全部删除，使用的是delete而非truncate 31 Query OK, 5 rows affected (0.04 sec) 32 33 34 MySQL [bosco]> insert into t2(name) values('NJ'); ## 再次插入数据时，自增id会自动为下一编号 35 Query OK, 1 row affected (0.02 sec) 36 37 38 MySQL [bosco]> select * from t2; 39 +----+------+ 40 | id | name | 41 +----+------+ 42 | 6 | NJ | 43 +----+------+ 44 1 row in set (0.00 sec) 45 46 47 但是，如果在delete from t2后，重启了mysqld，那么情况完全不一样了，这个重启会重置id。 48 MySQL [bosco]> insert into t2(name) values('FJ'); ## 再次插入数据时，自增id会被重置编号 49 Query OK, 1 row affected (0.02 sec) 50 51 52 MySQL [bosco]> select * from t2; 53 +----+------+ 54 | id | name | 55 +----+------+ 56 | 1 | FJ | ## 再次插入新值时，id编号会重新从头开始 57 +----+------+ 58 1 row in set (0.00 sec)
