一、问题背景 

       当您有两个表（或 SELECT 语句的结果集）要进行比较，以查看任何列中的任何更改，以及查看 一个表中存在哪些行在另一个表中不存在时，我发现 UNION 运算符工作得很好。

二、思想与限制 

        基本思想：如果我们对两个表所有列的并集进行 GROUP，那么如果这两个表相同，所有分组的 COUNT(*) 必须等于 2。但是对于在任何列上不完全匹配的任何行执行GROUP BY 的COUNT(*) 将会是 1，而这些正好我们想要的。当然我们还可以在 UNION 的每个部分添加一列来指示每行来自哪个表，否则无法区分哪行来自哪个表。

        限制要求：两个表的表结构一致，且主键字段相同；或者两个select结果集结构是一样的；

三、模拟示例 

下面以PostgreSQL数据库为例

1、第一个表

CREATE TABLE "public"."HI_citys" (
"id" int8 NOT NULL,
"status" int4,
"fcname" text COLLATE "default",
"shape" text COLLATE "default",
"parent" int8,
"create_time" timestamp(6),
"modify_time" timestamp(6),
PRIMARY KEY ("id")
)

2、 第二个表

CREATE TABLE "public"."TA_citys" (
"id" int8 NOT NULL,
"status" int4,
"cname" text COLLATE "default",
"shape" text COLLATE "default",
"parent" int8,
"create_time" timestamp(6),
"modify_time" timestamp(6),
PRIMARY KEY ("id")
)

3、 比较SQL

select min(table_name),id,status,cname,shape,parent,create_time,modify_time from 
(
    select 'HI_citys' as table_name,id,status,cname,shape,parent,create_time,modify_time from "HI_citys"
    UNION ALL
    select 'TA_citys' as table_name,id,status,cname,shape,parent,create_time,modify_time from "TA_citys"
) tmp
group by id,status,cname,shape,parent,create_time,modify_time
HAVING COUNT(*) = 1
ORDER BY id

 

领导的要求：同一个系统有多个环境，数据库用的是mysql ,需要比较一下他们的表和表结构信息，由于网络问题不能直接用mysqldiff 工具比较，所以我用了python下pandas 、list、pymysql来操作。

操作思路：

比较表

1.用pymysql 连接测试环境1和测试环境2的数据库；

2.读取表的信息，转存在pandas 的数据框中df1和df2；

3.读取数据框中的表名，转成列表格式，就可以比对两个表的异同点；

4.转存表信息的数据框为excel文件

比较表结构

1.相同表的列表，读取对应得表结构信息df1_DE和df2_DE;

2.找出两个数据框的异同

范例1l(两个都可以连接网络的）：

import pandas as pd

import pymysql

#连接测试环境1 ，阿里云上的

conn = pymysql.connect(host='r*********.mysql.rds.aliyuncs.com',user='*****',password='*****',db='abc',port=3306,charset='utf8')

cursor = conn.cursor()

#df为阿里云上的数据库abc的数据表信息

df=pd.read_sql("select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='abc'",conn)

df_table_list=df['TABLE_NAME'].to_list()

#连接测试环境2 ，本地的

conn1 = pymysql.connect(host='*******',user='*****',password='*****',db='abc',port=3306,charset='utf8')

cursor1 = conn1.cursor()

#df1为本地的数据库ab数据表信息

df1=pd.read_sql("select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='abc'",conn1)

df1_table_list=df1['TABLE_NAME'].to_list()

#找出两个数据框相同的表的信息

table_list=[x for x in df_table_list if x in df1_table_list]

#查看相同表的个数

len(table_list)

#新建数据框本地数据库df_test_table

df_test_table=pd.DataFrame()

#新建数据框阿里云数据库df_online_table

df_online_table=pd.DataFrame()

#两个数据框df_test_table和df_online_table的合集

df_diff_table_DE=pd.DataFrame()

df_diff_table=pd.DataFrame()

for table in test_table_list:

    #查询每张表的表结果信息的sql 语句,用for循环

    sql1 = f"select COLUMN_NAME,COLUMN_TYPE,ORDINAL_POSITION from information_schema.columns where TABLE_SCHEMA='abc' and TABLE_NAME='{table}'"

    #执行查询语句

    df_test=pd.read_sql(sql1,conn1)

    #df_test数据框中添加TABLE_NAME列，并赋值为变量table

    df_test['TABLE_NAME']=table

    #df_test数据框中添加TABLE_TYPE列，并赋值为常量字符串test

    df_test['TABLE_TYPE']='test'

    #for循环的数据框df_test插入到df_test_table中，并且重建索引

    df_test_table=df_test_table.append(df_test,ignore_index=True)

    #执行查询语句

    df_online=pd.read_sql(sql1,conn)

    #df_online数据框中添加TABLE_NAME列，并赋值为变量table

    df_online['TABLE_NAME']=table

    #df_test数据框中添加TABLE_TYPE列，并赋值为常量字符串online

    df_online['TABLE_TYPE']='online'

    df_online_table=df_online_table.append(df_online,ignore_index=True)

   #比较两个相同表之间的表字段，不同的哪些保存下来

    df_diff_table_DE=df_diff_table_DE.append(df_test_table)

    df_diff_table_DE=df_diff_table_DE.append(df_online_table)

    df_diff_table_DE=df_diff_table_DE.drop_duplicates(subset=['COLUMN_NAME','COLUMN_TYPE','ORDINAL_POSITION','TABLE_NAME'],keep=False)

    df_diff_table=df_diff_table.append(df_diff_table_DE,ignore_index=True)

#保存相关信息

writer = pd.ExcelWriter('E:\\TABLENAME.xlsx')

#阿里云上的数据库表信息

df.to_excel(writer, sheet_name='online_table')

#本地的数据库表信息

df1.to_excel(writer, sheet_name='bendi_table')

#本地的数据表的表结构

df_test_table.to_excel(writer, sheet_name='gtja_table_DE')

#阿里云上的数据表的表结构

df_online_table.to_excel(writer, sheet_name='online_table_DE')

#相同表名不同的表结构

df_diff_table.to_excel(writer, sheet_name='df_diff_table')

writer.save()

范例1l(两个不能直接互连网络的）

##将上面的文件上传到目的主机，我们以阿里云的数据表对比隔离网络的数据表

import pandas as pd

import pymysql

#连接隔离环境2 ，本地的

conn2 = pymysql.connect(host='*******',user='*****',password='*****',db='abc',port=3306,charset='utf8')

cursor2 = conn2.cursor()

#df1为本地的数据库ab数据表信息

df3=pd.read_sql("select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='abc'",conn2)

df3_table_list=df3['TABLE_NAME'].to_list()

#读取范例一的数据库表格文件的表名和表结构信息

#df4 = pd.read_excel(r'/opt/TABLENAME.xlsx',sheet_name='online_table')

df_online_table=pd.read_excel(r'/opt/TABLENAME.xlsx',sheet_name='online_table_DE')

#df4 的表格信息TABLE_NAME转换为列表

df4_table_list=df4['TABLE_NAME'].to_list()

#找出两个数据框相同的表的信息

table_list2=[x for x in df3_table_list if x in df4_table_list]

#查看相同表的个数

len(table_list)

df_re_table=pd.DataFrame()

df_online1_table=pd.DataFrame()

#两个数据框df_test_table和df_online_table的合集

df_diff_table_DE=pd.DataFrame()

df_diff_table=pd.DataFrame()

for table in test_table_list:

    #查询隔离环境

    #每张表的表结果信息的sql 语句,用for循环

    sql1 = f"select COLUMN_NAME,COLUMN_TYPE,ORDINAL_POSITION from information_schema.columns where TABLE_SCHEMA='abc' and TABLE_NAME='{table}'"

    #执行查询语句

    df_re=pd.read_sql(sql1,conn2)

    #df_test数据框中添加TABLE_NAME列，并赋值为变量table

    df_re['TABLE_NAME']=table

    #df_test数据框中添加TABLE_TYPE列，并赋值为常量字符串test

    df_re['TABLE_TYPE']='re'

    #for循环的数据框df_test插入到df_test_table中，并且重建索引

    df_re_table=df_re_table.append(df_re,ignore_index=True)

    

    #找出阿里云上对应表名的对应表结构信息

    a = df_online_table[(df_online_table['TABLE_NAME']==table)]

    df_online1_table=df_online1_table.append(a,ignore_index=True)

#比较两个相同表之间的表字段，不同的哪些保存下来

df_diff_table_DE=df_diff_table_DE.append(df_online1_table)

df_diff_table_DE=df_diff_table_DE.append(df_re_table)

df_diff_table_DE=df_diff_table_DE.drop_duplicates(subset=['COLUMN_NAME','COLUMN_TYPE','ORDINAL_POSITION','TABLE_NAME'],keep=False)

df_diff_table=df_diff_table.append(df_diff_table_DE,ignore_index=True)

#保存相关信息

writer = pd.ExcelWriter('/opt/TABLENAME2.xlsx')

#隔离环境的数据库表信息

df3.to_excel(writer, sheet_name='geli_table')

#隔离环境的相同数据表的表结构

df_re_table.to_excel(writer, sheet_name='geli_table_DE')

#相同表名下不同的表结构

df_diff_table.to_excel(writer, sheet_name='df_diff_table')

writer.save()    

对比测试服务器与正式服务器的表结构差别可以使用软件进行对比，如navicat，可以对比各个表的具体差异，包括字段的增减、非空限定，数据类型，表是否存在等。

也可以使用语句查询，但是必须拥有两个数据库的权限。

判断两个数据库相同表的字段不为空是否相同

select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.COLUMN_TYPE,a.IS_NULLABLE,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.COLUMN_TYPE,b.IS_NULLABLE ,b.COLUMN_DEFAULT,b.COLUMN_COMMENT from information_schema.`COLUMNS` a inner join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.IS_NULLABLE<>b.IS_NULLABLE where a.IS_NULLABLE='NO';(在表中存在至少一个匹配时，INNER JOIN 关键字返回行。)

判断两个数据库相同表的字段默认值是否相同

select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.COLUMN_DEFAULT from information_schema.`COLUMNS` a inner join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.COLUMN_DEFAULT<>b.COLUMN_DEFAULT;

判断两个数据库相同表的字段数据类型是否相同,这里是判断数据类型不同如果要判断数据类型的长度不同需要用COLUMN_TYPE字段select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.DATA_TYPE ,b.COLUMN_DEFAULTfrom information_schema.`COLUMNS` a inner join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.DATA_TYPE<>b.DATA_TYPE;

判断两个数据库相同表的中互相不存在的字段select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULTfrom information_schema.`COLUMNS` a where a.TABLE_SCHEMA='db1' and a.COLUMN_NAME NOT IN(SELECT b.COLUMN_NAME from information_schema.`COLUMNS` b where b.TABLE_SCHEMA='db2' and a.TABLE_SCHEMA='db1'and a.TABLE_NAME=b.TABLE_NAME );

select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULTfrom information_schema.`COLUMNS` a where a.TABLE_SCHEMA='db2' and a.COLUMN_NAME NOT IN(SELECT b.COLUMN_NAME from information_schema.`COLUMNS` b where b.TABLE_SCHEMA='db1' and a.TABLE_SCHEMA='db2'and a.TABLE_NAME=b.TABLE_NAME );

 

mysql没有full jion所以变相的多做了一次select查询，这种方法性能比较差,对于表比较多的数据库建议使用上面的分开查询select b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.DATA_TYPE,c.TABLE_SCHEMA,c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE from

(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db2','db1') )a left join

(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db2')) b  on a.TABLE_NAME=b.TABLE_NAME AND a.COLUMN_NAME=b.COLUMN_NAME left join

(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db1')) c on a.TABLE_NAME=c.TABLE_NAME AND a.COLUMN_NAME=c.COLUMN_NAMEwhere b.COLUMN_NAME is null or c.COLUMN_NAME is null ;

(LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行)

判断两个数据库互相不存在的表

select a.TABLE_SCHEMA,a.TABLE_NAMEfrom information_schema.TABLES a where a.TABLE_SCHEMA='db1' and a.TABLE_NAME NOT IN(SELECT b.TABLE_NAME from information_schema.TABLES b where b.TABLE_SCHEMA='db2');

select a.TABLE_SCHEMA,a.TABLE_NAMEfrom information_schema.TABLES a where a.TABLE_SCHEMA='db2' and a.TABLE_NAME NOT IN(SELECT b.TABLE_NAME from information_schema.TABLES b where b.TABLE_SCHEMA='db1');

 

select b.TABLE_SCHEMA,b.TABLE_NAME,c.TABLE_SCHEMA,c.TABLE_NAME from

(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db2','db1') )a left join

(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db2')) b  on a.TABLE_NAME=b.TABLE_NAME left join

(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db1')) c on a.TABLE_NAME=c.TABLE_NAME where b.TABLE_NAME is null or c.TABLE_NAME is null ;

MySQL中修改多个数据库下包含相同字段的表中该字段的长度
一、业务场景
由于一开始设计数据库表结构的时候没能沟通协作好，导致不同的数据库之间，甚至同一个数据库下的不同表中，同一字段package_name的长度也不同，甚至一些表中字段名称是大写，一些是小写。由于长度不统一，上游业务中记录的数据中package_name字段可以存储数据，而下游业务在保存数据时mysql就会报错。为了解决这一问题，查阅了网上很多博客，最终想到以下两种方式解决。
方法一：使用存储过程
-- 如果存储过程存在就删除
DROP PROCEDURE
IF
	EXISTS userId;

delimiter //
CREATE PROCEDURE userId ( ) BEGIN
-- 定义循环条件
DECLARE
		flag INT DEFAULT 0;
-- 声明变量
	DECLARE
		db VARCHAR ( 50 );
	DECLARE
		tname VARCHAR ( 50 );
	DECLARE
		columnName VARCHAR ( 50 );
	DECLARE
		execSql VARCHAR ( 50 );
-- 使用游标
-- 业务要求：只查询以gpx或者gpe开头的数据库；排除视图
	DECLARE
		result CURSOR FOR SELECT
		table_schema,
		table_name,
		column_name
	FROM
		INFORMATION_SCHEMA.COLUMNS 
	WHERE
		COLUMN_NAME = 'PACKAGE_NAME' AND TABLE_NAME NOT LIKE AND (table_schema like 'gpx%' or table_schema like 'gpe%')'view%';
-- 退出循环
	DECLARE
		CONTINUE HANDLER FOR SQLSTATE '02000' 
		SET flag = 1;
-- 打开游标
	OPEN result;
	WHILE
			flag <> 1 DO
			FETCH result INTO db,tname,columnName;
	
		SET @execSql = CONCAT( 'ALTER TABLE `', db,'`.`', tname,'` MODIFY COLUMN ', columnName,' varchar(500);' );
		PREPARE stmt FROM @execSql;
		EXECUTE stmt;
		
	END WHILE;
	
	END;
// -- 调用存储过程更新数据
CALL userId ( );
// 
DROP PROCEDURE userId;
//

使用上面的存储过程会出现以下两种问题：

1、虽然所有包含package_name字段的表（mysql不区分大小写，字段名称为PACKAGE_NAME和package_name的表都能被查询得到)，且字段的名称都成功改为varchar(500)，但是字段的注释说明变成了空白。我尝试加上comment，但是sql执行一直报错，就放弃了。

2、可能一些表的字段的长度已经被修改了，但是接下来的执行报错了，存储过程没有回滚。本人对数据库研究不深，没有想到解决的办法。

有兴趣的大佬可以根据自己的兴趣修改一下，也许就解决了。
方法二：使用查询语句查询出符合条件的数据库表，之后给出所有的修改语句
第一步：
select 
 table_schema,
  table_name,
  column_name,
 CONCAT( 'ALTER TABLE `', table_schema,'`.`', table_name,'` MODIFY COLUMN ', column_name,' varchar(500);' )
 FROM
  INFORMATION_SCHEMA.COLUMNS 
 WHERE
  COLUMN_NAME = 'PACKAGE_NAME' AND (table_schema like 'gpx%' or table_schema like 'gpe%') AND TABLE_NAME NOT LIKE 'view%'

执行结果如下：



第二步：右键复制出来所有的修改字段的sql语句。



最终得到的结果为：


最后附上查询语句，方便查看操作结果
SELECT   TABLE_NAME '表名',TABLE_SCHEMA '数据库名',ORDINAL_POSITION '顺序',COLUMN_NAME '字段',DATA_TYPE '类型' 
    ,character_maximum_length '字符长',IF(COLUMN_KEY='PRI',"√","") '主键',IF(EXTRA='auto_increment',"√","") '自增长' 
  ,IF(IS_NULLABLE='YES',"√","") '允许空',CHARACTER_SET_NAME '编码',COLUMN_DEFAULT '默认值',COLUMN_COMMENT '说明' 
FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'PACKAGE_NAME' AND (table_schema like 'gpx%' or table_schema like 'gpe%') AND TABLE_NAME NOT LIKE 'view%'

查询结果：