更新 MySQL 数据库中所有表的 AUTO_INCREMENT 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3595584/
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
Updating AUTO_INCREMENT value of all tables in a MySQL database
提问by pars
It is possbile set/reset the AUTO_INCREMENTvalue of a MySQL table via
可以通过以下方式设置/重置AUTO_INCREMENTMySQL 表的值
ALTER TABLE some_table AUTO_INCREMENT = 1000
ALTER TABLE some_table AUTO_INCREMENT = 1000
However I need to set the AUTO_INCREMENTupon its existing value (to fix M-M replication), something like:
但是我需要设置AUTO_INCREMENT其现有值（以修复 MM 复制），例如：
ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1which is not working
ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1这不起作用
Well actually, I would like to run this query for all tables within a database. But actually this is not very crucial.
实际上，我想对数据库中的所有表运行此查询。但实际上这并不是很关键。
I could not find out a way to deal with this problem, except running the queries manually. Will you please suggest something or point me out to some ideas.
除了手动运行查询之外，我找不到解决此问题的方法。请你提出一些建议或指出我的一些想法。
Thanks
谢谢
回答by OMG Ponies
Using:
使用：
ALTER TABLE some_table AUTO_INCREMENT = 0
...will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column.
...将根据 auto_increment 列中的最高现有值将 auto_increment 值重置为下一个值。
To run this over all the tables, you'll need to use MySQL's dynamic SQL syntax called PreparedStatementsbecause you can't supply the table name for an ALTER TABLE statement as a variable. You'll have to loop over the output from:
要在所有表上运行此操作，您需要使用MySQL 的名为 PreparedStatements 的动态 SQL 语法，因为您无法将表名作为变量提供给 ALTER TABLE 语句。您必须遍历以下输出：
SELECT t.table_name
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_schema = 'your_database_name'
...running the ALTER TABLE statement above for each table.
...为每个表运行上面的 ALTER TABLE 语句。
回答by the_nuts
set @db = 'your_db_name';
SELECT concat('ALTER TABLE ', @db, '.', TABLE_NAME, ' AUTO_INCREMENT = 0;')
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db AND TABLE_TYPE = 'BASE TABLE'
Then copy-paste and run the output you get.
然后复制粘贴并运行你得到的输出。
回答by nicholas.alipaz
In the below instructions you will need to replace everything that is in [brackets] with your correct value. BACKUP BEFORE ATTEMPTING.
在下面的说明中，您需要用正确的值替换 [括号] 中的所有内容。尝试前备份。
If you can login to mysql as root through the command line then you could do the following to reset the auto_increment on all tables, first we will construct our queries which we want to run:
如果您可以通过命令行以 root 身份登录 mysql，那么您可以执行以下操作来重置所有表上的 auto_increment，首先我们将构建我们想要运行的查询：
Make a database backup:
进行数据库备份：
mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]
Login:
登录：
mysql -u root -p
Set the group_concat_max_length to a higher value so our list of queries doesn't get truncated:
将 group_concat_max_length 设置为更高的值，这样我们的查询列表就不会被截断：
SET group_concat_max_len=100000;
Create our list of queries by using the following:
使用以下命令创建我们的查询列表：
SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " AUTO_INCREMENT = 0") SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "[DATABASENAME]";
Then you will receive a long string of mysql queries followed by a bunch of dashes. Copy the string of queries to your clipboard, it will look something similar to:
然后你会收到一长串 mysql 查询，后面跟着一堆破折号。将查询字符串复制到剪贴板，它看起来类似于：
ALTER table1 AUTO_INCREMENT = 0;ALTER table2 AUTO_INCREMENT = 0;...continued...
Change to the database you would like to run the command on:
更改为要在其上运行命令的数据库：
USE [DATABASENAME];
Then paste the string that is on your clipboard and hit enter to run it. This should run the alter on every table in your database.
然后粘贴剪贴板上的字符串并按回车键运行它。这应该在数据库中的每个表上运行更改。
Messed up? Restore from your backup, be sure to logout of mysql before running the following (just type exit;to do so)
弄乱？从备份中恢复，请确保在运行以下命令之前注销 mysql（只需键入exit;即可）
gzip -d < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]
I will not take responsibility for any damage cause by your use of any of these commands, use at your own risk.
对于您使用这些命令中的任何一个而造成的任何损害，我将不承担任何责任，使用风险自负。
回答by symcbean
Assuming that you must fix this by amending the auto-increment column rather than the the foreign keys in the table decomposing the N:M relationship, and that you can pridict what the right values are, try using a temporary table where the relevant column is not auto-increment, then map this back in place of the original table and change the column type to auto-increment afterwards, or truncate the original table and load the data from the temp table.
假设您必须通过修改自动增量列而不是分解 N:M 关系的表中的外键来解决此问题，并且您可以预测正确的值是什么，请尝试使用相关列所在的临时表不自动递增，然后将其映射回原始表，然后将列类型更改为自动递增，或者截断原始表并从临时表加载数据。
回答by Yes Barry
I found this gist on github and it worked like a charm for me: https://gist.github.com/abhinavlal/4571478
我在 github 上找到了这个要点，它对我来说就像一个魅力：https: //gist.github.com/abhinavlal/4571478
The command:
命令：
mysql -Nsr -e "SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.table_schema = 'DB_NAME'" | xargs -I {} mysql DB_NAME -e "ALTER TABLE {} AUTO_INCREMENT = 1;"
If your DB requires a password, you unfortunately have to put that in the command for it to work. One work-around (still not greatbut works) is to put the password in a secure file. You can always delete the file after so the password doesn't stay in your command history:
如果您的数据库需要密码，不幸的是，您必须将其放入命令中才能使其工作。一种解决方法（仍然不是很好但有效）是将密码放在安全文件中。您可以随时删除该文件，以便密码不会保留在您的命令历史记录中：
... | xargs -I {} mysql -u root -p`cat /path/to/pw.txt` DB_NAME -e...
回答by Lovesh Pareek
I have written below procedure change the database name and execute the procedure
我写了下面的程序更改数据库名称并执行该程序
CREATE DEFINER=`root`@`localhost` PROCEDURE `setAutoIncrement`()
BEGIN
DECLARE done int default false;
DECLARE table_name CHAR(255);
DECLARE cur1 cursor for SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_schema = "buzzer_verifone";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
myloop: loop
fetch cur1 into table_name;
if done then
leave myloop;
end if;
set @sql = CONCAT('ALTER TABLE ',table_name, ' AUTO_INCREMENT = 1');
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end loop;
close cur1;
END
Execute the procedure above using below line
使用下面的行执行上面的过程
Call setAutoIncrement();
回答by kumar
Reset mysql table auto increment was very easy, we can do it with single query, please see this http://webobserve.blogspot.com/2011/02/reset-mysql-table-autoincrement.html.
重置 mysql 表自动增量非常简单，我们可以通过单个查询来完成，请参阅http://webobserve.blogspot.com/2011/02/reset-mysql-table-autoincrement.html。
