Query to compare the structure of two tables in MySQL

Question

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).

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

Here are some example tables that you can compare.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.

Answer 1

TWO TABLES IN THE CURRENT DATABASE

If you want to know if two tables are different, run this

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 ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type 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 ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN A SPECIFIC DATABASE

If you want to know if two tables are different in database mydb, run this

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='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN TWO DIFFERENT DATABASES

If you want to know if db1.tb1 and db2.tb2 are different, run this

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='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

GIVE IT A TRY !!!

Answer 2

You can compare checksum of output of SHOW CREATE TABLE product_today

# mysql -NBe "SHOW CREATE TABLE sakila.actor"| sed -r 's/AUTO_INCREMENT=[0-9]+/AUTO_INCREMENT=XXX/g' | md5sum
# 1bc0d72b294d1a93ce01b9a2331111cc  -

Answer 3

Expanding on RolandoMySQLDBA's answer:

To see the table name as well, query this:

SELECT table_name, column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        table_name, column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('table_1','table_2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

Answer 4

Take a look at the columns table in the information_schema - the column_type field. That will allow you to compare table structures.

Answer 5

My ultimate way of comparing 2 databases (DB1, DB2) - tables/views only, constrains and foreign key are not included. In my case I always use following SQL to compare PRODUCTION with UAT or UAT with DEV.

DB DIFF (compare tables/views)

select x.* from (
SELECT a.table_name, a.column_name,
    max(IF(b.TS='S1',b.ordinal_position,null)) as S1_ordinal_position,
    max(IF(b.TS='S2',b.ordinal_position,null)) as S2_ordinal_position,
    max(IF(b.TS='S1',b.data_type       ,null)) as S1_data_type,
    max(IF(b.TS='S2',b.data_type       ,null)) as S2_data_type,
    max(IF(b.TS='S1',b.column_type     ,null)) as S1_column_type,
    max(IF(b.TS='S2',b.column_type     ,null)) as S2_column_type
FROM
(SELECT DISTINCT table_name, column_name
 FROM information_schema.columns
 WHERE table_schema IN ('DB1','DB2')
) a
INNER JOIN
(SELECT IF(table_schema='DB1','S1','S2') as TS,
    table_schema,table_name,column_name,ordinal_position,data_type,column_type
 FROM information_schema.columns
 WHERE table_schema IN ('DB1','DB2')
) b
on (a.table_name = b.table_name and a.column_name = b.column_name)
group by a.table_name, a.column_name
) x
where x.S1_ordinal_position != x.S2_ordinal_position or x.S1_ordinal_position is null or x.S2_ordinal_position is null
or    x.S1_data_type        != x.S2_data_type
or    x.S1_column_type      != x.S2_column_type
ORDER BY x.table_name;

Answer 6

I am be too late but I am posting my response so that someone out there can have more options to choose form

I was given a challenge to migrate data from and old db to a new one. One thing was the tables kept their name but the structure has been changed. The default values were given to me on each table and where it was not provided it was someone else job to do it. The RDBMS was MariaDb 10.1 and I used the following script to get the difference in columns. with That you can add additional information regarding fields structure. Hope I didn't miss the question's target.

set @tem = cast('tabl1' as char(90));
set @db_new = cast('db_new' as char(90));
set @db_old = cast('db_old' as char(90));
select n.column_name newCol, (case when o.column_name is null then '''''' else
o.column_name end) oldCol from information_schema.columns as n 
left join information_schema.columns as o on (n.table_name = o.table_name and
n.column_name = o.column_name and o.table_schema = @db_old)
where 
n.table_name = @tem
and
n.table_schema = @db_new

Answer 7

to only get the column names that exists in the first table and not the second:

select column_name from information_schema.columns where TABLE_SCHEMA='your_database_name' and TABLE_NAME='first_table' and column_name
not in (select column_name from information_schema.columns where TABLE_SCHEMA='your_database_name' and TABLE_NAME='second_table');

Answer 8

for all changes in table structure of two databases :

SELECT table_schema, table_name, column_name,ordinal_position,data_type,column_type FROM (
    SELECT
        table_schema, table_name, column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema IN ('database1', 'database2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1 ) A;

Ref.: from RolandoMySQLDBA ans