Compare two databases

from the Artful Common Queries page


One of EF Codd's rules for relational databases is the no-back-door rule: all info about tables should be accessible only by a query on tables. Since version 5, the MySQL implementation of information_schema (I_S) helps meet Codd's requirement. I_S supplies metadata in tables, so it's the first place to look for how to compare the structures of two databases.

Elsewhere on this page there is a simple query template for comparing data in two structurally similar tables:

SELECT MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
  SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
  FROM a
  UNION ALL
  SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
  FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID; 

To apply this logic to the comparison of two database structures:
  • write temp tables collecting desired I_S metadata on each database
  • map the compare-data query template to those two metadata tables
This logic is easiest to re-use when it is parameterised in a stored procedure, in a system database:

USE sys;
DROP PROCEDURE IF EXISTS CompareDBs;
DELIMITER |
CREATE PROCEDURE CompareDBs( vdb1 VARCHAR(64), vdb2 VARCHAR(64) )
BEGIN

  DROP TEMPORARY TABLE IF EXISTS desc1,desc2;
  CREATE TEMPORARY TABLE desc1
  SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
  FROM information_schema.tables t1
  JOIN information_schema.columns c1 USING (table_schema,table_name)
  WHERE t1.table_schema=vdb1 
  ORDER BY t1.table_name,c1.column_name;

  CREATE TEMPORARY TABLE desc2
  SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
  FROM information_schema.tables t1
  JOIN information_schema.columns c1 USING (table_schema,table_name)
  WHERE t1.table_schema=vdb2 
  ORDER BY t1.table_name,c1.column_name;

  SELECT
    TableName,column_name,MIN(SchemaName),table_type,engine,
    ordinal_position,column_type,column_default,is_nullable,column_key
  FROM (
    SELECT 
      a.table_schema AS SchemaName,a.table_name AS TableName,a.table_type,a.engine,
      a.column_name,a.ordinal_position,a.column_type,a.column_default,a.is_nullable,a.column_key
    FROM desc1 a
    UNION ALL
    SELECT 
      b.table_schema AS SchemaName,b.table_name AS TableName,b.table_type,b.engine,
      b.column_name,b.ordinal_position,b.column_type,b.column_default,b.is_nullable,b.column_key
    FROM desc2 b
  ) AS tmp
  GROUP BY TableName,table_type,engine,column_name,ordinal_position,column_type,column_default,is_nullable,column_key
  HAVING COUNT(*) = 1
  ORDER BY TableName,column_name,SchemaName;   

  DROP TEMPORARY TABLE desc1, desc2;

END |
DELIMITER ;

Call it as follows:

CALL compareDBs('db1','db2');

MEMORY tables would it speed it up, but unfortunately MySQL MEMORY tables do not support BLOB/TEXT columns.

Return to the Artful Common Queries page