mysql - 寻找模式差异

Question

我想与表和列几乎相同的 DB Schema 进行比较。

mysql> select a.TABLE_SCHEMA, a.TABLE_NAME FROM information_schema.tables as a left join information_schema.tables as b on a.TABLE_NAME = b.TABLE_NAME WHERE a.TABLE_SCHEMA = 'india' AND b.TABLE_SCHEMA = 'china' AND b.TABLE_NAME IS NULL;

我预计上述查询会返回印度数据库中存在但中国数据库中不存在的表。但这似乎不起作用。除了表差异之外，我还需要找到 2 个数据库中可能不同的列。

我不想使用像 Navicat 这样的应用程序。我需要查询或 UNIX 命令。

Answer 1

您需要将 AND b.TABLE_SCHEMA = 'china' 谓词从 WHERE 移动到 JOIN:

select a.TABLE_SCHEMA, a.TABLE_NAME 
FROM information_schema.tables as a left join information_schema.tables as b
  ON b.TABLE_SCHEMA = 'china'
    AND a.TABLE_NAME = b.TABLE_NAME
WHERE a.TABLE_SCHEMA = 'india'  AND b.TABLE_NAME IS NULL

查找列信息:

SELECT a.TABLE_NAME, a.COLUMN_NAME
FROM information_schema.columns a LEFT JOIN information_schema.columns b
  ON b.TABLE_SCHEMA = 'china'
    AND a.TABLE_NAME = b.TABLE_NAME
    AND a.ORDINAL_POSITION = b.ORDINAL_POSITION
    AND a.COLUMN_DEFAULT   = b.COLUMN_DEFAULT
    AND a.IS_NULLABLE      = b.IS_NULLABLE
    AND a.DATA_TYPE        = b.DATA_TYPE
    AND a.COLUMN_TYPE      = b.COLUMN_TYPE
.... Add other comparisons here depending on what you consider non identical.
WHERE a.TABLE_SCHEMA = 'india' AND b.COLUMN_NAME IS NULL