通过选择结果改变AUTO_INCREMENT值。

Basically what I want is a working-version of the following code:

基本上，我想要的是以下代码的工作版本:

ALTER TABLE table_name
AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

The error:

错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT =

The reason:

其原因是:

According to MySQL Doc:

根据MySQL文档:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

InnoDB在服务器运行时使用内存自动增量计数器。当服务器停止并重新启动时，InnoDB重新初始化每个表的计数器，以便第一次插入到表中，如前所述。

This means that whenever I restart the server, my auto_increment values are set to the minimum possible.

这意味着每当我重新启动服务器时，我的auto_increment值就被设置为最小值。

I have a table called ticket and another one called ticket_backup. Both of them have a column id that is shared. Records inside the ticket table are available and can be claimed by customers. When they claim the ticket I insert the record inside ticket_backup and then I erase them from ticket table. As of today, I have 56 thousand tickets already claimed (inside ticket_backup) and 0 tickets available. If I restart the server now and don't perform the ALTER TABLE, the first ticket I make available will have id 1 which is an ID already taken by ticket_backup, thus causing me duplicate key error if I don't fix the auto-increment value. The reason for me to want this in a single query is to be able to easily perform the query on server startup.

我有一个名为ticket的表和另一个名为ticket_backup的表。它们都有一个共享的列id。客票表内的记录是可用的，可以由顾客认领。当他们取回票据时，我将记录插入ticket_backup中，然后我将它们从票务表中删除。到今天为止，我已经认领了56000张票(包含ticket_backup)和0张票。如果我现在重新启动服务器，而不执行ALTER TABLE，那么我提供的第一张票据将会有id 1，这是ticket_backup已经取得的id，因此如果我不修复自动增值值，将导致重复键错误。我希望在单个查询中使用这个功能的原因是能够在服务器启动时轻松执行查询。

3 个解决方案

#1

Try this:

试试这个:

SELECT `AUTO_INCREMENT` INTO @AutoInc
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'another_table_name';

SET @s:=CONCAT('ALTER TABLE `database_name`.`table_name` AUTO_INCREMENT=', @AutoInc);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#2

Just upon quick glance, the error says it all. Syntax is incorrect

只要快速一瞥，错误就说明了一切。语法是错误的

The syntax should adhere to:

语法应遵守:

ALTER TABLE table_name AUTO_INCREMENT=<INTEGER_VALUE>;

So looking at your query, remove the word "SET"

查看查询，删除"SET"

ALTER TABLE table_name AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

#3

What about

是什么

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MAX(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the current increment value or

如果您想要当前的增量值或

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MIN(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the same initial INCREMENT VALUE

如果你想要相同的初始增量值

#1