“插入SELECT`”根据运行查询的客户端来转换值

Question

我正在运行一个非常基本的查询，我希望它能够正确地失败，并且在任何MySQL客户机上运行时，它都会像预期的那样运行。但是，当使用mysqli在PHP脚本中运行时，它会执行奇怪的数据转换。

下一个查询是：

INSERT INTO gk_process_log (process_name, id_instance, date_start) select 'TestLog', NULL, '2020-10-19 14:29:11';");

列id_instance是NOT NULL，所以查询应该失败--完整的表定义在底部。正如我所说，当在任何MySQL客户机上运行时，它都会“正确地”失败。

​

​

但是，当我在PHP脚本中运行该查询时：

mysqli_query($this->getConnection(), "INSERT INTO gk_process_log (process_name, id_instance, date_start) select 'TestLog', NULL, '2020-10-19 14:29:11';");

它确实在NULL列查询执行中插入了一个零，如图所示，不涉及参数绑定。

​

​

请大家解释一下为什么会发生这种事，我该如何解决呢？

表定义：

            CREATE TABLE gk_process_log (
              id_process_log int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
              process_name varchar(50) NOT NULL,
              id_instance int(11) NOT NULL,
              iteration_total INT NULL,
              iteration VARCHAR(255) NULL,
              iteration_step VARCHAR(255) NULL,
              is_finished TINYINT(1) NULL,
              date_start DATETIME NOT NULL,
              date_iteration VARCHAR(255) NULL,
              date_iteration_step VARCHAR(255) NULL,
              date_end DATETIME NULL DEFAULT NULL,
              state_data VARCHAR(255) NULL,
              result_data VARCHAR(255) NULL,
              UNIQUE INDEX instance (process_name, id_instance),
              INDEX (process_name),
              INDEX (id_instance),
              INDEX (is_finished)
            ) ENGINE=InnoDB CHARSET=utf8

Answer 1

“值转换”行为是一个有问题的MySQL特性，由"SQL“配置。来自手册

如果严格模式无效，MySQL将插入无效或缺失值的调整值，并生成警告。

因此，在将NULL转换为零的场景中，没有启用严格模式，因此MySQL将无效值自动转换为字段默认值，以解决NULL约束。

但是，只有在使用NOT NULL语法时，才会使用INSERT INTO SELECT约束来观察此场景。当使用常规的INSERT语法时，“列不能为空”错误出现在它的位置上，而不管采用何种SQL模式。

关于客户机依赖行为，答案是在SQL模式上找到的。唯一可以改变同一查询在不同连接上的工作方式的是，其中一个已经更改了SQL模式，因此根据连接当前的SQL模式设置，服务器的行为会有所不同。它可以通过运行select @@sql_mode;来检查--显示当前连接的当前SQL模式--和select @@GLOBAL.sql_mode; --显示在服务器上设置的全局SQL模式，这将是在更改之前创建的任何新连接的默认模式。

在OP的场景中，他用于手工构建和测试的MySQL客户机是IntelliJ IDEA，内置了一个Java连接器，而且客户端的实现似乎正在改变其连接的MySQL模式。然后，通过PHP运行的查询的行为与通过IntelliJ的mysqli运行查询时所观察到的不同。OP将mysqli行为解释为非标准行为，但它确实是服务器上设置的SQL模式，而IntelliJ IDEA的行为在这里是错误的。这种意外的IntelliJ想法行为已与其开发人员这里共享。

Answer 2

根据MySQL论坛答案

注意，如果连接属性SQL_MODE=STRICT_TRANS_TABLES为“true”(默认情况下为jdbcCompliantTruncation )，Connector/J将设置jdbcCompliantTruncation。这是对SQL_MODE的唯一更改，它发生在引擎盖下面。

在ConnectionImpl.java setupServerForTruncationChecks方法中，也可以在MySQL连接器/J源代码中看到相同的内容