Início / dba / Perguntas / 281421
Accepted
DevelJoe
Asked: 2020-12-13 13:54:58 +0800 CST

Faça duas atualizações diferentes com duas condições de onde diferentes

  • 772

Embora essa pergunta seja muito semelhante, não acho que seja também a melhor abordagem para o meu caso, embora pareça funcionar. Considere o seguinte exemplo:

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

Agora quero criar UMA consulta de atualização que esconda todas as mensagens do usuário xpara ele; ou seja, que define show_for_writerigual a 0 onde o writeré x, E que define show_for_recipientigual a 0 onde o recipienté x. Observe que quero tornar isso o mais eficiente possível, pois esta tabela pode conter milhões de registros (um por mensagem) para inúmeras combinações de escritor-destinatário (índice composto, não exclusivo).

O que tentei fazer, de acordo com o post vinculado, é:

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

No entanto, sinto que isso não é o ideal, porque passa por todos os registros da tabela, e essa consulta pode não ser feita em escala regular, mas também não raramente. Qual a melhor solução para isso? Estou aberto a qualquer solução, até mesmo propostas de modificação de colunas estruturais; Estou usando o MariaDB 10.3.

Uma ideia que tive foi talvez editar a estrutura da seguinte forma:

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

E faça algo como:

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

O problema aqui é que eu consulto as mensagens se elas devem ser mostradas para 'x' na inicialização. Portanto, definir showum JSONtipo de dados não possibilitará indexar essa coluna, portanto, consultar em f( show) de forma eficiente. Por isso achei que essa ideia pode não ser a ideal, mas apenas para mostrar que estou basicamente aberto até mesmo a modificações no nível estrutural.

Caso contrário, eu poderia simplesmente disparar duas UPDATEinstruções subsequentes, assim:

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

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

Mas eu realmente prefiro usar uma consulta por ação. Ajuda por favor!

Confira o exemplo aqui, com a consulta de atualização a ser otimizada para evitar passar por cima de todos os registros da tabela:

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

update mariadb

2 respostas

  1. Best Answer

    Faça dois UPDATEs.

    E ter umPRIMARY KEY

    E dois índices secundários:

    INDEX(recipient),
INDEX(writer)

    Se você estiver preocupado com a integridade do ACIT, faça uma transação:

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

  2. Não tenho certeza se entendi o que há de errado em usar essa sintaxe?

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

    Independentemente da solução escolhida, todos os registros precisarão ser verificados se writerou recipientfor o valor fornecido, do ponto de vista da consulta.

    Ter a indexação adequada nesses campos é como você garante o UPDATE mais eficiente por meio de operações de busca de índice que buscam apenas os valores de sua cláusula WHERE de uma B-Tree (e, portanto, reduz quantos pontos de dados precisam ser analisados ​​para encontrar os registros que precisam ser atualizado).

    • 0

relate perguntas