5

I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table?

  • the databases are in different schema.
  • It's only the structure I want to compare, not the data.
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Zhenyu
  • 185
  • 2
  • 6

1 Answers1

2

Using INFORMATION_SCHEMA.COLUMNS, here is my proposed query

SELECT B.* FROM
(
    SELECT DISTINCT table_name FROM
    (
        SELECT table_name,column_name,ordinal_position,data_type,column_type,COUNT(1) match_count
        FROM information_schema.columns WHERE table_schema IN ('db1','db2')
        GROUP BY table_name,column_name,ordinal_position,data_type,column_type
        HAVING COUNT(1) = 1
    ) AA
) A INNER JOIN
(
    SELECT table_schema,table_name,column_name,ordinal_position,data_type,column_type
    FROM information_schema.columns WHERE table_schema IN ('db1','db2')
) B;
USING (table_name)
ORDER BY B.table_name,B,table_schema;

The output will be each the columns differences. You will see differences by data type, column type, and/or column position. You should quickly see if a table only appears in one one database and not another.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Nice. I once detected a difference between two databases with about 500 columns in them where one column was variously defined as Int or Float. This pinpointed an elusive problem that had been bugging the administrators for a long time. – Walter Mitty Jan 24 '15 at 12:38