Início / dba / Perguntas / 53209
Accepted
Mark D
Asked: 2013-11-13 13:37:04 +0800 CST

Encontrando duplicatas

  • 772

O cenário é o seguinte, há algum tempo um colega executou acidentalmente uma consulta semelhante a

 DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = '';

Esta é a estrutura da tabela de app_i18n

CREATE TABLE app_i18n (
     `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
     `category` INTEGER(11) unsigned NOT NULL,
     `keyword`  varchar(50) DEFAULT NULL,
     `language` varchar(2) NOT NULL DEFAULT 'en',
     `translation` text NOT NULL,
     `disabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`category`, `keyword`, `language`, `disabled`)
) ENGINE=InnoDB;

O problema resultante é que algumas dessas "traduções", embora vazias, estavam lá por um motivo. Portanto, uma restauração estava em ordem. De uma cópia antiga do banco de dados, ele restaurou todas as linhas que estavam vazias ou nulas e não foram desativadas.

O problema foi agravado por um número desconhecido de outros desenvolvedores lançando rapidamente "traduções" para corrigir os problemas que estavam enfrentando. Então pensei que a solução seria

SELECT id 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

Essa consulta produziria as respostas que eu quero? ou seja, todos os registros que possuem categoria duplicada, palavras-chave no mesmo idioma que possuem duplicatas?

mysql

2 respostas

  1. Best Answer

    Na maioria dos RDBMSs, sua consulta causa um erro. Acredito que o MySQL retorne um resultado, mas não necessariamente o que você está procurando.

    Quando você agrupa por um conjunto de colunas, apenas as colunas mencionadas na cláusula group by também podem ser mencionadas diretamente na cláusula select. Todas as outras colunas devem ser agrupadas em algum tipo de agregado como MAX() ou AVG().

    Você está procurando todas as combinações de categoria, palavra-chave e idioma que tenham entradas duplicadas. Então você quer que sua consulta retorne essas colunas:

    SELECT category, keyword, language 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

    Se você deseja que todas as colunas das linhas tenham uma categoria não exclusiva, palavra-chave e combinação de idioma, é necessário adicionar uma junção:

    SELECT A.*
  FROM app_i18n A
  JOIN (
       SELECT category, keyword, language 
         FROM app_i18n A2
        WHERE disabled = 0 
          AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1)C
    ON A.category = C.category
   AND A.keyword = C.keyword
   AND A.language = C.language;
    • 1

  2. O MySQL tem uma função GROUP_CONCAT para que você possa obter uma lista separada por vírgulas de IDs que correspondam aos seus registros duplicados.

    SELECT
    GROUP_CONCAT(id ORDER BY id ASC) as "duplicated_row_ids"
  , category
  , keyword
  , language 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

    ou

    SELECT
    CONCAT('{"id":'
      , GROUP_CONCAT(CONCAT('"', id, '"') ORDER BY id ASC SEPARATOR ',"id":')
      , '}' 
    ) as "duplicated_row_ids" 
  , category
  , keyword
  , language 
  FROM app_i18n 
 WHERE disabled = 0 
   AND (translation is null or translation = '') 
 GROUP BY category, keyword, language 
HAVING COUNT(id) > 1;

    para bons IDs JSON

    consulte http://sqlfiddle.com/#!2/3b51a/6 para demonstração

    • 0

relate perguntas