更新语句中的 mysql case 与 REPLACE

发布于 2024-10-03 00:54:24 字数 500 浏览 2 评论 0原文

我目前有这样的事情：

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

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

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;

这是执行此操作的正确方法吗？我对案例/时间不熟悉。谢谢！

原文

I currently have something like this:

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

手心的海 2024-10-10 00:54:24

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

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

这样做：

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%';

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).

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.

Do this:

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%';

回复收藏 0 原文

~没有更多了~