0

To automate the backup process of the Users database, I would like to compare the structure of two tables (Users vs ArchivedUsers).

I want to compare the columns between both tables to make sure that the the same columns exist, i.e a column like firebase_id isn't added to the Users table without being added to the ArchivedUsers table as well.

A user on stackexchange, @RolandoMySQLDBA, put together a query that compares two tables in a database.

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('users','archived_users')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

Can you think of a query that can compare the columns in two tables?

spickermann
  • 100,941
  • 9
  • 101
  • 131
VegaStudios
  • 378
  • 1
  • 4
  • 22

1 Answers1

1

There are several way to do this, but for MySQL I suggest this:

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
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

This query is sourced from a blog by Jeff Smith (here)

The advantage of this approach is you get to know which table has the different row(s)

An earlier SO question on this topic is here which shows other methods particularly for DBMS platforms that support EXCEPT or MINUS at: SQL compare data from two tables

Community
  • 1
  • 1
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51