mysql自增列的自增问题

  

mysql> create table t(id int not null primary key auto_increment,c1 varchar(50),c2 char(50),dt1 timestamp,dt2 datetime);
#自增列从1开始
mysql> select * from t;
+----+------+------+------+------+
| id | c1   | c2   | dt1  | dt2  |
+----+------+------+------+------+
|  1 | 111  | 111  | NULL | NULL |
|  2 | 222  | 222  | NULL | NULL |
|  3 | 222  | 222  | NULL | NULL |
|  4 | 222  | 222  | NULL | NULL |
|  5 | 222  | 222  | NULL | NULL |
|  6 | 222  | 222  | NULL | NULL |
|  7 | 222  | 222  | NULL | NULL |
|  8 | 222  | 222  | NULL | NULL |
|  9 | 222  | 222  | NULL | NULL |
+----+------+------+------+------+
#删除大于4的ID
mysql> delete from t where id>=4;
Query OK, 6 rows affected (0.01 sec)
mysql> insert into t (c1,c2)values('222','222');
#自增列从10开始
mysql> select * from t;
+----+------+------+------+------+
| id | c1   | c2   | dt1  | dt2  |
+----+------+------+------+------+
|  1 | 111  | 111  | NULL | NULL |
|  2 | 222  | 222  | NULL | NULL |
|  3 | 222  | 222  | NULL | NULL |
| 10 | 222  | 222  | NULL | NULL |
+----+------+------+------+------+
#删除ID=10然后重启数据库
mysql> select * from t;
+----+------+------+------+------+
| id | c1   | c2   | dt1  | dt2  |
+----+------+------+------+------+
|  1 | 111  | 111  | NULL | NULL |
|  2 | 222  | 222  | NULL | NULL |
|  3 | 222  | 222  | NULL | NULL |
+----+------+------+------+------+
#继续插入
mysql> insert into t (c1,c2)values('222','222');
#发现ID已经从4开始了
mysql> select * from t;
+----+------+------+------+------+
| id | c1   | c2   | dt1  | dt2  |
+----+------+------+------+------+
|  1 | 111  | 111  | NULL | NULL |
|  2 | 222  | 222  | NULL | NULL |
|  3 | 222  | 222  | NULL | NULL |
|  4 | 222  | 222  | NULL | NULL |
+----+------+------+------+------+

总结1：如果数据库重启，INNODB的ID会从最大的ID开始自增，否则会产生跳号

#truncate表
mysql> truncate table t;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t (c1,c2)values('222','222');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+----+------+------+------+------+
| id | c1   | c2   | dt1  | dt2  |
+----+------+------+------+------+
|  1 | 222  | 222  | NULL | NULL |
+----+------+------+------+------+
1 row in set (0.00 sec)

总结2：执行truncate后，INNODB的自增ID从1重新开始计数