如何重置MySQL中一个数据库中所有表的AUTO_INCREMENT值？

Question

有没有一种方法可以在数据库中运行这个或类似的AUTO_INCREMENT命令来处理所有表？

ALTER TABLE tablename AUTO_INCREMENT = 1

- Primoz Rome

---

如果问题与原始问题没有直接关系，我认为您不应该编辑您的问题。请开一个新问题。 - Chris Stryczynski

@ChrisStryczynski 好的，我会的...谢谢。 - Primoz Rome

Answer 1

试试这个：

SELECT GROUP_CONCAT(`t`.`query` SEPARATOR '; ')
FROM
    (
        SELECT 
            CONCAT('ALTER TABLE `', `a`.`table_name`, '` AUTO_INCREMENT = 1') AS `query`,
            '1' AS `id`
        FROM `information_schema`.`tables` AS `a`
        WHERE `a`.`TABLE_SCHEMA` = 'my_database' # <<< change this to your target database
          #AND `a`.`TABLE_TYPE` = 'BASE TABLE' << optional
          AND `a`.`AUTO_INCREMENT` IS NOT NULL
    ) AS `t`
GROUP BY `t`.`id`

这将为每个表生成一个查询，但不会运行这些查询，因此您需要自己运行生成的查询。

结果将如下所示：

ALTER TABLE `tablename1` AUTO_INCREMENT = 1; ALTER TABLE `tablename2` AUTO_INCREMENT = 1; ALTER TABLE `tablename3` AUTO_INCREMENT = 1;

- evilReiko

---

我认为你的查询语句中有错误，因为它返回了“MySQL返回了一个空结果集（即零行）”。 - Primoz Rome

好的，我的错误...我看错了模式。 - Primoz Rome

对我不起作用，你是假定所有的表都有一个id列吗？ - Roberto Sepúlveda Bravo

太好了，谢谢 ^_^。顺便说一下，WHERE a.table_schema = 'SCHEMA_NAME'不是表名。 - Gag Baghdasaryan

@GagBaghdasaryan已修复查询，谢谢，现在应该可以正常工作了。 - evilReiko

Answer 2

您可以使用xargs（替换DB_NAME）：

mysql -Nsr -e "SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.table_schema = 'DB_NAME' AND table_type = 'BASE TABLE'" | xargs -I {} mysql DB_NAME -e "ALTER TABLE {} AUTO_INCREMENT = 1;"

- Chris Stryczynski

---

你可能想要添加 and table_type = 'BASE TABLE'。 - fancyPants

谢谢，您能否也检查一下我更新的问题吗？如果可能的话。 - Primoz Rome

此外，MySQL也支持直接执行此操作。例如，您可以执行select concat("alter table ", table_name...，将其写入临时文件中的into outfile，然后再次使用source <temporary file>进行操作。有多种方法可以实现。 - fancyPants

重置的意思是删除数据并从头开始吗？因此要进行批量截断。 - RoMEoMusTDiE