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's 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 apply when it's parameterised in a stored procedure, in a system database. Before MySQL version 8, the sproc needs to create named temporary tables:

USE system;
DROP PROCEDURE IF EXISTS CompareDBs;
DELIMITER go
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;
go
DELIMITER ;
CALL compareDBs('db1','db2');

MEMORY tables would it speed it up, but MySQL MEMORY tables do not support BLOB/TEXT columns. Since MySQL version 8 and MariaDB 10.2, Common Table Expressions implement temporary tables as virtual tables in a WITH clause, uncluttering the code:

DROP PROCEDURE IF EXISTS CompareDBs;
DELIMITER go
CREATE PROCEDURE CompareDBs( vdb1 VARCHAR(64), vdb2 VARCHAR(64) )
BEGIN
  WITH 
    desc1 AS (
      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
    ),
    desc2 AS (
      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;   
END;
go
DELIMITER ;
CALL compareDBs('db1','db2');

Return to the Artful Common Queries page