主页 / dba / 问题 / 281421
Accepted
DevelJoe
Asked: 2020-12-13 13:54:58 +0800 CST

使用两个不同的 where 条件进行两个不同的更新

  • 772

尽管这个问题非常相似，但我不认为这也是我案例的最佳方法，尽管它似乎有效。考虑以下示例：

CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

我现在想创建一个更新查询，x为他隐藏用户的所有消息；即show_for_writer在是 的情况下设置等于 0 ，在writer是的情况下x设置show_for_recipient等于 0 。请注意，我希望尽可能高效地完成此操作，因为该表可能包含数百万条记录（每条消息一条），用于无数的作者-收件人组合（复合、非唯一索引）。recipientx

根据链接的帖子，我试图做的是：

UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

然而，我觉得这根本不理想，因为它会遍历表的所有记录，而且这个查询可能不会以常规规模完成，但也不是很少。什么是最好的解决方案？我愿意接受任何解决方案，甚至是结构柱修改提议；我正在使用 MariaDB 10.3。

我的一个想法可能是按如下方式编辑结构：

CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

并执行以下操作：

UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

这里的问题是我查询消息是否在初始化时显示为“x”。因此，设置show为JSON数据类型将无法对该列进行索引，因此无法show有效地在 f() 中进行查询。这就是为什么我认为这个想法可能并不理想，只是为了向您展示我基本上对结构层面的修改持开放态度。

否则我可以简单地触发两个后续UPDATE语句，如下所示：

UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

但我真的更喜欢坚持每个操作使用一个查询。请帮忙！

检查这里的示例，更新查询被优化以避免遍历表的所有记录：

http://www.sqlfiddle.com/#!9/f6830d/1

update mariadb

2 个回答

  1. Best Answer

    做两个UPDATEs。

    并拥有一个PRIMARY KEY

    和两个二级索引：

    INDEX(recipient),
INDEX(writer)

    如果您担心 ACIT 的完整性，请进行交易：

    BEGIN;
UPDATE ... WHERE writer...
UPDATE ... WHERE recipient...
COMMIT;
    • 1

  2. 我不确定我是否理解使用这种语法有什么问题？

    UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

    writer无论您选择哪种解决方案，从查询的角度来看，如果orrecipient是提供的值，则需要检查每条记录。

    对这些字段进行适当的索引是您如何通过索引查找操作确保最有效的更新，该操作仅从 B 树中查找 WHERE 子句的值（因此减少了需要分析多少数据点来查找需要的记录要被更新）。

    • 0

相关问题