MySQL 更新 CASE WHEN/THEN/ELSE

我正在尝试使用 CLI 脚本更新一个大型 MyISAM 表(2500万条记录)。该表没有被其他任何东西锁定/使用。

我认为与其对每个记录执行单个 UPDATE 查询，还不如利用 CASE 特性。

id字段是 PRIMARY。

UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
END

你瞧，查询占用了 CPU，而且不会永远完成。

然后，令我惊讶的是，我发现查询正在更新所有2500万行，在没有指定的行上放置 NULL。

这样做的目的是什么？我是否可以只对特定行进行 MASS 更新，而不必每次执行此查询时更新2500万行？或者我必须单独更新然后提交？

194835 次浏览
小开
最佳答案

Try this

UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
ELSE `uid`
END
WHERE id  in (1,2,3)
小开

If id is sequential starting at 1, the simplest (and quickest) would be:

UPDATE `table`
SET uid = ELT(id, 2952, 4925, 1592)
WHERE id IN (1,2,3)

As ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.

Clearly, the above code only works if id is 1, 2, or 3. If id was 10, 20, or 30, either of the following would work:

UPDATE `table`
SET uid = CASE id
WHEN 10 THEN 2952
WHEN 20 THEN 4925
WHEN 30 THEN 1592 END CASE
WHERE id IN (10, 20, 30)

or the simpler:

UPDATE `table`
SET uid = ELT(FIELD(id, 10, 20, 30), 2952, 4925, 1592)
WHERE id IN (10, 20, 30)

As FIELD() returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

小开

That's because you missed ELSE.

"Returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part." (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case)


提交答案