Mysql 不同实例的表结构字段对比实践

两台实例如果需要进行表结构对比时，一旦涉及的表过多或者字段过多，对比起来非常麻烦。

这时候沿用Mysql SQL问题mysql Illegal mix of collations for operation UNION 排查解决思路

来完成这部分的对比。

这里拿表字段举例

先从主库information_schema库中将表COLUMNS 导入到需要对比的库中。

这里需要注意导出成sql文件的时候，记得将TEMPORARY这个关键字去掉，这个代表临时表的意思。临时表会导致看不到任何东西。

这个时候对比库已经拥有的主库的所有字段信息，然后根据这些字段信息和对比库的字段做交叉对比。

参考语句:

SELECT
	a.TABLE_NAME,
	a.COLUMN_NAME,
	b.COLUMN_NAME
FROM
	(
		SELECT
			*
		FROM
			COLUMNS b
		WHERE
			b.TABLE_SCHEMA = '主库名称'
		AND b.TABLE_NAME IN (
			SELECT
				*
			FROM
				(
					SELECT
						a.TABLE_NAME
					FROM
						information_schema.`COLUMNS` a
					WHERE
						a.TABLE_SCHEMA = '对比库的名称'
				) a
		)
	) a
LEFT JOIN (
	SELECT
		a.TABLE_NAME,
		a.COLUMN_NAME
	FROM
		information_schema.`COLUMNS` a
	WHERE
		a.TABLE_SCHEMA = '对比库的名称'
) b ON a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE
	b.COLUMN_NAME IS NULL;

查询出来的结果就是对比库和主库之间差异字段。

同理: 表名称、索引… 一系列的对比都可以参考该思路。

另外补充一些根据Mysql查询表的SQL语句

-- 根据表的创建时间进行排序
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'marketing_db' ORDER BY create_time DESC;

-- 查看表的修改时间字段是否有默认值为当前时间戳
SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_SCHEMA = 'marketing_db'  and column_name = 'updated' and extra = '';

-- 导出表的结构文件
select DISTINCT a.table_name,
       b.table_comment,
       a.column_name,
       a.column_type,
       a.column_comment
  from `information_schema`.columns a
  left join `information_schema`.tables b on a.table_name= b.table_name
 where a.table_schema= 'marketing_db'
   and a.table_name in('t_activity_info', 't_activity_ticket_record') ;

-- 根据特定的表创建相同的表
create table temp_jinmao_data.c_user like marketing_db.c_user 							;