使用子查询mysql更改同一表内的auto_increment [英] change auto_increment within same table using subquery mysql

问题描述

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

I am using mysql. I have a database table with auto_increment counter set. Now because of a requirement I need to leave starting 100 ids free and move all existing records starting from 101, so current id 1 will go to 101 and id 2 will become 102 and so on.

我能够将记录移至101，但问题是如何将auto_increment计数器更改为max(id)+1.

I am able to move records to 101 but the problem is that how to change auto_increment counter to max(id)+1.

这里的主要限制是我需要在单个sql语句中执行此操作.我无法使用@counter保存该值，以后再使用.

Main constraint here with me is that I need to do it in single sql statement. I can not save the value using @counter and use it later.

我尝试使用以下查询

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

但是它不起作用.

推荐答案

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

The parser does not support a subquery in the place you are trying to use it.

以下是MySQL摘录，摘自sql/sql_yacc.yy:

Here's the excerpt from the MySQL source, from sql/sql_yacc.yy:

create_table_option:
    . . .
    | AUTO_INC opt_equal ulonglong_num

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

What you should read there is that the AUTO_INCREMENT table option accepts only a single literal number, not an expression or a subquery or a variable or anything else. So you simply can't set the AUTO_INCREMENT in the same statement in which you do SELECT MAX(id)+1.

但是您不必这样做.

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

MySQL will never allocate an auto-increment id less than the largest value currently in the table. So if you have a table with id value 102, the next value allocated will be at least 103.

您甚至可以尝试明确设置AUTO_INCREMENT = 50，但该值会自动增加到MAX(id)+1.

You can even try to set AUTO_INCREMENT=50 explicitly, but that will be increased automatically to MAX(id)+1.

这篇关于使用子查询mysql更改同一表内的auto_increment的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！