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

问题描述

长话短说，我有一个要导入为skel样式文件的SQL文件，因此将以编程方式重复进行此操作.我可以根据需要编辑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."

现在，我想我可以在我的SQL文件中放入一个INSERT然后是一个UPDATE查询，但是总的来说，有一种方法可以告诉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将INSERT/UPDATE ID解释为0作为下一个序列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屋！