更新 MySQL 数据库中所有表的 AUTO_INCRMENT 值

Question

可以通过

ALTER TABLE some_table AUTO_INCRMENT = 1000

设置/重置MySQL表的AUTO_INCRMENT值，但是我需要在其上设置AUTO_INCRMENT现有值（用于修复 MM 复制），例如：

ALTER TABLE some_table SET AUTO_INCRMENT = AUTO_INCRMENT + 1 ，这不起作用

实际上，我想对数据库中的所有表运行此查询。但实际上这并不是很重要。

除了手动运行查询之外，我找不到解决此问题的方法。请您提出一些建议或给我指出一些想法好吗？

谢谢

Answer 1

使用：

ALTER TABLE some_table AUTO_INCREMENT = 0

...将根据 auto_increment 列中现有的最高值将 auto_increment 值重置为下一个值。

要在所有表上运行此命令，您需要使用 MySQL 的动态 SQL 语法称为“PreparedStatements”，因为您无法将 ALTER TABLE 语句的表名作为变量提供。您必须循环遍历以下输出：

SELECT t.table_name
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.table_schema = 'your_database_name'

...为每个表运行上面的 ALTER TABLE 语句。

Answer 2

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'

然后复制粘贴并运行得到的输出。

Answer 3

在下面的说明中，您需要将 [括号] 中的所有内容替换为正确的值。尝试之前进行备份。

如果您可以通过命令行以 root 身份登录 mysql，那么您可以执行以下操作来重置所有表上的 auto_increment，首先我们将构建我们想要运行的查询：

进行数据库备份：

mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]

登录：

mysql -u root -p

将 group_concat_max_length 设置为更高的值，这样我们的查询列表就不会被截断：

SET group_concat_max_len=100000;

使用以下命令创建我们的查询列表：

SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " AUTO_INCREMENT = 0") SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "[DATABASENAME]";

然后您将收到一长串 mysql 查询，后跟一堆破折号。将查询字符串复制到剪贴板，它看起来类似于：

ALTER table1 AUTO_INCREMENT = 0;ALTER table2 AUTO_INCREMENT = 0;...continued...

更改为要运行命令的数据库：

USE [DATABASENAME];

然后粘贴剪贴板上的字符串并按 Enter 运行它。这应该对数据库中的每个表运行更改。

搞砸了吗？从您的备份恢复，请务必在运行以下命令之前注销 mysql（只需输入 exit; 即可），

gzip -d < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]

我不会对您使用任何这些命令造成的任何损害承担责任，请使用风险由您自行承担。

Answer 4

我在 github 上找到了这个要点，它对我来说就像一个魅力： https://gist.github.com /abhinavlal/4571478

命令：

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;"

如果您的数据库需要密码，不幸的是您必须将其放入命令中才能使其工作。一种解决方法（仍然不是很好，但有效）是将密码放在安全文件中。您可以随时删除该文件，这样密码就不会保留在您的命令历史记录中：

 ... | xargs -I {} mysql -u root -p`cat /path/to/pw.txt` DB_NAME -e...

Answer 5

假设您必须通过修改自动增量列而不是表中分解 N:M 关系的外键来解决此问题，并且您可以预测正确的值是什么，请尝试使用相关列不存在的临时表自动增量，然后将其映射回原始表的位置，然后将列类型更改为自动增量，或者截断原始表并从临时表加载数据。

Answer 6

我编写了下面的过程，更改数据库名称并执行该过程

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

使用下面的行执行上面的过程

Call setAutoIncrement();

Answer 7

在 MySQL 数据库中更新/重置 AUTO_INCRMENT 的最快解决方案

确保 AUTO_INCRMENT 列尚未在另一个表上用作 FOREIGN_KEY。

首先，将 AUTO_INCRMENT COLUMN 删除为：

ALTER TABLE table_name DROP column_name

示例：ALTER TABLE payment DROP payment_id

然后重新添加该列，并将其移动为表中的第一列

ALTER TABLE table_name ADD column_name DATATYPE AUTO_INCREMENT PRIMARY KEY FIRST

示例：<代码>更改表付款添加 payment_id INT AUTO_INCRMENT PRIMARY KEY FIRST

Answer 8

重置mysql表自动增量非常简单，我们可以通过单个查询来完成，请参阅此http://webobserve.blogspot.com/2011/02/reset-mysql-table-autoincrement.html。