如何使用另一个表中的 MAX 值重置 MySQL AutoIncrement？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2410689/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Reset an MySQL AutoIncrement using a MAX value from another table?
提问by ThinkCode
I know this won't work, tried it in various forms and failed all times. What is the simplest way to achieve the following result?
我知道这行不通，以各种形式尝试过，但总是失败。实现以下结果的最简单方法是什么？
ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC);
This is great for automation projects. Thank you!
这对于自动化项目非常有用。谢谢！
SELECT @max := (max(ID)+1) from ABC; -> This works!
select ID from ABC where ID = (@max-1); -> This works!
ALTER TABLE XYZ AUTO_INCREMENT = (@max+1); -> This fails :( Why?
回答by OMG Ponies
Use a Prepared Statement:
使用准备好的语句：
SELECT @max := MAX(ID)+ 1 FROM ABC;
PREPARE stmt FROM 'ALTER TABLE ABC AUTO_INCREMENT = ?';
EXECUTE stmt USING @max;
DEALLOCATE PREPARE stmt;
回答by Saeed
following the mysql docs, this worked for me in mysql 5.7:
按照mysql docs，这在 mysql 5.7 中对我有用：
SET @m = (SELECT MAX(id) + 1 FROM ABC);
SET @s = CONCAT('ALTER TABLE XYZ AUTO_INCREMENT=', @m);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
回答by user357516
I'm creating an automated database transformation script for a new version of my application.
我正在为我的应用程序的新版本创建一个自动数据库转换脚本。
In one table, I needed to change the primary auto-increment field to a different field. Since this page came up first many times while I googled a solution for it, here's a solution that eventually worked for me:
在一张表中，我需要将主要的自动增量字段更改为不同的字段。由于这个页面在我用谷歌搜索解决方案时第一次出现很多次，这里有一个最终对我有用的解决方案：
-- Build a new ID field from entry_id, make it primary and fix the auto_increment for it:
ALTER TABLE `entries` ADD `id` INT UNSIGNED NOT NULL FIRST;
UPDATE entries SET id = entry_id;
ALTER TABLE `entries` ADD PRIMARY KEY ( `id` );
-- ...the tricky part of it:
select @ai := (select max(entry_id)+1 from entries);
set @qry = concat('alter table entries auto_increment=',@ai);
prepare stmt from @qry; execute stmt;
-- ...And now it's possible to switch on the auto_increment:
ALTER TABLE `entries` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
回答by Anurag
Who is having problem with PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?'can use
谁对PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT =有问题？可以使用
CREATE PROCEDURE reset_xyz_autoincrement
BEGIN
SELECT @max := MAX(ID)+ 1 FROM ABC;
set @alter_statement = concat('ALTER TABLE temp_job_version AUTO_INCREMENT = ', @max);
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
回答by Artistan
Reset Auto Increment IDs.
重置自动增量 ID。
http://community.spiceworks.com/scripts/show/3042-reset-auto-increment-ids
http://community.spiceworks.com/scripts/show/3042-reset-auto-increment-ids
update all auto increment columns in a database to the smallest possible value based on current values in the databases. We needed to do this after cleaning out a database.
根据数据库中的当前值，将数据库中的所有自动增量列更新为可能的最小值。我们需要在清理数据库后执行此操作。
Use a Prepared Statement within a Stored Procedure:
在存储过程中使用准备好的语句：
drop PROCEDURE if exists reset_autoincrement;
DELIMITER //
CREATE PROCEDURE reset_autoincrement (IN schemaName varchar(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE o_name VARCHAR(255);
DECLARE o_table VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.`COLUMNS` WHERE extra LIKE '%auto_increment%' and table_schema=schemaName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO o_name, o_table;
IF done THEN
LEAVE read_loop;
END IF;
set @qry1 = concat('SELECT MAX(`',o_name,'`) + 1 as autoincrement FROM `',o_table,'` INTO @ai');
PREPARE stmt1 FROM @qry1;
EXECUTE stmt1;
IF @ai IS NOT NULL THEN
SELECT o_name, o_table;
select @qry1;
select @ai;
set @qry2 = concat('ALTER TABLE `',o_table,'` AUTO_INCREMENT = ', @ai);
select @qry2;
PREPARE stmt2 FROM @qry2;
EXECUTE stmt2;
END IF;
END LOOP;
CLOSE cur1;
END //
DELIMITER ;
call reset_autoincrement('my_schema_name');
回答by ThinkCode
Ok guys. I have come up with a not so intuitive solution. The best part is that it works!
好，朋友们。我想出了一个不太直观的解决方案。最好的部分是它有效！
SELECT @max := max(ID) from ABC;
ALTER TABLE XYZ AUTO_INCREMENT = 1;
ALTER TABLE XYZ ADD column ID INTEGER primary key auto_increment;
UPDATE XYZ SET ContactID = (ContactID + @max);
回答by Ike Walker
If you really want to do this in MySQL alone, you can just dump the dynamically built alter command to a file on disk and then execute it.
如果您真的想单独在 MySQL 中执行此操作，您可以将动态构建的 alter 命令转储到磁盘上的文件，然后执行它。
Like so:
像这样：
select concat('ALTER TABLE XYZ AUTO_INCREMENT = ',max(ID)+1,';') as alter_stmt
into outfile '/tmp/alter_xyz_auto_increment.sql'
from ABC;
\. /tmp/alter_xyz_auto_increment.sql
回答by Andy Shellam
Personally I'd probably use either a shell script or a little C#/C++ application or PHP/Ruby/Perl script to do this in 2 queries:
就我个人而言，我可能会使用 shell 脚本或一些 C#/C++ 应用程序或 PHP/Ruby/Perl 脚本在 2 个查询中执行此操作：
- Grab the value you want
SELECT MAX(ID) FROM ABC; - Alter the table using the value
ALTER TABLE XYZ AUTO_INCREMENT = <insert value retrieved from first query here>
- 抓住你想要的价值
SELECT MAX(ID) FROM ABC; - 使用值更改表
ALTER TABLE XYZ AUTO_INCREMENT = <insert value retrieved from first query here>
Obviously being careful that the new auto increment won't cause any key clashes with existing data in the XYZtable.
显然要小心，新的自动增量不会导致与XYZ表中现有数据的任何键冲突。
回答by Ji?í Dvo?ák
I am only an amateur, but I have a tip :-) If you fills your entire table with new data, you can just use before filling:
我只是一个业余爱好者，但我有一个提示:-) 如果你用新数据填充整个表格，你可以在填充之前使用：
mysql_query( "TRUNCATE TABLE `your_table`" ) or die( mysql_error());
I mean, it also resets auto_increment (and erases all content; Yes, table will stay).
我的意思是，它还重置 auto_increment （并擦除所有内容；是的，表将保留）。
You can backup form your data, the after truncate insert into this without id.
你可以备份你的数据，在没有id的情况下截断后插入。
