mysql - sólo - ¿Cómo verificar si dos tablas tienen exactamente los mismos datos?

sql comparar dos tablas y buscar registros no coincidentes

Question

Básicamente tenemos una tabla (tabla original table ) y se realiza una copia de seguridad en otra tabla ( backup table ); por lo tanto, las dos tablas tienen exactamente el mismo esquema.

Al principio, ambas tablas ( original table y backup table ) contienen exactamente el mismo conjunto de datos. Después de algún tiempo, por alguna razón, necesito verificar si el conjunto de datos en la original table ha cambiado o no.

Para hacer esto, tengo que comparar el conjunto de datos en la original table con la backup table .

Digamos que la original table tiene el siguiente esquema:

create table LemmasMapping (
lemma1 int,
lemma2 int,
index ix_lemma1 using btree (lemma1),
index ix_lemma2 using btree (lemma2)
)

¿Cómo podría lograr la comparación de datos?

Actualización : la tabla no tiene una clave primaria. Simplemente almacena las asignaciones entre dos identificadores.

Answer 1

Intente lo siguiente para comparar dos tablas:

SELECT ''different'' FROM DUAL WHERE EXISTS(
SELECT * FROM (
SELECT /*DISTINCT*/ +1 AS chk,a.c1,a.c2,a.c3 FROM a
UNION ALL
SELECT /*DISTINCT*/ +1 AS chk,b.c1,b.c2,b.c3 FROM b
) c
GROUP BY c1,c2,c3
HAVING SUM(chk)<>2
)
UNION SELECT ''equal'' FROM DUAL
LIMIT 1;

Answer 2

Para el desarrollador más perezoso o más averso a SQL que trabaja con MS SQL Server, recomendaría SQL Delta (www.sqldelta.com) para este y cualquier otro trabajo de tipo de base de datos. Tiene una gran GUI, es rápida y precisa y puede diferenciar todos los objetos de la base de datos, generar y ejecutar los scripts de cambio necesarios, sincronizar bases de datos completas. Es lo mejor para un DBA ;-)

Creo que hay una herramienta similar disponible de RedGate llamada SQL Compare. Creo que algunas ediciones de la última versión de Visual Studio (2010) también incluyen una herramienta muy similar.

Answer 3

Pruebe el siguiente método para determinar si dos tablas son exactamente iguales, cuando no hay una clave principal de ningún tipo y no hay filas duplicadas dentro de una tabla, usando la siguiente lógica:

Paso 1 - Prueba de filas duplicadas en TABLEA

Si SELECCIONA DISTINTO * DE TABLEA

tiene el mismo número de filas que

SELECCIONAR * DE TABLEA

luego ve al siguiente paso, de lo contrario no puedes usar este método ...

Paso 2 - Prueba de filas duplicadas en TABLEB

Si SELECCIONA DISTINTO * DE TABLEB

tiene el mismo número de filas que

SELECCIONAR * DE TABLEB

Luego ve al siguiente paso, de lo contrario no puedes usar este método ...

Paso 3: INNER JOIN TABLEA to TABLEB en cada columna

Si el recuento de filas de la consulta a continuación tiene el mismo recuento de filas que los recuentos de filas de los pasos 1 y 2, entonces las tablas son las mismas:

SELECT
*
FROM
TABLEA
INNER JOIN TABLEA ON
TABLEA.column1 = TABLEB.column1
AND TABLEA.column2 = TABLEB.column2
AND TABLEA.column3 = TABLEB.column3
--etc...for every column

Tenga en cuenta que este método no necesariamente prueba diferentes tipos de datos, y probablemente no funcionará en tipos de datos que no se pueden unir (como VARBINARY)

Comentarios bienvenidos!

Answer 4

Solo puedes usar CHECKSUM TABLE y comparar los resultados. Incluso puede alterar la tabla para habilitar las sumas de comprobación en vivo para que estén continuamente disponibles.

CHECKSUM TABLE original_table, backup_table;

No requiere que las tablas tengan una clave primaria.

Answer 5

Yo escribiría tres consultas.

1. Una combinación interna para recoger las filas donde existe la clave principal en ambas tablas, pero hay una diferencia en el valor de una o más de las otras columnas. Esto recogería filas modificadas en el original.

2. Una combinación externa izquierda para recoger las filas que están en las tablas originales, pero no en la tabla de respaldo (es decir, una fila en el original tiene una clave principal que no existe en el respaldo). Esto devolvería filas insertadas en el original.

3. Una unión externa derecha para recoger las filas en la copia de seguridad que ya no existe en el original. Esto devolvería las filas que se han eliminado del original.

Podría unir las tres consultas para devolver un único conjunto de resultados. Si hiciera esto, tendría que agregar una columna para indicar qué tipo de fila es (actualizada, insertada o eliminada).

Con un poco de esfuerzo, es posible que pueda hacer esto en una consulta utilizando una unión externa completa. Tenga cuidado con las combinaciones externas, ya que se comportan de manera diferente en diferentes motores SQL. Los predicados colocados en la cláusula where, en lugar de la cláusula de combinación, a veces pueden convertir su combinación externa en una combinación interna.

Answer 6

SELECT * FROM Table1
UNION
SELECT * FROM Table2

Si obtiene registros mayores que cualquiera de las dos tablas, no tienen los mismos datos.

Answer 7

select count(*)
from lemmas as original_table
full join backup_table using (lemma_id)
where backup_table.lemma_id is null
or original_table.lemma_id is null
or original_table.lemma != backup_table.lemma

La unión completa / comprobación de nulos debe cubrir las adiciones o eliminaciones, así como los cambios.

• backup.id es nulo = adición
• original.id es nulo = borrado
• ni nulo = cambio