更新语句中的mysql案例与REPLACE [英] mysql case in update statement with REPLACE

问题描述

我目前有这样的事情:

UPDATE table1  SET column1 = REPLACE(column1, 'abc', 'abc1') WHERE column1 LIKE '%abc%';
UPDATE table1  SET column1 = REPLACE(column1, 'def', 'def1') WHERE column1 LIKE '%def%';

我正在尝试将这些合并到一个更新语句中并尝试以下操作:

I am trying to consolidate these into a single update statement and am trying the following:

UPDATE table1
SET column1 = 
CASE
WHEN column1 LIKE '%abc%' THEN REPLACE(column1, 'abc', 'abc1')
WHEN column1 LIKE '%def%' THEN REPLACE(column1, 'def', 'def1')
ELSE column1
END;

这是正确的做法吗?我是新来的案例/何时.谢谢！

Is this the correct way of doing this? I am new to case/when. Thanks!

推荐答案

由于您使用的是 LIKE '%abc%'，更新语句将需要全表扫描.在这种情况下，结合这两个语句将提高整体性能.但是，在您的建议中，每一行都会更新，并且大多数都更新而不更改(column1 值替换为 column1 值).

Since you are using LIKE '%abc%', the update statement will require a full table scan. In that case, combining the two statements will improve overall performance. However, in your suggestion, every single row is updated and most of them are updated without being changed (column1 value is replaced with column1 value).

您要确保保留 WHERE 子句，以便只更改真正需要更改的行.这种不必要的磁盘写入比检查行是否符合条件要慢.

You want to make sure that you keep the WHERE clause so that only rows that really need change are changed. This unnecessary write to disk is slower than checking whether the row matches the criteria.

这样做:

UPDATE table1
SET column1 = 
CASE
WHEN column1 LIKE '%abc%' THEN REPLACE(column1, 'abc', 'abc1')
WHEN column1 LIKE '%def%' THEN REPLACE(column1, 'def', 'def1')
END
WHERE column1 LIKE '%abc%' OR column1 LIKE '%def%';

这篇关于更新语句中的mysql案例与REPLACE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！