设置 IF NULL 或其他值的 MySQL 更新字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9691138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Update field of set IF NULL or other value
提问by wachpwnski
I am currently using two update queries and am wondering if there is a way to cut it down to one.
我目前正在使用两个更新查询，想知道是否有办法将其缩减为一个。
field1 OFTYPE INT
field2 OFTYPE DATETIME
field3 OFTYPE DATETIME
UPDATE `Table1` SET `field1` = `field1` + 1, `field2` = NOW() WHERE `id` = $id;
UPDATE `Table1` SET `field3` = NOW() WHERE `id` = $id AND (`field3` < '2011-00-00 00:00:00' OR `field3` IS NULL);
I'm trying to get a query that would do the UPDATE more like so:
我正在尝试获取一个查询，该查询会更像这样执行 UPDATE：
UPDATE `Table1`
SET `field1` = `field1` + 1,
`field2` = NOW(),
`field3` = ISNULL(NOW(), `first_seen`);
回答by MysticXG
I think that's it's possible for you to do this using an IF statement. The IF statement takes 3 parameters when you're using it: the expression, value if true, value if false
我认为您可以使用 IF 语句执行此操作。当您使用 IF 语句时，它需要 3 个参数：表达式，如果为真则为值，如果为假则为值
So in your case, you could probably write your queries in one go like the following:
因此，在您的情况下，您可能可以一次性编写查询，如下所示：
UPDATE Table1
SET
field1 = field1 + 1,
field2 = NOW(),
field3 = IF(field3 < '2011-00-00 00:00:00' OR field3 IS NULL, NOW(), field3)
WHERE id = $id;
This way, if expression is true, then field3will be NOW()and otherwise, it'll remain as it was.
这样，如果表达式为真，field3则将是NOW()，否则，它将保持原样。
回答by Ami
In your case you could use CASE*:
在您的情况下，您可以使用CASE*：
UPDATE Table1
SET field1 = field1 + 1,
field2 = NOW(),
field3 =
CASE
WHEN field3 < '2011-00-00 00:00:00' THEN /* Evaluates to false if NULL */
NOW()
WHEN field3 IS NULL THEN
NOW()
ELSE /* Don't change */
field3
END
WHERE id = 1
*Pun optional
*双关可选
回答by Kaloian Kunov
You can do it like that:
你可以这样做：
UPDATE `Table1`
SET `field1` = `field1` + 1,
`field2` = NOW(),
`field3` = COALESCE(field3, NOW())
WHERE id = 1;
This will update all field1 and field2 and if field3 is null will update it too, if its not null it will remain what it was. But this works only for NULL!!!
这将更新所有 field1 和 field2，如果 field3 为 null 也会更新它，如果它不是 null 它将保持原样。但这仅适用于 NULL ！！！
回答by DonCallisto
If field3have to be updated with a different condition from field1and field2, I suppose that you can't do all in one query.
What you can do is a TRANSACTIONif the queries have to be executed together or nothing.
如果field3必须与一个不同的状态更新field1和field2，我想，你不能做一个查询。如果查询必须一起执行或什么都不执行，您可以做的是TRANSACTION。
