アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

2つのデータベース間の差分を求めるクエリ

本番環境と開発環境に違いがないか確認するのが面倒でテンプレクエリ作ったのでメモ。

テーブル構成の差分を求める

以下クエリで、「database_1」「database_2」を比較し、
いずれか片方にしかないカラムを取得することができます。

SELECT 
  `schema`.`database`
 ,`schema`.`table_name`
 ,`schema`.`column_name`
FROM
  (
    SELECT
      TABLE_NAME AS `table_name`
     ,COLUMN_NAME AS `column_name`
     ,GROUP_CONCAT(`table_schema`) AS `database`
     ,COUNT(*) AS `count`
    FROM
      `information_schema`.`columns`
    WHERE
      `table_schema` IN ('database_1', 'database_2')
    GROUP BY
      TABLE_NAME
     ,COLUMN_NAME
  ) AS `schema`
WHERE `schema`.`count` = 1

もっと詳しく比較したい場合はこちら。型などの違いがある場合も検出します。
(使う状況によって比較内容は要調整)

SELECT 
  `schema`.`database`
 ,`schema`.`table_name`
 ,`schema`.`column_name`
FROM
  (
    SELECT
      TABLE_NAME AS `table_name`
     ,COLUMN_NAME AS `column_name`
     ,GROUP_CONCAT(`table_schema`) AS `database`
     ,COUNT(*) AS `count`
     ,COUNT(DISTINCT IF(ORDINAL_POSITION IS NULL, '', ORDINAL_POSITION)) AS `ordinal_position_count`
     ,COUNT(DISTINCT IF(COLUMN_DEFAULT IS NULL, '', COLUMN_DEFAULT)) AS `column_default_count`
     ,COUNT(DISTINCT IF(IS_NULLABLE IS NULL, '', IS_NULLABLE)) AS `is_nullable_count`
     ,COUNT(DISTINCT IF(COLUMN_TYPE IS NULL, '', COLUMN_TYPE)) AS `column_type_count`
     ,COUNT(DISTINCT IF(CHARACTER_SET_NAME IS NULL, '', CHARACTER_SET_NAME)) AS `character_set_name_count`
     ,COUNT(DISTINCT IF(COLLATION_NAME IS NULL, '', COLLATION_NAME)) AS `collation_name_count`
     ,COUNT(DISTINCT IF(COLUMN_KEY IS NULL, '', COLUMN_KEY)) AS `column_key_count`
     ,COUNT(DISTINCT IF(EXTRA IS NULL, '', EXTRA)) AS `extra_count`
     ,COUNT(DISTINCT IF(COLUMN_COMMENT IS NULL, '', COLUMN_COMMENT)) AS `column_comment_count`
    FROM
      `information_schema`.`columns`
    WHERE
      `table_schema` IN ('database_1', 'database_2')
    GROUP BY
      TABLE_NAME
     ,COLUMN_NAME
  ) AS `schema`
WHERE `schema`.`count` = 1
OR    `schema`.`ordinal_position_count` <> 1
OR    `schema`.`column_default_count` <> 1
OR    `schema`.`is_nullable_count` <> 1
OR    `schema`.`column_type_count` <> 1
OR    `schema`.`character_set_name_count` <> 1
OR    `schema`.`collation_name_count` <> 1
OR    `schema`.`column_key_count` <> 1
OR    `schema`.`extra_count` <> 1
OR    `schema`.`column_comment_count` <> 1

実行例

database_1 table_1 column_1
database_1,database_2 table_1 column_2

1つ目は「table_1.column_1」が「database_2」にないこと、
2つ目は「table_1.column_2」について両データベース間で何らかの差があることを示しています。

特定テーブルのデータの差分を求める

こちらはあまり難しくないですが、一応。
下記は「database_1」「database_2」の「table」テーブル同士を比較し、
いずれかのみに存在するレコードを取得することができます。
「column」はPKのカラムです。
(必要に応じて返却内容を追加したり、複合PKであればJOIN条件を増やしたりして使用します。)

SELECT
  'A' AS `database`
 ,`A`.`column`
FROM
  `database_1`.`table` AS `A`
LEFT JOIN
  `database_2`.`table` AS `B`
  ON
    `A`.`column` = `B`.`column`
WHERE
  `B`.`column` IS NULL

UNION

SELECT
  'B' AS `database`
 ,`B`.`column`
FROM
  `database_2`.`table` AS `B`
LEFT JOIN
  `database_1`.`table` AS `A`
  ON
    `B`.`column` = `A`.`column`
WHERE
  `A`.`column` IS NULL