MySQL SELECT查询工作，但具有相同WHERE子句的UPDATE查询不工作

Question

当行在UPDATE查询中被排除时，为什么数据库引擎首先尝试设置错误的日期时间并停止执行?

CREATE TABLE `test`(
`name` VARCHAR(255) NOT NULL,
`order_date` DATETIME NULL
)
INSERT INTO `test` (`name`, `order_date`) VALUES
("test nodate", NULL),
("test 2021-10-27", NULL);
-- Both rows are selected with a formatted date taken from the `name`
SELECT *, DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") AS `date part from name` FROM `test`;
-- The first row with an invalid date in the name is correctly excluded
SELECT *, DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") AS `date part from name` FROM `test`
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;
-- Error: invalid datetime "est nodate", but that row should be excluded
UPDATE `test` SET
`order_date` = DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d")
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

DB小提琴:https://www.db-fiddle.com/f/iUmjxZz3u5bpZesfiD4pzY/1

当尝试例如这个更新查询设置无效的日期时间时，引擎不会抛出相同的错误，而我希望它与上面的错误更新语句相同:

UPDATE `test`
SET `order_date` = "invalid"
WHERE `name` <> `name`;

此更新是成功的，没有更新任何行，但我试图强制无效的日期时间错误。

像DATE_FORMAT()这样的函数在执行顺序或其他方面是特殊情况吗?是否有解决方法可以使UPDATE查询成功执行?

Answer 1

在WHERE中检测到错误，而不是在SET中。

UPDATE `test`
SET `order_date` = NOW()
WHERE DATE_FORMAT(RIGHT(`name`, 10), "%Y-%m-%d") IS NOT NULL;

产生相同的错误。

错误发生由SQL Server模式定义。

参见Strict SQL Mode

严格模式控制MySQL如何处理INSERT或UPDATE等数据更改语句中无效或缺失的值。

https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= e0bcb8862b10fb8af16b8e2ffb1ae46c

Answer 2

操作:

DATE_FORMAT('foo bar baz', '%Y-%m-%d')

导致以下警告:

1292-Incorrect datetime value: '...'

现在，根据SQL模式的不同，警告将在数据更改语句中被视为错误。． 这解释了为什么您的SELECT语句工作(有警告)，但INSERT不能(警告被视为错误)。

db<>fiddle的完整演示