如何强制 MySQL 将 0 作为有效的自动递增值 [英] How to force MySQL to take 0 as a valid auto-increment value

问题描述

长话短说，我有一个 SQL 文件，我想将其导入为 skel 样式文件，因此这将以编程方式重复执行.我可以随意编辑 SQL 文件，但我不想接触应用程序本身.

Long story short, I have a SQL file that I want to import as a skel style file, so this will be done repeatedly, programmatically. I can edit the SQL file however I want, but I'd rather not touch the application itself.

此应用程序使用 userid = 0 来表示匿名用户.它还在数据库中有一个相关的(空白)条目来表示这个用户".因此，我的 skel.sql 中的行看起来像这样:

This application uses userid = 0 to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this 'user'. Hence, the line in my skel.sql looks something like this:

INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);

问题在于 uid 是一个 auto_increment 字段，从技术上讲，0 是一个无效值.或者至少，如果你将它设置为 0，你基本上是在告诉 MySQL，请在这个字段中插入下一个 id."

The problem with this is that uid is a auto_increment field, for which, technically, 0 is an invalid value. Or atleast, if you set it to 0, you're basically telling MySQL, "Please insert the next id into this field."

现在，我想我可以将一个 INSERT 然后一个 UPDATE 查询放入我的 SQL 文件中，但是有没有办法告诉 MySQL 是的，我实际上想在这个字段中插入 0 吗?

Now, I suppose I could put an INSERT then an UPDATE query into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert 0 into this field?

推荐答案

从答案中我得到了 这里:

您可以使用:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

如此处所述，将防止 MySQL 将 0 的 INSERT/UPDATE ID 解释为下一个序列 ID.此类行为将被限制为 NULL.

Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL.

不过，我认为应用程序的行为非常糟糕.您必须非常小心，以确保其始终如一地使用，尤其是如果您选择在以后实施复制.

It is what I'd consider pretty bad behaviour from the application though. You'll have to be real careful that it's used consistently, especially if you choose to implement replication at a later date.

这篇关于如何强制 MySQL 将 0 作为有效的自动递增值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！