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.

score 55 · Accepted Answer · answered Sep 03 '14 at 20:54

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

akuzminsky · Answer 2 · 2014-09-03T22:15:28.830

4

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  -

edited Sep 03 '14 at 22:15

answered Sep 03 '14 at 19:08

akuzminsky

4,997
15
16

score 1 · Answer 3 · answered Feb 16 '17 at 03:08

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;

score 0 · Answer 4 · answered Sep 21 '16 at 21:48

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;

score 0 · Answer 5 · answered Oct 14 '20 at 11:21

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

score 0 · Answer 6 · answered Nov 15 '23 at 08:05

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');

score 0 · Answer 7 · answered Jul 16 '24 at 10:07

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;

score 0 · Answer 8 · answered Sep 03 '14 at 01:56

0

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

answered Sep 03 '14 at 01:56

Vérace

30,923
9
73
85

murtaza.webdev · Answer 9 · 2016-01-18T09:12:50.463

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

Query to compare the structure of two tables in MySQL

9 Answers9

TWO TABLES IN THE CURRENT DATABASE

TWO TABLES IN A SPECIFIC DATABASE

TWO TABLES IN TWO DIFFERENT DATABASES

GIVE IT A TRY !!!

DB DIFF (compare tables/views)

Linked

Related