MySQL MySQL更新CASE WHEN/THEN/ELSE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12754470/
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 CASE WHEN/THEN/ELSE
提问by nick
I am trying to update a LARGE MyISAM table (25 million records) using a CLI script. The table is not being locked/used by anything else.
我正在尝试使用 CLI 脚本更新大型 MyISAM 表（2500 万条记录）。该表没有被其他任何东西锁定/使用。
I figured instead of doing single UPDATE queries for each record, I might as well utilize the CASE feature.
我想与其对每条记录执行单个 UPDATE 查询，不如利用 CASE 功能。
The idfield is PRIMARY. I suspect the following query should take milliseconds.
该id字段是主要的。我怀疑以下查询应该花费几毫秒。
UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
END
Lo and behold, the query hogs the CPU and doesn't finish in forever.
瞧，查询会占用 CPU，并且不会永远完成。
Then, to my surprise, I found out that the query is updating all the 25 million rows, placing a NULL on rows that I didn't specify.
然后，令我惊讶的是，我发现查询正在更新所有 2500 万行，在我没有指定的行上放置一个 NULL。
What is the purpose of that? Can I just do a MASS update on specific rows without updating 25 million rows every time I execute this query? Or do I have to do individual updates and then commit?
这样做的目的是什么？我可以只对特定行进行 MASS 更新而不在每次执行此查询时更新 2500 万行吗？还是我必须进行单独的更新然后提交？
回答by rs.
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)
回答by godiane
Simpler would be:
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
更简单的是：
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
This will only work if uid is indeed 1, 2, 3.
这仅在 uid 确实是 1、2、3 时才有效。
Source: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt
来源：http: //dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt
回答by Ross Smith II
Simpler would be:
更简单的是：
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
回答by alex
That's because you missed ELSE.
那是因为你错过了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)
“返回为真的第一个条件的结果。如果没有匹配的结果值，则返回 ELSE 之后的结果，如果没有 ELSE 部分，则返回 NULL。” （http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case）
