mysql - 使用子查询mysql在同一个表中更改auto_increment

Question

我正在使用 mysql。我有一个带有 auto_increment 计数器集的数据库表。现在由于一项要求，我需要保留 100 个起始 ID，并从 101 开始移动所有现有记录，因此当前 ID 1 将变为 101，ID 2 将变为 102，依此类推。

我可以将记录移动到 101，但问题是如何将 auto_increment 计数器更改为 max(id)+1。

我的主要限制是我需要在单个 sql 语句中完成。我无法使用 @counter 保存该值并在以后使用它。

我尝试使用下面的查询

ALTER TABLE role AUTO_INCREMENT = (SELECT rd.counter FROM (SELECT (MAX(id) + 1) AS counter FROM role r) rd);

但它不起作用。

Answer 1

解析器在您尝试使用它的地方不支持子查询。

这是 MySQL 源代码的摘录，来自 sql/sql_yacc.yy:

create_table_option:
    . . .
    | AUTO_INC opt_equal ulonglong_num

你应该读到的是，AUTO_INCREMENT 表选项只接受一个文字数字，而不是表达式、子查询、变量或其他任何东西。因此，您根本无法在执行 SELECT MAX(id)+1 的同一语句中设置 AUTO_INCREMENT。

但您不必这样做。

MySQL 永远不会分配小于表中当前最大值的自动递增 id。因此，如果您有一个 ID 值为 102 的表，则分配的下一个值将至少为 103。

您甚至可以尝试显式设置 AUTO_INCREMENT=50，但它会自动增加到 MAX(id)+1。