如何强制MySQL将0作为有效的自增值

Question

长话短说，我有一个 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 进入此字段？

Answer 1

从我得到的答案此处：

您可以使用：

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

如此处，将阻止 MySQL 将 INSERT/UPDATE ID 0 解释为下一个序列 ID。 此类行为将被限制为 NULL。

不过，我认为应用程序的行为非常糟糕。 您必须非常小心，确保它的使用一致，特别是如果您选择稍后实施复制。

Answer 2

检查您的 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 开发页面主题此

更新

对于 MariaDB，请使用 此评论

SET sql_mode='NO_AUTO_VALUE_ON_ZERO'

Answer 3

如果您不想处理 mysql 变量，这里有一个有用的 hack：

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 不使用负值。

Answer 4

故障安全方法：

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

• 检查 sql_mode 中是否已设置 NO_AUTO_VALUE_ON_ZERO
• 如果不为空，则添加到 sql_mode
• 仅设置会话，我建议仅在需要插入 0 的会话上使用它