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 t7G
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 t7G
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 t2G
 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)