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. Since MySQL 8.0 and MariaDB 10.2, Common Table Expressions simplify and optimise this query ... 
drop procedure if exists comparedbs;
create procedure comparedbs( 
  vdb1 varchar(64), vdb2 varchar(64) 
)
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
  ),
  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
  )
SELECT 
  a.table_schema as SchemaA,
  a.table_name as TableA,
  a.table_type as TypeA,
  a.engine as EngineA,
  a.column_name as ColA,
  a.column_type as ColTypeA,
  a.column_default as DefaultA,
  a.is_nullable as NullA,
  a.column_key as KeyA,
  IfNull(b.table_schema,'') as SchemaB,
  IfNull(b.table_name,'') as TableB,
  IfNull(b.table_type,'') as TypeB,
  IfNull(b.engine,'') as EngineB,
  IfNull(b.column_name,'') as ColB,
  If(b.column_type Is Null,'',If(a.column_type=b.column_type,lower(b.column_type),upper(b.column_type))) as ColTypeB,
  If(b.column_default Is Null,'',If(a.column_default=b.column_default,lower(b.column_default),upper(b.column_default))) as DefaultB,
  If(b.is_nullable Is Null,'',If(a.is_nullable=b.is_nullable,lower(b.is_nullable),upper(b.is_nullable))) as NullB,
  If(b.column_key Is Null,'',If(a.column_key=b.column_key,lower(b.column_key),upper(b.column_key))) as KeyB
FROM desc1 a 
LEFT JOIN desc2 b USING(table_schema,table_name,table_type,column_name)
UNION ALL
SELECT 
  b.table_schema as SchemaA,
  b.table_name as TableA,
  b.table_type as TypeA,
  b.engine as EngineA,
  b.column_name as ColA,
  b.column_type as ColTypeA,
  b.column_default as DefaultA,
  b.is_nullable as NullA,
  b.column_key as KeyA,
  IfNull(a.table_schema,'') as SchemaB,
  IfNull(a.table_name,'') as TableB,
  IfNull(a.table_type,'') as TypeB,
  IfNull(a.engine,'') as EngineB,
  IfNull(a.column_name,'') as ColB,
  If(a.column_type Is Null,'',If(a.column_type=b.column_type,lower(a.column_type),upper(a.column_type))) as ColTypeB,
  If(a.column_default Is Null,'',If(a.column_default=b.column_default,lower(a.column_default),upper(a.column_default))) as DefaultB,
  If(a.is_nullable Is Null,'',If(a.is_nullable=b.is_nullable,lower(a.is_nullable),upper(a.is_nullable))) as NullB,
  If(a.column_key Is Null,'',If(a.column_key=b.column_key,lower(a.column_key),upper(a.column_key))) as KeyB
FROM desc1 a 
RIGHT JOIN desc2 b USING(table_schema,table_name,table_type,column_name)
WHERE a.table_name IS NULL 
ORDER BY TableA,ColA,SchemaA;

Before MySQL 8.0 and MariaDB 10.2

With versions earlier than MySQL 8.0 and MariaDB, there's more work. 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');

Last updated 10 Apr 2024