使用sql对比Mysql中数据库2个表结构

发表于 | 分类于 |
使用sql对比Mysql中数据库2个表结构

比较两个数据表的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
	column_name,
	max(
		CASE
		WHEN table_name = 'table1' AND table_schema = 'db1'  THEN
			'Yes'
		END
	) AS in_table_1,
	max(
		CASE
		WHEN table_name = 'table1' AND table_schema = 'db2' THEN
			'Yes'
		END
	) AS in_table_2
FROM
	information_schema. COLUMNS
WHERE
	(
		(
			table_schema = 'db1'
			AND table_name = 'table1'
		)
		OR (
			table_schema = 'db2'
			AND table_name = 'table1'
		)
	)
AND table_name IN ('table1', 'table1')
GROUP BY
	column_name
ORDER BY
	column_name;
References:
  1. mysql: compare structure of two tables
  2. Query to compare the structure of two tables in MySQL