使用REPLACE的更新语句中的mysql案例 [英] 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%'，因此update语句将需要进行全表扫描.在这种情况下，将这两个语句组合在一起将提高整体性能.但是，在您的建议中，每一行都会更新，并且大多数行都会更新而不会更改(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%';

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