MySQL更新CASE WHEN/THEN/ELSE

2021-11-20 00:00:00 mysql

我正在尝试使用 CLI 脚本更新大型 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)

相关文章