2つのデータベース間の差分を求めるクエリ
※MySQLを前提として書いています
本番環境と開発環境に違いがないか確認するのが面倒でテンプレクエリ作ったのでメモ。
※
クエリで済ませたいというわけでなければ、
テキスト等に出力してWinmergeあたりで比較した方が早いこともあります
テーブル構成の差分を求める
以下クエリで、「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