MySQL自动增量? [英] MySQL Auto Increment?

问题描述

我有2个不同的MySQL服务器. 一个是5.6.12，另一个是5.5.25a.

I have 2 different MySQL servers. One is 5.6.12 the other 5.5.25a.

在5.5版服务器中，我可以这样做:

In the 5.5 version server I can do this:

ALTER TABLE AUTO_INCREMENT = 100;
ALTER TABLE AUTO_INCREMENT = 50;

5.6版本仅允许我将auto_increment更改为比当时的auto_increment更高的值.

The 5.6 version only allows me to change the auto_increment to a higher value than auto_increment is in that moment.

当然，我不是想将值更改为比具有最大值的记录更低的值.

Of course I'm not trying to change the value to a lower value than the record that has the maximum value.

两个MySQL服务器都是InnoDB

Both MySQL servers are InnoDB

我需要在最新版本中使用该命令

I need to use that commen in the newest version

有任何线索吗?

推荐答案

此查询在mysql 5.6.12和5.5.25中均有效:

This query works in both mysql 5.6.12 and 5.5.25:

ALTER TABLE  `table_name` AUTO_INCREMENT =10

根据 mysql手册:

您不能将计数器重置为小于或等于该值的值 已经被使用了.对于MyISAM，如果该值小于或等于 到当前在AUTO_INCREMENT列中的最大值，该值 重置为当前最大值加一. 对于InnoDB，如果值为 小于列中的当前最大值，不会发生错误，并且 当前序列值不会更改.

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

如果表中没有记录，则可以使用TRUNCATE:

If the table has no records, you can use TRUNCATE:

TRUNCATE TABLE table_name

从 mysql手册:

将任何AUTO_INCREMENT值重置为其初始值.这是真实的 即使对于MyISAM和InnoDB，通常不会重用序列 值.

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

---

更新:看来mysql5.6.12有一些错误: http://bugs.mysql.com/bug.php?id=69882

这篇关于MySQL自动增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！