mysql - mostrar - sql eliminar registros repetidos dejando solo 1

Cómo encontrar duplicados en 2 columnas, no 1

Question

Tengo una tabla de base de datos MySQL con dos columnas que me interesan. Individualmente pueden tener duplicados, pero nunca deben tener un duplicado de AMBOS con el mismo valor.

stone_id puede tener duplicados siempre que para cada título de upsharge sea ​​diferente, y en reversa. Pero diga por ejemplo stone_id = 412 y upcharge_title = "zafiro" que esa combinación solo debería ocurrir una vez.

Esto esta bien:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"

Esto NO está bien:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "sapphire"

¿Hay alguna consulta que encuentre duplicados en ambos campos? Y si es posible, ¿hay alguna manera de configurar mi base de datos para que no lo permita?

Estoy usando la versión 4.1.22 de MySQL

Answer 1

Debe configurar una clave compuesta entre los dos campos. Esto requerirá un stone_id y un upcharge_title únicos para cada fila.

En cuanto a encontrar los duplicados existentes intente esto:

select   stone_id,
         upcharge_title,
         count(*)
from     your_table
group by stone_id,
         upcharge_title
having   count(*) > 1

Answer 2

Incidentalmente, una restricción compuesta única en la tabla evitaría que esto ocurriera en primer lugar.

ALTER TABLE table
    ADD UNIQUE(stone_id, charge_title)

(Esto es válido para T-SQL. No estoy seguro acerca de MySQL).

Answer 3

Me pareció útil agregar un índice único usando un "ALTER IGNORE" que elimina los duplicados y hace cumplir los registros únicos que suenan como lo que le gustaría hacer. Entonces la sintaxis sería:

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX(`id`, `another_id`, `one_more_id`);

Esto agrega efectivamente la restricción única, lo que significa que nunca tendrá registros duplicados y el IGNORE elimina los duplicados existentes.

Puede leer más sobre eh ALTER IGNORE aquí: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

Actualización: @Inquisitive me informó que esto puede fallar en las versiones de MySql> 5.5:

Falla en MySQL> 5.5 y en la tabla InnoDB, y en Percona debido a su función de creación rápida de índices InnoDB [ http://bugs.mysql.com/bug.php?id=40344] . En este caso, primero ejecuta set session old_alter_table=1 y luego el comando anterior funcionará bien

Answer 4

Para encontrar los duplicados:

select stone_id, upcharge_title from tablename group by stone_id, upcharge_title having count(*)>1

Para evitar esto en el futuro, cree una clave única compuesta en estos dos campos.

Answer 5

Puedes encontrar duplicados como este ..

Select
    stone_id, upcharge_title, count(*)
from 
    particulartable
group by 
    stone_id, upcharge_title
having 
    count(*) > 1

Answer 6

esta publicación SO me ayudó, pero también quería saber cómo eliminar y mantener una de las filas ... aquí hay una solución PHP para eliminar las filas duplicadas y mantener una (en mi caso solo había 2 columnas y está en una función para borrar asociaciones de categorías duplicadas)

$dupes = $db->query(''select *, count(*) as NUM_DUPES from PRODUCT_CATEGORY_PRODUCT group by fkPRODUCT_CATEGORY_ID, fkPRODUCT_ID having count(*) > 1'');
if (!is_array($dupes))
    return true;
foreach ($dupes as $dupe) {
    $db->query(''delete from PRODUCT_CATEGORY_PRODUCT where fkPRODUCT_ID = '' . $dupe[''fkPRODUCT_ID''] . '' and fkPRODUCT_CATEGORY_ID = '' . $dupe[''fkPRODUCT_CATEGORY_ID''] . '' limit '' . ($dupe[''NUM_DUPES''] - 1);
}

el (límite NUM_DUPES - 1) es lo que preserva la única fila ...

gracias a todos