有没有办法对数据库中的所有表运行此命令或类似的AUTO_INCREMENT命令?
ALTER TABLE tablename AUTO_INCREMENT = 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;
您可以使用 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;"