mysql 两个表结构图_查询比较MySQL中两个表的结构

为了自动化我的一个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;

试试看 ！！！

标签：information-schema,mysql,ddl,schema

来源： https://codeday.me/bug/20190805/1587882.html