如何强制 MySQL 将 0 作为有效的自动递增值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1142472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to force MySQL to take 0 as a valid auto-increment value
提问by Matthew Scharley
Long story short, I have a SQL file that I want to import as a skelstyle 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.
长话短说，我有一个 SQL 文件，我想将其作为skel样式文件导入，因此这将以编程方式重复执行。我可以随心所欲地编辑 SQL 文件，但我不想接触应用程序本身。
This application uses userid = 0to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this 'user'. Hence, the line in my skel.sqllooks something like this:
此应用程序用于userid = 0表示匿名用户。它在数据库中还有一个相关的（空白）条目来表示这个“用户”。因此，我的行skel.sql看起来像这样：
INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);
The problem with this is that uidis a auto_incrementfield, for which, technically, 0is an invalid value. Or atleast, if you set it to 0, you're basically telling MySQL, "Please insert the next id into this field."
问题在于这uid是一个auto_increment字段，从技术上讲，它0是一个无效值。或者至少，如果你将它设置为 0，你基本上是在告诉 MySQL，“请在这个字段中插入下一个 id。”
Now, I suppose I could put an INSERTthen an UPDATEquery into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert 0into this field?
现在，我想我可以把一个INSERT那么UPDATE查询到我的SQL文件，但有没有告诉MySQL的一般，是的，其实我是想以插入的方式0进入这个领域？
回答by Matthew Scharley
From the answer I got here:
从答案我在这里：
You can use:
您可以使用：
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'
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.
其如所描述的在这里，将防止从MySQL的解释0-2的插入/更新ID为下一个序列ID。这种行为将被限制为 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.
不过，我认为应用程序的行为非常糟糕。您必须非常小心它的使用是否一致，特别是如果您选择在以后实施复制。
回答by I.G. Pascual
Check your sql DB mode with:
使用以下命令检查您的 sql DB 模式：
SELECT @@[GLOBAL|SESSION].sql_mode;
If it's empty or not set, use:
如果它为空或未设置，请使用：
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'
BE CAREFUL! If you use GLOBAL, it's not an immediate change, you need to restart your connection to apply the setting.
当心！如果您使用GLOBAL，这不是立即更改，您需要重新启动连接以应用设置。
So, if you're restoring data from one DB to another, for example, and you're not sure if this setting is applied, use SESSIONfor an immediate change (it resets when closing the connection). When done, insert 0 value and it won't change even if the sql_modeis changed.
因此，例如，如果您要将数据从一个数据库恢复到另一个数据库，并且您不确定是否应用了此设置，请使用SESSION立即更改（关闭连接时重置）。完成后，插入 0 值，即使更改也不会更改sql_mode。
To reset this mode (and others) use
要重置此模式（和其他模式），请使用
SET [GLOBAL|SESSION] sql_mode=''
Zero auto-increment values are not recommended because they're not set as default in MySQL databases.
不建议使用零自动增量值，因为它们未在 MySQL 数据库中设置为默认值。
For more info check mysql dev page topicon this
有关更多信息，请查看有关此的mysql dev 页面主题
Update
更新
For MariaDB use the command pointed out in this comment
对于 MariaDB，请使用此评论中指出的命令
SET sql_mode='NO_AUTO_VALUE_ON_ZERO'
回答by e18r
If you do not want to deal with mysql variables, here's a useful hack:
如果您不想处理 mysql 变量，这里有一个有用的技巧：
INSERT INTO `{{TABLE_PREFIX}}users` VALUES (-1, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);
And then
进而
UPDATE `{{TABLE_PREFIX}}users` SET id = 0 where id = -1;
Obviously, this assumes that you're using a signed integer and not using negative values for your table ids.
显然，这假设您使用的是有符号整数，而不是为您的表 ID 使用负值。
回答by Stefan Rogin
Fail safe way:
故障安全方式：
SET @@session.sql_mode =
CASE WHEN @@session.sql_mode NOT LIKE '%NO_AUTO_VALUE_ON_ZERO%'
THEN CASE WHEN LENGTH(@@session.sql_mode)>0
THEN CONCAT_WS(',',@@session.sql_mode,'NO_AUTO_VALUE_ON_ZERO') -- added, wasn't empty
ELSE 'NO_AUTO_VALUE_ON_ZERO' -- replaced, was empty
END
ELSE @@session.sql_mode -- unchanged, already had NO_AUTO_VALUE_ON_ZERO set
END
- Checks if NO_AUTO_VALUE_ON_ZERO already set in sql_mode
- Adds to sql_mode if not empty
- Sets only the session, I recommend using it only on the sessions where you need to insert a 0
- 检查是否已在 sql_mode 中设置 NO_AUTO_VALUE_ON_ZERO
- 如果不为空，则添加到 sql_mode
- 仅设置会话，我建议仅在需要插入 0 的会话中使用它
