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 -

• RolandoMySQLDBA: If there is an AUTO_INCREMENT, it might get it in the way.
• akuzminsky: Right, then you cut the autoincrement value
• Zds: There is no guarantee the columns will be in the same order, so spec-wise identical schemas can produce different checksums.
• akuzminsky: Columns are strictly ordered in the dictionary. I've never seen different order in SHOW CREATE TABLE. Can you give examples to prove otherwise?

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;

• Daniele Testa: So you are running dev, testing and production on the same server?

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

Following compares 2 different database for all matching tables (tables exist in both db)

SELECT src,
 column_name,
 ordinal_position,
 data_type,
 column_type
FROM (SELECT column_name,
 ordinal_position,
 data_type,
 column_type,
 COUNT(1) rowcount,
 CONCAT(TABLE_SCHEMA, '->', TABLE_NAME) AS src
 FROM information_schema.columns
 WHERE table_schema IN ('db_new', 'db_old')
# AND table_name IN ('users') # uncomment this to compare selective tables
 GROUP BY column_name,
 ordinal_position,
 data_type,
 column_type
 HAVING COUNT(1) = 1) A;

Credit to @RolandoMySQLDBA, slightly changed from his code

Caveat: this doesn't works for tables missing in one db. Like if tbl1 exist in one db but not in other then this doesn't includes that in the comparison.

To find out which tables are missing in both db then perform following query

SET @db1 = 'db_new',@db2 = 'db_old';
SELECT TABLE_NAME,
 (CASE
 WHEN SUM(TABLE_SCHEMA = @db1) = 0
 THEN concat('Missing in ', @db1)
 ELSE concat('Missing in ', @db2)
 END) AS which
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN (@db1, @db2)
GROUP BY TABLE_NAME
HAVING COUNT(*) = 1
ORDER BY TABLE_NAME;

Answer 9

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

• ypercubeᵀᴹ: What exactly is this? An improvement on Rolando's answer?
• murtaza.webdev: not improved but to view direct changes in all tables between two databases.