MySQL 5.7 准备语句更新错误的时间戳列 [英] MySQL 5.7 prepared statements updating the wrong timestamp column

问题描述

与准备好的语句一起使用时，我在更新表时遇到了一些问题.似乎 mysql 正在更新一个错误的列，该列甚至没有在更新命令中指定.

准备:

drop table test1;创建表 test1(id int 不为空，show_from 时间戳不为空，updated_at 时间戳 NULL DEFAULT NULL);插入 test1(id, show_from, updated_at) values(1, '2018-01-11 12:10:11.19808', '2019-04-15 11:50:00.704748');

一次性完成:

UPDATE test1 SET show_from='2018-04-15 11:50:00.704748' WHERE id=1;SELECT * FROM test1;

返回:

1 2018-04-15 13:50:01 2019-04-15 13:50:01

正如预期的那样.

现在批量执行此操作:

PREPARE stmt1 FROM 'UPDATE test1 SET updated_at=?WHERE id=?';SET @s1='2019-02-11 12:12:11.19808';设置@s2=1;使用@s1、@s2 执行 stmt1；解除分配准备 stmt1;SELECT * FROM test1;

返回:

1 2019-04-15 12:54:27 2019-02-11 13:12:11

mysql 为何更新 show_from 列?

解决方案

列被声明为时间戳，not null:

show_from 时间戳不为空

在这种模式下，每次更新行(其任何列)时，MySQL 都会更新该列.来自手册:

如果 explicit_defaults_for_timestamp 系统变量被禁用，第一个 TIMESTAMP 列同时具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP> 如果两者都没有明确指定.

文档提出了解决方法，例如:

<块引用>

• 使用指定常量默认值的 DEFAULT 子句定义列.

• 指定 NULL 属性.这也会导致列允许 NULL 值，这意味着您无法分配当前时间戳通过将列设置为 NULL.

我实际上会推荐这个:

<块引用>

• 启用 explicit_defaults_for_timestamp 系统变量.

I'm having some issues with an update on table, when used with a prepared statement. Seems like mysql is updateing a wrong column which was not even specifed in the update command.

prepare:

drop table test1;
create table test1(
  id int not null,
  show_from timestamp not null,
  updated_at timestamp NULL DEFAULT NULL
);
insert into test1(id, show_from, updated_at) values(1, '2018-01-11 12:10:11.19808', '2019-04-15 11:50:00.704748');

do this in one batch:

UPDATE test1 SET show_from='2018-04-15 11:50:00.704748' WHERE id=1;
SELECT * FROM test1;

returns:

1    2018-04-15 13:50:01    2019-04-15 13:50:01

as expected.

Now do this in one batch:

PREPARE stmt1 FROM 'UPDATE test1 SET updated_at=? WHERE id=?';
SET @s1='2019-02-11 12:12:11.19808';
SET @s2=1;
EXECUTE stmt1 USING @s1, @s2;
DEALLOCATE PREPARE stmt1;
SELECT * FROM test1;

returns:

1    2019-04-15 12:54:27    2019-02-11 13:12:11

Why did mysql update the show_from column?

解决方案

The column is declared timestamp, not null:

show_from timestamp not null

In this mode, MySQL will update the column every time the row (any of its columns) is updated. From the manual:

If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

The documentation suggests workarounds such as:

• Define the column with a DEFAULT clause that specifies a constant default value.

• Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL.

I would actually recommend this:

• Enable the explicit_defaults_for_timestamp system variable.

这篇关于MySQL 5.7 准备语句更新错误的时间戳列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！