mysql - mysql中多种情况下的空值更新

Question

在 mysql 中，我有如下所示的表

昨天我学到了一个影响大型生产数据库的教训。

我的第一个要求是将两个用户名 a 和 b 更新为 z 和 y其次是将 id 5 和 6 的两封电子邮件更新到 z.com 和 y.com

# ID, username, mail
1, a, a.com
2, b, b.com
3, c, c.com
4, d, d.com
5, e, e.com
6, f, f.com
7, g, g.com
8, h, h.com
9, i, i.com
10, j, j.com

所以当我决定像这样同时进行两个更新时

UPDATE test
SET username = CASE
   WHEN username = 'a' THEN 'z'
   WHEN username = 'b' THEN 'y'
   END,
mail = CASE
   WHEN id = 5  THEN 'z.com'
   WHEN id = 6  THEN 'y.com'
   END

我的产品数据库受到了这样的影响。很多空值

# ID, username, mail
1, z, 
2, y, 
3, , 
4, , 
5, , z.com
6, , y.com
7, , 
8, , 
9, , 
10, , 

一旦我意识到，我也应该提到 where 计算中的 when 条件列。就像下面的例子，它的工作原理

UPDATE test
SET username = CASE
   WHEN username = 'a' THEN 'z'
   WHEN username = 'b' THEN 'y'
   END,
WHERE username IN ('a','b')

但这只是一次更新。但如果同时进行相同的两个更新

UPDATE test
SET username = CASE
   WHEN username = 'a' THEN 'z'
   WHEN username = 'b' THEN 'y'
   END,
mail = CASE
   WHEN id = 5  THEN 'z.com'
   WHEN id = 6  THEN 'y.com'
   END
WHERE username IN ('a','b') AND id IN(5,6)

没有任何更新。

所以我有一些问题1)为什么有很多 null 出现......我错过了逻辑2)为什么我的新查询在两列中都进行更新..？

提前致谢

Answer 1

您可以在 case when 表达式中添加 else，如下所示:

UPDATE test
SET 
    username = CASE
        WHEN username = 'a' THEN 'z'
        WHEN username = 'b' THEN 'y'
        ELSE username
    END,
    mail = CASE
        WHEN id = 5  THEN 'z.com'
        WHEN id = 6  THEN 'y.com'
        ELSE mail
    END
    -- ,dateCol = CASE
    --     WHEN username IN ('a', 'b') OR id IN (5, 6) THEN NOW() 
    --     ELSE dateCol
    -- END

在最后一个 update 查询中，尝试将 and 更改为 or:

UPDATE test
SET username = CASE
   WHEN username = 'a' THEN 'z'
   WHEN username = 'b' THEN 'y'
   ELSE username
   END,
mail = CASE
   WHEN id = 5  THEN 'z.com'
   WHEN id = 6  THEN 'y.com'
   ELSE mail
   END
-- ,dateCol = now()
WHERE username IN ('a','b') OR id IN (5,6)