為了自動化我的一個 MySQL 數據庫的備份過程，我想比較兩個表的結構（目前版本與舊版本）。

你能想到一個可以比較兩個表的查詢嗎？

以下是一些您可以比較的範例表。

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

前兩個表具有相同的結構。最後一個不同。我只需要知道兩個表是否具有不同的結構。我對它們的不同之處不感興趣。

目前數據庫中的兩個表

如果您想知道兩個表是否不同，請執行此

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;

如果您確實需要查看差異，請執行此

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;

特定數據庫中的兩個表

如果您想知道數據庫中的兩個表是否不同mydb，請執行此

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;

如果您確實需要查看差異，請執行此

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;

兩個不同數據庫中的兩個表

如果您想知道db1.tb1和db2.tb2是否不同，請執行此

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;

如果您確實需要查看差異，請執行此

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;

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/75532