通过选择结果更改AUTO_INCREMENT值 [英] Alter AUTO_INCREMENT value by select result

问题描述

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

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'
);

错误：

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:

根据 MySQL Doc ：

InnoDB使用内存自动增量计数器服务器
运行。当服务器停止并重新启动后，InnoDB将为每个表的计数器重新初始化
，以便对表进行第一个INSERT操作，如先前的
所述。

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.

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

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

我有一个名为<$ c的表$ c> ticket 和另一个名为 ticket_backup 的票。他们两个都有共享的列 id 。 门票表中的记录可用，并且可由客户声明。当他们索取 ticket 时，我将记录插入 ticket_backup 内，然后从 ticket中删除它们表。截止到今天，我已经领取了 5.6万票（在 ticket_backup 里面）和0张票。如果我现在重新启动服务器并且不执行 ALTER TABLE ，则我提供的第一张票证将具有 id 1 ，这是 ticket_backup 已经使用的ID，因此如果我不解决自动递增值，就会导致重复键错误。我希望在单个查询中执行此操作的原因是能够轻松地在服务器启动时执行查询。

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.

推荐答案

尝试

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;

这篇关于通过选择结果更改AUTO_INCREMENT值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！