MYSQL 插入数据主键id不连续

前提情况

结构

mysql> desc tt1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

内容

mysql> select * from tt1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

错误的插入重复记录
mysql> insert into tt1 (name)values('aaa');
ERROR 1062 (23000): Duplicate entry 'aaa' for key 'name'


修改后重新操作


mysql> insert into tt1 (name)values('bbb');
Query OK, 1 row affected (0.00 sec)

再次看内容

mysql> select * from tt1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | bbb  |
+----+------+
2 rows in set (0.00 sec)

问题:id不是我们需要的2,而是3


原因:错误的插入操作,已经影响了自增+1的操作,
     命令是正确的,而数据不符合表要求.
     只要命令不错,自增+1就运行,没毛病.


解决办法
每次插入前,先设置auto_increment=1
也就是运行两条命令完成正确操作


alter table tt1 auto_increment=1;
        insert into tt1 (name)values('ccc');


执行情况如下:

mysql> alter table tt1 auto_increment=1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into tt1 (name)values('ccc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | bbb  |
|  4 | ccc  |
+----+------+
3 rows in set (0.00 sec)t