MySQL更新CASE WHEN/THEN/ELSE [英] MySQL update CASE WHEN/THEN/ELSE
问题描述
我正在尝试使用CLI脚本更新LARGE MyISAM表(2500万条记录).该表没有被其他任何东西锁定/使用.
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.
我想而不是对每个记录执行单个UPDATE查询,我还可以利用CASE功能.
I figured instead of doing single UPDATE queries for each record, I might as well utilize the CASE feature.
id
字段为PRIMARY.我怀疑以下查询应花费毫秒.
The id
field is PRIMARY. I suspect the following query should take milliseconds.
UPDATE `table` SET `uid` = CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
END
瞧瞧,查询占用了CPU,并且不会永远结束.
Lo and behold, the query hogs the CPU and doesn't finish in forever.
然后,令我惊讶的是,我发现查询正在更新所有2500万行,在我未指定的行上放置NULL.
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.
这是什么目的?我可以只对特定的行进行MASS更新,而不必每次执行此查询时都更新2500万行吗?还是我必须先进行个别更新然后提交?
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?
推荐答案
尝试一下
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)
这篇关于MySQL更新CASE WHEN/THEN/ELSE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!