MySQL更新字段设置IF NULL或其他值

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 `Table1` 
SET `field1` = `field1` + 1,
    `field2` = NOW(),
    `field3` = ISNULL(NOW(), `first_seen`);

3 个解决方案

#1

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个参数：表达式，值为true，值为false

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 field3 will be NOW() and otherwise, it'll remain as it was.

这样，如果表达式为true，则field3将为NOW（），否则，它将保持原样。

#2

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

*双关语可选

#3

If field3 have to be updated with a different condition from field1 and field2, I suppose that you can't do all in one query. What you can do is a TRANSACTION if the queries have to be executed together or nothing.

如果field3必须使用与field1和field2不同的条件进行更新，我想您不能在一个查询中执行所有操作。如果查询必须一起执行或什么都不执行，则可以执行TRANSACTION。

#1