MySQL数据库innoDB动态更改表的AUTO_INCREMENT [英] MYSQL & innoDB alter dynamically AUTO_INCREMENT of a table

问题描述

我遇到了问题，例如在我的系统中，我有一张下表:

I have a problem, for example in my system I have the next table:

CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` FLOAT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- is more complex table

内容:

+-----+-------+
| id  | amount|
+-----+-------+
|2023  |  100 |
|2024  |  223 |
|2025  |  203 |
|...          |
|2505  |  324 |
+-----+-------+

我不知道当前的ID(每天都有销售).我正在尝试标准化表格.

I don't know the current id(There are sales every day). I'm trying to normalize the table.

UPDATE  sales SET id=id - 2022;

结果:

+-----+-------+
| id  | amount|
+-----+-------+
|   1  |  100 |
|   2  |  223 |
|   3  |  203 |
|...          |
| 482  |  324 |
+-----+-------+

问题

我的问题是尝试更改AUTO_INCREMENT，例如:

ALTER TABLE sales AUTO_INCREMENT = 483;

它是正确的，但我不知道当前的ID :(，我尝试以下查询:

Its correct but I don't know the current id :(, I try the following query:

ALTER TABLE sales AUTO_INCREMENT = (SELECT MAX(id) FROM sales );

这会导致我出错(#1064).阅读文档会告诉我:

This causes me a error(#1064). Reading the documentation tells me:

在MySQL中，您不能修改表并在子查询中从同一表中选择.

In MySQL, you cannot modify a table and select from the same table in a subquery.

http://dev.mysql.com/doc/refman/5.7/en/subqueries .html

我尝试白色变量:

SET @new_index = (SELECT MAX(id) FROM sales );
ALTER TABLE sales AUTO_INCREMENT = @new_index;

但是，这会导致错误:(.

But, this causes a error :(.

推荐答案

ALTER TABLE在分析语句时(即在准备时)必须在其中具有文字值.

ALTER TABLE must have literal values in it by the time the statement is parsed (i.e. at prepare time).

您不能在解析时将变量或参数放入语句中，但是可以在解析时 之前将变量放入语句中.这意味着使用动态SQL:

You can't put variables or parameters into the statement at parse time, but you can put variables into the statement before parse time. And that means using dynamic SQL:

SET @new_index = (SELECT MAX(id) FROM sales );
SET @sql = CONCAT('ALTER TABLE sales AUTO_INCREMENT = ', @new_index);
PREPARE st FROM @sql;
EXECUTE st;

这篇关于MySQL数据库innoDB动态更改表的AUTO_INCREMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！