By sjdh


2014-09-03 00:42:55 8 Comments

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.

6 comments

@Bibek Lekhak 2017-02-16 03:08:51

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;

@JKar 2016-09-21 21:48:20

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;

@murtaza.webdev 2016-01-18 08:34:24

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ᵀᴹ 2016-01-18 09:02:34

What exactly is this? An improvement on Rolando's answer?

@murtaza.webdev 2016-01-18 09:12:29

not improved but to view direct changes in all tables between two databases.

@akuzminsky 2014-09-03 19:08:39

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 2014-09-03 20:55:41

If there is an AUTO_INCREMENT, it might get it in the way.

@akuzminsky 2014-09-03 22:16:30

Right, then you cut the autoincrement value

@RolandoMySQLDBA 2014-09-04 01:55:07

Now, that's quick and dirty. +1 !!!

@sjdh 2014-09-04 02:12:17

This seems a clever solution if you are working from the shell. Thank you.

@Zds 2015-05-29 14:51:31

There is no guarantee the columns will be in the same order, so spec-wise identical schemas can produce different checksums.

@RolandoMySQLDBA 2014-09-03 20:54:37

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

@Jason 2018-05-24 20:30:09

I had need to do a side by side comparison of two dev databases that have all the same tables in different states, I was able to modify this to meet that goal nicely.

@RolandoMySQLDBA 2018-05-24 21:47:15

@Jason glad I could help !!!

@Nikita Kurtin 2019-05-22 11:09:52

Very helpful, saved me some precious time

@iCoders 2019-08-27 10:10:44

how to show schema name ,table name in select columns

@Vérace 2014-09-03 01:56:54

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

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] MySQL query taking too long

2 Answered Questions

[SOLVED] Compare two MySQL dump files

2 Answered Questions

[SOLVED] Joining mysql tables

  • 2018-09-20 09:51:09
  • Matthew
  • 60 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql php

1 Answered Questions

[SOLVED] MySql Joining two tables

  • 2018-05-24 13:14:50
  • Koushik.S
  • 92 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql

2 Answered Questions

[SOLVED] MYSQL slow down using UNION ALL

  • 2017-07-26 07:16:00
  • Zeno
  • 3142 View
  • 1 Score
  • 2 Answer
  • Tags:   mysql

1 Answered Questions

[SOLVED] Compare the structure of two MySQL Databases

1 Answered Questions

Sponsored Content