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.
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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.