如何强制 MySQL 将0作为一个有效的自动增量值

长话短说，我有一个 SQL 文件，我想导入作为一个 skel样式的文件，所以这将被重复，编程。我可以随心所欲地编辑 SQL 文件，但我不想触及应用程序本身。

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

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

这样做的问题是，uid是一个 auto_increment字段，从技术上讲，0是一个无效的值。或者至少，如果将其设置为0，基本上就是在告诉 MySQL，“请将下一个 id 插入到这个字段中。”

现在，我假设我可以把一个 INSERT然后一个 UPDATE查询到我的 SQL 文件，但是有没有一种方法告诉 MySQL 一般，是的，我实际上想插入 0到这个字段？

73944 次浏览
小开
最佳答案

从 给你给我的答案来看:

你可使用:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

如 给你所述，它将阻止 MySQL 将 INSERT/UPDATE ID 为0解释为下一个序列 ID。这种行为将被限制为 NULL。

不过，我认为这是应用程序中相当糟糕的行为。您必须非常小心地使用它，特别是如果您选择在以后实现复制。

小开

使用以下命令检查 sql DB 模式:

SELECT @@[GLOBAL|SESSION].sql_mode;

如果它是空的或者没有设置，使用:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

小心！如果您使用的是 GLOBAL，这不是一个立即的变化，您需要重新启动您的连接来应用设置。

因此，例如，如果要将数据从一个数据库还原到另一个数据库，并且不确定是否应用了此设置，请使用 SESSION进行即时更改(在关闭连接时重置)。完成后，插入0值，即使 sql_mode改变了，它也不会改变。

若要重置此模式(和其他模式) ，请使用

SET [GLOBAL|SESSION] sql_mode=''

不推荐使用零自动增量值，因为在 MySQL 数据库中没有将它们设置为默认值。

更多信息请查看 Mysql dev 页面主题

更新

对于 MariaDB，使用 此评论中指出的命令

SET sql_mode='NO_AUTO_VALUE_ON_ZERO'
小开

防故障安全方法:

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
  • 检查 NO _ AUTO _ VALUE _ ON _ ZERO 是否已在 sql _ mode 中设置
  • 如果不为空，则添加到 sql _ mode
  • 只设置会话，我建议只在需要插入0的会话上使用它
小开

如果你不想处理 mysql 变量，这里有一个有用的技巧:

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

然后

UPDATE `\{\{TABLE_PREFIX}}users` SET id = 0 where id = -1;

显然，这里假设您使用的是有符号整数，而不是表 id 的负值。


提交答案