MySQL クイックデータ比較テクニック

MySQL の運用と保守において、R&D の同僚が 2 つの異なるインスタンスのデータを比較し、違いを見つけたいと考えています。主キーに加えて、すべてのフィールドを比較する必要があります。どうすればよいでしょうか?

最初の解決策は、比較のために 2 つのインスタンスから各データ行を抽出するプログラムを作成することです。これは理論的には可能ですが、比較に時間がかかります。

2 番目の解決策は、各データ行のすべてのフィールドを結合し、チェックサム値を取得して、チェックサム値に従って比較することです。これは実現可能と思われるので、試してみてください。

まず、すべてのフィールドの値を結合し、MySQL が提供する CONCAT 関数を使用する必要があります。CONCAT 関数に NULL 値が含まれている場合、最終結果は NULL になります。したがって、次のように IFNULL 関数を使用して NULL 値を置き換える必要があります。

CONCAT(IFNULL(C1,''),IFNULL(C2,''))

結合するテーブルには多くの行があり、手動でスクリプトを作成するのは面倒です。心配しないでください。information_schema.COLUMNS を使用して処理できます。

## 列名の連結文字列を取得する SELECT
GROUP_CONCAT('IFNULL(',COLUMN_NAME,','''')')
information_schema.COLUMNS から 
ここで、TABLE_NAME='テーブル名';

次のテストテーブルがあると仮定します。

テーブル t_test01 を作成します
（
 id INT AUTO_INCREMENT 主キー、
 C1 INT、
 C2 INT
）

次に、次の SQL を抽出します。

選択
id、
MD5(CONCAT(
IFNULL(id,'')、
IFNULL(c1,'')、
IFNULL(c2,'')、
)) AS md5_value
t_test01より

2 つのインスタンスで実行し、Beyond Compare を使用して結果を比較します。異なる行と主キー ID を見つけるのは簡単です。

データ量が多いテーブルの場合、結果セットも大きくなり、比較が難しくなります。そのため、まず結果セットを縮小してみてください。複数の行の md5 値を組み合わせて MD5 値を計算できます。最終的な MD5 値が同じであれば、これらの行は同じです。異なる場合は、違いがあることを証明します。次に、これらの行を 1 行ずつ比較します。

1,000 行のグループで比較すると仮定します。グループ化された結果を結合する必要がある場合は、GROUP_CONCAT 関数を使用する必要があります。結合されたデータの順序を保証するために、GROUP_CONCAT 関数で並べ替えを追加する必要があることに注意してください。SQL は次のとおりです。

選択
min(id) を min_id として、
max(id) を max_id として、
count(1)をrow_countとして、
MD5(GROUP_CONCAT(
MD5(CONCAT(
IFNULL(id,'')、
IFNULL(c1,'')、
IFNULL(c2,'')、
)) IDで並べ替え
))AS md5_value
t_test01より
GROUP BY (id div 1000)

実行結果は次のとおりです。

最小ID 最大ID 行数 md5値
0 999 1000 7d49def23611f610849ef559677fec0c
1000 1999 1000 95d61931aa5d3b48f1e38b3550daee08
2000 2999 1000 b02612548fae8a4455418365b3ae611a
3000 3999 1000 fe798602ab9dd1c69b36a0da568b6dbb

異なるデータが少ない場合は、数千万のデータを比較する必要がある場合でも、min_id と max_id に基づいて相違のある 1,000 のデータを簡単に見つけ出し、MD5 値を 1 行ずつ比較して、最終的に異なる行を見つけることができます。

最終比較表:

追伸:

GROUP_CONCAT を使用する場合は、MySQL 変数 group_concat_max_len を設定する必要があります。デフォルト値は 1024 で、超過分はステージングされます。