1. 说明

在对带有AUTO_INCREMENT列的表delete掉所有数据时:

(1)对于MyISAM表,在delete表中所有数据时没有任何风险,随意折腾;

(2)对于InnoDB表,在delete表中所有数据时,是可能有风险的。(delete 完不能重启mysql)

2. MyISAM表

 MySQL [bosco]> CREATE TABLE `t7` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(20) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec) MySQL [bosco]> insert into t7(name) values('GZ'),('SH'),('BJ'),('SZ'),('HZ');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0 MySQL [bosco]> select * from t7;
+----+------+
| id | name |
+----+------+
| 1 | GZ |
| 2 | SH |
| 3 | BJ |
| 4 | SZ |
| 5 | HZ |
+----+------+
5 rows in set (0.00 sec) MySQL [bosco]> delete from t7;
Query OK, 5 rows affected (0.03 sec) MySQL [bosco]> show create table t7\G
*************************** 1. row ***************************
Table: t7
Create Table: CREATE TABLE `t7` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) MySQL [bosco]> insert into t7(name) values('NJ');
Query OK, 1 row affected (0.07 sec) MySQL [bosco]> select * from t7;
+----+------+
| id | name |
+----+------+
| 6 | NJ |
+----+------+
1 row in set (0.00 sec) 如果在delete from t2后,重启了mysqld,AUTO_INCREMENT并不会被重置:
MySQL [bosco]> show create table t7\G
*************************** 1. row ***************************
Table: t7
Create Table: CREATE TABLE `t7` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. InnoDB表

 MySQL [bosco]> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec) MySQL [bosco]> insert into t2(name) values('GZ'),('SH'),('BJ'),('SZ'),('HZ');
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0 MySQL [bosco1]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | GZ |
| 2 | SH |
| 3 | BJ |
| 4 | SZ |
| 5 | HZ |
+----+------+
5 rows in set (0.00 sec) MySQL [bosco]> delete from t2; ## 直接将t2表中的数据全部删除,使用的是delete而非truncate
Query OK, 5 rows affected (0.04 sec) MySQL [bosco]> insert into t2(name) values('NJ'); ## 再次插入数据时,自增id会自动为下一编号
Query OK, 1 row affected (0.02 sec) MySQL [bosco]> select * from t2;
+----+------+
| id | name |
+----+------+
| 6 | NJ |
+----+------+
1 row in set (0.00 sec) 但是,如果在delete from t2后,重启了mysqld,那么情况完全不一样了,这个重启会重置id。
MySQL [bosco]> insert into t2(name) values('FJ'); ## 再次插入数据时,自增id会被重置编号
Query OK, 1 row affected (0.02 sec) MySQL [bosco]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | FJ | ## 再次插入新值时,id编号会重新从头开始
+----+------+
1 row in set (0.00 sec)
05-20 05:31