通过sql语句查询三数据库的表、索引、字段的差异

–三个mysql库 检查差异

— 检查三个库的表的差异
SELECT n.TABLE_NAME,
MAX(CASE WHEN s.TABLE_SCHEMA = ‘skong_tuangou_t’ THEN ‘✅’ ELSE ‘❌’ END) AS t,
MAX(CASE WHEN s.TABLE_SCHEMA = ‘skong_tuangou_u’ THEN ‘✅’ ELSE ‘❌’ END) AS u,
MAX(CASE WHEN s.TABLE_SCHEMA = ‘skong_tuangou_zdx’ THEN ‘✅’ ELSE ‘❌’ END) AS zdx
FROM (
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘skong_tuangou_t’ AND TABLE_TYPE = ‘BASE TABLE’
UNION
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘skong_tuangou_u’ AND TABLE_TYPE = ‘BASE TABLE’
UNION
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘skong_tuangou_zdx’ AND TABLE_TYPE = ‘BASE TABLE’
) AS n
LEFT JOIN INFORMATION_SCHEMA.TABLES AS s
ON s.TABLE_NAME = n.TABLE_NAME
AND s.TABLE_TYPE = ‘BASE TABLE’
AND s.TABLE_SCHEMA IN (‘skong_tuangou_t’,’skong_tuangou_u’,’skong_tuangou_zdx’)
GROUP BY n.TABLE_NAME
ORDER BY n.TABLE_NAME;

— 检查三个库列的差异
SELECT b.TABLE_NAME,
b.COLUMN_NAME,
‘t’ AS ref_side,
IF(u.COLUMN_NAME IS NULL, ‘❌缺失’,
IF((b.COLUMN_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT, b.EXTRA)
= (u.COLUMN_TYPE, u.IS_NULLABLE, u.COLUMN_DEFAULT, u.EXTRA),
‘✅一致’,’类型差异’)) AS u_result,
IF(z.COLUMN_NAME IS NULL, ‘❌缺失’,
IF((b.COLUMN_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT, b.EXTRA)
= (z.COLUMN_TYPE, z.IS_NULLABLE, z.COLUMN_DEFAULT, z.EXTRA),
‘✅一致’,’类型差异’)) AS zdx_result
FROM (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ‘skong_tuangou_t’
) AS b
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS u
ON u.TABLE_NAME = b.TABLE_NAME AND u.COLUMN_NAME = b.COLUMN_NAME AND u.TABLE_SCHEMA = ‘skong_tuangou_u’
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS z
ON z.TABLE_NAME = b.TABLE_NAME AND z.COLUMN_NAME = b.COLUMN_NAME AND z.TABLE_SCHEMA = ‘skong_tuangou_zdx’
WHERE u.COLUMN_NAME IS NULL OR z.COLUMN_NAME IS NULL
OR (b.COLUMN_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT, b.EXTRA)
<> (u.COLUMN_TYPE, u.IS_NULLABLE, u.COLUMN_DEFAULT, u.EXTRA)
OR (b.COLUMN_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT, b.EXTRA)
<> (z.COLUMN_TYPE, z.IS_NULLABLE, z.COLUMN_DEFAULT, z.EXTRA)
ORDER BY b.TABLE_NAME, b.COLUMN_NAME;

— 检查三个库索引的差异

SELECT b.TABLE_NAME,
b.INDEX_NAME,
‘t’ AS ref_side,
IF(u.INDEX_NAME IS NULL, ‘❌缺失’,
IF(b.idx_cols = u.idx_cols AND b.NON_UNIQUE = u.NON_UNIQUE, ‘✅一致’, ‘定义差异’)) AS u_result,
IF(z.INDEX_NAME IS NULL, ‘❌缺失’,
IF(b.idx_cols = z.idx_cols AND b.NON_UNIQUE = z.NON_UNIQUE, ‘✅一致’, ‘定义差异’)) AS zdx_result
FROM (
SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE,
GROUP_CONCAT(CONCAT(COLUMN_NAME,'(‘,SEQ_IN_INDEX,’)’) ORDER BY SEQ_IN_INDEX) AS idx_cols
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘skong_tuangou_t’
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE
) AS b
LEFT JOIN (
SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE,
GROUP_CONCAT(CONCAT(COLUMN_NAME,'(‘,SEQ_IN_INDEX,’)’) ORDER BY SEQ_IN_INDEX) AS idx_cols
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘skong_tuangou_u’
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE
) AS u ON b.TABLE_NAME = u.TABLE_NAME AND b.INDEX_NAME = u.INDEX_NAME
LEFT JOIN (
SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE,
GROUP_CONCAT(CONCAT(COLUMN_NAME,'(‘,SEQ_IN_INDEX,’)’) ORDER BY SEQ_IN_INDEX) AS idx_cols
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘skong_tuangou_zdx’
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE
) AS z ON b.TABLE_NAME = z.TABLE_NAME AND b.INDEX_NAME = z.INDEX_NAME
WHERE u.INDEX_NAME IS NULL OR z.INDEX_NAME IS NULL
OR b.idx_cols <> u.idx_cols OR b.NON_UNIQUE <> u.NON_UNIQUE
OR b.idx_cols <> z.idx_cols OR b.NON_UNIQUE <> z.NON_UNIQUE
ORDER BY b.TABLE_NAME, b.INDEX_NAME;