mysql的id=2重复_MySQL重复ID

不同的存储引擎以不同方式处理自动增量.例如,使用MyISAM,下一个自动增量值将保持不变,这样,如果重新启动MySQL服务器,它将保留该自动增量值.

但是,InnoDB不会保留下一个自动增量值,因此如果重新启动MySQL服务器,它将计算当前最大值并从那里开始递增.

这与您相关,因为您使用的是InnoDB.因此,如果100是表中的最大id值,则删除该行,然后重新启动MySQL服务器,然后在下一次插入时重新使用100.

这是一个简单的例子来说明这一点：

mysql> CREATE TABLE `bill` (

-> `id` int(11) NOT NULL auto_increment,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.12 sec)

mysql> -- start at 99 to force next value to 100

mysql> insert into bill values (99);

Query OK, 1 row affected (0.01 sec)

mysql> -- use auto-increment, should be 100

mysql> insert into bill values (null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from bill;

+-----+

| id |

+-----+

| 99 |

| 100 |

+-----+

2 rows in set (0.00 sec)

mysql> -- delete max value

mysql> delete from bill where id = 100;

Query OK, 1 row affected (0.00 sec)

mysql> -- use auto-increment, should be 101

mysql> insert into bill values (null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from bill;

+-----+

| id |

+-----+

| 99 |

| 101 |

+-----+

2 rows in set (0.00 sec)

mysql> -- delete max value

mysql> delete from bill where id = 101;

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> /*** RESTART MYSQL ***/

mysql>

mysql> -- use auto-increment, should be 100

mysql> insert into bill values (null);

Query OK, 1 row affected (0.01 sec)

mysql> select * from bill;

+-----+

| id |

+-----+

| 99 |

| 100 |

+-----+

2 rows in set (0.00 sec)