18

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.

  • @yagmoth555 assuming my question is on-topic enough for SF, if you feel like typing up a similar answer here i'll accept it. otherwise, i'll answer my own question later today. – Woodrow Barlow Aug 17 '15 at 13:58
  • I'am not sure if it fit there, but I will write an answer, as it can fit there anyhow, as it can be an server admin question anyhow :) As, if I would answer with todo a dump of the table struct, and a grep between the two, it would had fit. It's a gray line in my own opinion – yagmoth555 Aug 17 '15 at 14:07
  • 1
    This is impossible to do reliably. Not all changes to structure of data between software revisions actually manifest themselves as changes in the schema. Only the developers of the application know what exactly has changed. If the developers haven't provided you with an official tool for migration, you need to ask them how to migrate between specific versions of the application. – kasperd Aug 17 '15 at 17:03
  • 1
    I made a free tool which will generate the alter statements to make the second table the same as the first one tablediff.com. Still alpha. – Mihai Aug 5 at 8:50
31

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

  • 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. – Jason May 24 '18 at 20:30
  • 1
    @Jason glad I could help !!! – RolandoMySQLDBA May 24 '18 at 21:47
  • Very helpful, saved me some precious time – Nikita Kurtin May 22 at 11:09
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  -
  • 1
    If there is an AUTO_INCREMENT, it might get it in the way. – RolandoMySQLDBA Sep 3 '14 at 20:55
  • Right, then you cut the autoincrement value – akuzminsky Sep 3 '14 at 22:16
  • Now, that's quick and dirty. +1 !!! – RolandoMySQLDBA Sep 4 '14 at 1:55
  • This seems a clever solution if you are working from the shell. Thank you. – sjdh Sep 4 '14 at 2:12
  • 2
    There is no guarantee the columns will be in the same order, so spec-wise identical schemas can produce different checksums. – Zds May 29 '15 at 14:51
1

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;
0

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

0

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;
-2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.