自动将mysql autoincrement设置为最小值 [英] Automatically set mysql autoincrement to min value

问题描述

我只是想知道，是否有一种优雅的方法可以根据当前内容自动将mysql表的自动增量重置为最低值.

i just wondered, if there is an elegant way of automatically resetting the autoincrement of a mysql table to the lowest value according to the present content.

示例:

mytable:

1  content of row 1
2  content of row 2
3  content of row 3
4  content of row 4
5  content of row 5

现在自动增量将为6

但是在我插入新内容之前，我先删除第3,4和5行.内容看起来像这样:

but before i insert new contents, i delete row 3,4 and 5. the content would look like this:

1  content of row 1
2  content of row 2

自动增量仍为6.

这就是问题所在.

我希望自动增量为3，因为根据插入的ID，它是可能的最低值.

i would like the autoincrement to be at 3, because it is the lowest possible value according to the inserted IDs.

如果自动递增将无限地"增长并超出12位长整数的范围，则将防止出现非常大的数字.

the would prevent extremely large numbers, if the autoincrement would grow "infinitely" and get out of range of a 12 digits long integer.

感谢您的任何建议！

推荐答案

这取决于您的存储引擎，

This depends on your storage engine,

对于MyISAM和MySQL 5.6+上的InnoDB，您可以将表的AUTO_INCREMENT值设置为1.该值将自动增加到最大当前值+1.执行此操作.

For MyISAM and for InnoDB on MySQL 5.6+, you can set the AUTO_INCREMENT value for the table to say 1. That value will automatically be increased to the maximum current value + 1. Do that like this.

ALTER TABLE table_name AUTO_INCREMENT = 1;

对于MySQL上的InnoDB< 5.6，这将无法正常工作，您将需要手动执行以下操作:

For InnoDB on MySQL < 5.6, this will not work and you will need to do this manually like this:

SELECT MAX(autoincrement_field) + 1 FROM table_name INTO @maxautoinc;
ALTER TABLE table_name AUTO_INCREMENT = @maxautoinc;

请注意，在最后一种情况下，两个查询将需要在相同的数据库连接下运行.

Note in that last case, the two queries will need to be run with the same database connection.

这篇关于自动将mysql autoincrement设置为最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！