我可以在具有 AUTO_INCRMENT 属性的列中使用零吗
我正在使用 MySQL 5.0 服务器。我的要求是向具有自动增量主键的现有表添加一个特殊行。
如果我们能够将这一行的id设置为0的话，对于以后项目的维护和管理将会非常有用（因为这样很容易记住，也很容易在人工观察中发现）。
现在，我知道 MySQL 对于您使用自己的自动增量列值没有问题，并且我的测试表明我可以使用 UPDATE 查询将行的自动增量主键设置为 0。然而，有人担心这可能会影响未来 INSERT 中列的自动增量功能。
我的（有限的）实验没有显示任何奇怪的情况，并且我在 MySQL 文档中找不到任何针对此的具体警告。也就是说，除此之外（强调我的）： http:// /dev.mysql.com/doc/refman/5.0/en/create-table.html
每个表只能有一个 AUTO_INCREMENT 列，它必须有索引，并且不能有 DEFAULT 值。仅当 AUTO_INCRMENT 列仅包含正值时，它才能正常工作。插入一个负数被视为插入一个非常大的正数。 这样做是为了避免数字从正数“换行”到负数时出现精度问题，同时也是为了确保您不会意外获得包含 0 的 AUTO_INCREMENT 列。
我无法找到具体原因的解释在 AUTO_INCRMENT 列中将值设置为零是错误的，所以谁能告诉我在 AUTO_INCRMENT 列中包含 0 是否是一件坏事？
I'm using a MySQL 5.0 server. My requirement is to add one special row to an existing table which has an auto-increment primary key.
It would be very useful for future maintenance and management of the project if we were able to make the id of this row 0 (because it's easy to remember and easy to spot in manual observations).
Now, I know that MySQL has no problem with you using your own value for an autoincrement column, and my tests have shown that I can set the autoincrement primary key of a row to 0 with an UPDATE query. However, some concerns have been raised about how this might affect the auto-increment functionality of the column in future INSERTs.
My (limited) experiments have shown nothing strange and I can't find anything specific warning against this in the MySQL docs. That is, apart from this (emphasis mine): http://dev.mysql.com/doc/refman/5.0/en/create-table.html
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
I am unable to find an explanation for what is wrong with having a value of zero in an AUTO_INCREMENT column, so can anyone tell me if having an AUTO_INCREMENT column that contains 0 is a bad thing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
正如您已经发现的，不可能使用 INSERT 将 0 分配给自动增量字段，您需要使用 UPDATE。 AFAIK 连续出现 0 没有什么问题，除非您尝试转储和导入。但是可以通过首先插入数据然后将其标记为自动增量字段来避免这种情况。
As you have already discovered, it's not possible to asign a 0 to an auto increment field with an INSERT, you need to use an UPDATE. AFAIK there is nothing wrong with having a 0 in a row except when you try to dump and import. But that can be avoided by first inserting the data and then later marking it as an auto increment field.
不建议存储 0。例如，如果使用
mysqldump转储表，然后重新加载它，MySQL通常会在遇到0值时生成新的序列号，从而导致表的内容与转储的表不同。在重新加载转储文件之前启用 NO_AUTO_VALUE_ON_ZERO解决了这个问题。mysqldump现在自动在其输出中包含一条启用NO_AUTO_VALUE_ON_ZERO的语句，以避免此问题。Storing 0 is not a recommended practice. For example, if you dump the table with
mysqldumpand then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem.mysqldumpnow automatically includes in its output a statement that enablesNO_AUTO_VALUE_ON_ZERO, to avoid this problem.