mysql:删除行后重置计数器

Question

删除或截断表会重置计数器 (AUTO_INCRMENT)，但删除选定的行(使用 WHERE 子句)不会重置计数器。

我希望它从删除行的位置继续。

有什么办法可以重置计数器吗？请遵循以下示例以便更好地理解。

mysql> create table dummy(id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (2.09 sec)

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.19 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> delete from dummy where id>4;
Query OK, 2 rows affected (0.23 sec)

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
+----+
10 rows in set (0.00 sec)

我想要什么:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

Answer 1

我们可以在删除语句后查询alter table dummy AUTO_INCRMENT=1;；

因此，一旦我们向表中添加新值，它就会检查现有表，应该为从 0 开始的 id 分配什么值，并分配比最大 id 正好大 1 的值表的内容。

所以，它会是这样的:

mysql> delete from dummy where id>4;
Query OK, 2 rows affected (0.14 sec)

mysql> select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> alter table dummy AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into dummy values (),(),(),(),(),();
Query OK, 6 rows affected (0.18 sec)
Records: 6  Duplicates: 0  Warnings: 0

我们将以顺序形式得到:

select * from dummy;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

谢谢@jspcal