Início / dba / Perguntas / 331837
Accepted
Rohit Ramani
Asked: 2023-10-04 19:39:29 +0800 CST

Resultados duplicados na consulta mysql ao tentar classificar no campo nulo com limite e deslocamento

  • 772

Estou tentando classificar dados com campo anulável e também tentando obter paginação usando limite e deslocamento

Aqui está a consulta para a primeira página

SELECT table_name.* FROM table_name ORDER BY table_name.date_field DESC LIMIT 2;

e recebo o seguinte resultado.

eu ia campo1 campo2 campo_data
1 nome nome nulo
2 nome1 nome1 nulo

enquanto a consulta seguinte está sendo usada para a segunda página

SELECT table_name.* FROM table_name ORDER BY table_name.date_field DESC LIMIT 2 OFFSET 2;

e recebo o seguinte resultado.

eu ia campo1 campo2 campo_data
2 nome1 nome1 nulo
3 nome2 nome2 nulo

se você comparar as duas tabelas, notará aquele registro com id=2 fornecido duas vezes (em uma primeira consulta e em uma segunda consulta), o que está errado.

Esta é uma consulta simples, sem junção e sem condição de onde.

Alguém tem solução? desde já, obrigado.

Estou usando a versão mysql: 8.0.34 - MySQL Community Server - GPL

mysql-8.0

1 respostas

  1. Best Answer

    Para expandir o comentário de mustaccio, o banco de dados não está fazendo nada de errado, mas o problema que você está enfrentando é chamado de classificação não determinística . É uma falha na sua lógica.

    Simplificando, a classificação não determinística ocorre quando uma ORDER BYcláusula é usada com valores não exclusivos (no seu caso, um monte de NULLs). Quando solicitado a classificar o mesmo valor entre várias linhas, não há maneira certa ou errada de classificá-las, e execuções repetidas da mesma consulta podem retornar ordens diferentes dessas linhas, tornando-a não determinística (incapaz de ser determinada repetidamente de forma concreta).

    Imagine este exemplo por um momento: Existem 10 pares de meias, todas de cor vermelha, e cada par tem um número único. Alguém lhe diz para separá-las por cor e depois entregar-lhes os primeiros 2 pares de meias. Em que ordem você coloca as meias? Além disso, essa pessoa coloca esses 2 pares de meias de volta na pilha e depois pede a outra pessoa para classificá-los por cor novamente e entregar-lhes os últimos 2 pares de meias. Como são todas da mesma cor, não há uma maneira confiável e repetível de classificar as meias por cor , repetidas vezes, e é perfeitamente possível que a pessoa receba exatamente os mesmos pares de meias nas duas vezes (apesar de pedir o primeiro e o último pares).

    esse problema acontece apenas no servidor mas no mysql local está funcionando bem, como é possível ...

    É uma coincidência que ele esteja funcionando na sua instância local do MySQL, mas sempre que você executar a consulta, ela poderá se transformar em um problema. A classificação em valores não exclusivos significa que não há garantia, independentemente dos resultados anteriores que você viu.

    então estou procurando uma configuração para o mysql, se houver alguma relacionada a esse problema?

    Não existe tal configuração para isso. Esta é uma falha lógica em seu design e existe em todos os sistemas de banco de dados e na vida em geral, conforme demonstra meu exemplo sobre meias.

    Para corrigir seu problema, você precisa adicionar um campo à ORDER BYcláusula que torne toda a expressão única. Um exemplo poderia ser se você adicionasse o idcampo assim:

    SELECT table_name.* 
FROM table_name 
ORDER BY 
    table_name.date_field DESC,
    table_name.id DESC
LIMIT 2;

    Isso ainda seguiria sua lógica de classificação por date_field, mas quando há um empate entre datas não exclusivas (por exemplo, NULLs), ele volta a idser o desempate consistente.

    • 2

relate perguntas