更新MySQL数据库中所有表的AUTO_INCREMENT值 [英] Updating AUTO_INCREMENT value of all tables in a MySQL database

问题描述

可以通过以下方式设置/重置MySQL表的AUTO_INCREMENT值

It is possbile set/reset the AUTO_INCREMENT value of a MySQL table via

ALTER TABLE some_table AUTO_INCREMENT = 1000

但是我需要在其现有值上设置AUTO_INCREMENT(以修复M-M复制)，例如:

However I need to set the AUTO_INCREMENTupon its existing value (to fix M-M replication), something like:

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.

谢谢

推荐答案

使用:

ALTER TABLE some_table AUTO_INCREMENT = 0

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

...will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column.

要在所有表上运行此命令，您需要使用 MySQL的动态SQL语法，称为PreparedStatements ，因为您不能提供ALTER TABLE语句的表名作为变量.您必须遍历以下来源的输出:

To run this over all the tables, you'll need to use MySQL's dynamic SQL syntax called PreparedStatements because you can't supply the table name for an ALTER TABLE statement as a variable. You'll have to loop over the output from:

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

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

...running the ALTER TABLE statement above for each table.

这篇关于更新MySQL数据库中所有表的AUTO_INCREMENT值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！