本文共 1394 字,大约阅读时间需要 4 分钟。
转载地址:
前提情况:
结构
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