使用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