Comparar los datos de dos tablas en MySql

Podríamos crear un procedimiento almacenado que leyera de la base de datos de sistemas information_schema los campos de cada tabla y comparara.

Pero no todo es tan sencillo y tiene un precio, digamos dos requisitos

• Las tablas que se comparan tienen que tener un campo incremental autonumérico que identificara la fila como única.
• Los nombre de los campos tiene que ser iguales

No es mucho a modificar y ejecutando este procedimiento almacena puede repasar un tabla de 10 millones de registros en 20 minutos ... ¿Cuanto tardarías tu a mano :-) ?

Pero antes de nada comprobemos que hay diferencias entre las dos tablas con CHECKSUM TABLE

CHECKSUM TABLE table1, table2;

Si el checksum es igual no hay nada que comparar, es que son iguales



En la imagen se ve que la primera comparación el checksum sale el mismo por lo que las tablas son iguales y en la segunda son diferentes por lo que hay modificaciones.

Entonces podemos utilizar el siguiente procedimiento almacenado, que aquí os pego, para comparar las dos tablas que el checksum ha dado diferente

DROP PROCEDURE IF EXISTS CompareTables2;
-- With database modifications
DELIMITER $$
CREATE PROCEDURE `CompareTables2`(
    IN table1 VARCHAR(64), 
    IN table2 VARCHAR(64) 
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE columnName VARCHAR(64);
    DECLARE createTableQuery TEXT;
    DECLARE insertDifferencesQuery TEXT;

    -- Cursor to loop through the columns of the first table
    DECLARE columnsCursor CURSOR FOR 
    SELECT COLUMN_NAME 
    FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = table1 
    AND TABLE_SCHEMA = database();

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Create the temporary table with dynamic column names based on the table names
    SET @createTableQuery = CONCAT(
        'CREATE TEMPORARY TABLE IF NOT EXISTS Differences (',
        '`', table1, '` INT, ',      -- Column for table1 ID
        '`', table2, '` INT, ',      -- Column for table2 ID
        'Field VARCHAR(64), ',       -- Field name being compared
        'Value_', table1, ' TEXT, ',  -- Value from table1
        'Value_', table2, ' TEXT)'    -- Value from table2
    );

    -- Execute the query to create the temporary table
    PREPARE stmt FROM @createTableQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    OPEN columnsCursor;

    readColumns: LOOP
        FETCH columnsCursor INTO columnName;
        IF done THEN
            LEAVE readColumns;
        END IF;

        -- Dynamically construct the query to compare columns and handle NULL values properly
        SET @insertDifferencesQuery = CONCAT(
            'INSERT INTO Differences (`', table1, '`, `', table2, '`, Field, Value_', table1, ', Value_', table2, ') ',
            'SELECT t1.id, t2.id, "', columnName, '", t1.', columnName, ', t2.', columnName, ' ',
            'FROM ', database(), '.', table1, ' t1 ',
            'JOIN ', database(), '.', table2, ' t2 ',
            'ON t1.id = t2.id ',  -- Adjust the join condition based on your key
            'WHERE (t1.', columnName, ' <> t2.', columnName, ' OR ',
            't1.', columnName, ' IS NULL AND t2.', columnName, ' IS NOT NULL OR ',
            't1.', columnName, ' IS NOT NULL AND t2.', columnName, ' IS NULL)'
        );

        -- Execute the query to insert the differences
        PREPARE stmt FROM @insertDifferencesQuery;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE columnsCursor;
    
        -- Detect rows that are in table1 but not in table2
    SET @insertDifferencesQuery = CONCAT(
        'INSERT INTO Differences (`', table1, '`, `', table2, '`, Field, Value_', table1, ', Value_', table2, ') ',
        'SELECT t1.id, NULL, "MISSING_IN_', table2, '", t1.id, NULL ',
        'FROM ', database(), '.', table1, ' t1 ',
        'LEFT JOIN ', database(), '.', table2, ' t2 ON t1.id = t2.id ',
        'WHERE t2.id IS NULL'
    );

    PREPARE stmt FROM @insertDifferencesQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Detect rows that are in table2 but not in table1
    SET @insertDifferencesQuery = CONCAT(
        'INSERT INTO Differences (`', table1, '`, `', table2, '`, Field, Value_', table1, ', Value_', table2, ') ',
        'SELECT NULL, t2.id, "MISSING_IN_', table1, '", NULL, t2.id ',
        'FROM ', database(), '.', table2, ' t2 ',
        'LEFT JOIN ', database(), '.', table1, ' t1 ON t2.id = t1.id ',
        'WHERE t1.id IS NULL'
    );

    PREPARE stmt FROM @insertDifferencesQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Display the differences
    SELECT * FROM Differences;

    -- Drop the temporary table after execution
    DROP TEMPORARY TABLE Differences;
END$$
DELIMITER ;

Y para ejecutarlo lo hacemos así:

Call CompareTables2('authors', 'authors_01');

El resultado tiene que ser algo como lo que muestra la imágen



En la tabla se muestran los resultados diferentes entre las dos tablas y al final de todo se muestran las filas que no están en una y otra tabla.

Descripción del procedimiento:

• Parámetros de Entrada:
  • tabla1: Nombre de la primera tabla.
  • tabla2: Nombre de la segunda tabla.
  • Opcionalmente se podría pasar un tercer parámetro (que en este ejemplo no se pasa) que podria ser el esquema: El nombre del esquema (base de datos) donde se encuentran ambas tablas y sustituir la función database() por este parámetro
    
    
• Funcionalidad:
  • Se utiliza el esquema information_schema para obtener los nombres de las columnas de tabla1.
  • Por cada columna en la tabla, se construye dinámicamente una consulta que compara los valores entre las dos tablas para dicha columna.
  • Las comparaciones se hacen campo por campo, tomando en cuenta si alguno de los campos es NULL.
  • Los resultados de las comparaciones que difieren se insertan en una tabla temporal Differences, donde se almacenan los campos que no coinciden junto con los valores de cada tabla.
  • Al final, se muestra el contenido de la tabla temporal Diferencias, que contiene las diferencias encontradas.
  • La tabla temporal se elimina automáticamente al final del procedimiento
• rqwr

Si tenéis muchos registros este procedimiento puede tardar un "ratito", el ejemplo lo he probado en una maquina de 32GB con 10 Millones de registros en un entorno dockerizado y ha tardado 12 minutos, el ejemplo de la imagen es con un conjunto de registros menor.

Y esto es todo, feliz programming!!!
Saludos
Alex
:-)
/

Si te ha gustado el artículo compartelo en:      

---

---

---

---

---