MySQLで全テーブルの正確なレコード数を取得する方法をご紹介します。phpMyAdminを使う前提で説明しますが、ターミナルなどのコマンドラインからでも同じ考え方でできるので参考にしてみてください。
もくじ
ロリポップでのMySQLバージョンアップのため
ロリポップのライトプランを使っていますがMySQLを5.6から5.7にバージョンアップすることになりました。ライトプランの場合は、データベースを一つしか作成できないのと、sshでアクセスできないのでターミナルは使えずphpMyAdminの操作しかできません。
バージョンアップをするには、一度データベースを削除する必要があるので、データを退避しなければなりません。そのために、phpMyAdminからデータをエクスポートします。
しかし、以下の記事で詳しく説明したように、phpMyAdminからのエクスポートはWebブラウザを介して行われるため不安定な部分があります。
なので、正常にエクスポートができたか確認する必要があります。
全テーブルの件数が一致してればOKと判断
正常なエクスポートをどう確認すればいいのでしょうか。
最善は、全データの中身まで一致していることを確認することでしょう。データ量が少なければそれは可能だと思います。まずエクスポート元の全データをCSVなどで吐き出しておきます。ローカルのPCなどにインストールしたMySQLに検証用の空のデータベースを作成します。phpMyAdminから全データをSQLでエクスポートします。それを検証用データベースにインポートします。そして、その検証用データベースからを全データをCSVに吐き出します。二つのCSVファイルのdiffをして一致していれば正常にエクスポートできたと言えます。
しかし、実際ギガバイト単位になってしまうと、diffをするのも現実的ではないです。また、エクスポート元からCSVファイルを吐き出すのもphpMyAdmin経由になるので、そのCSVファイルが壊れていない保証はありません。
なので、データ自体ではなく全テーブルの件数が、エクスポート元と検証用データベースで一致しているかで判断することにします。件数さえ一致していれば、あとは日本語の文字列などが文字化けしていないかなどを見ていけば、エクスポートが問題なくできていると判断できると考えました。
テーブルごとにカウントしたものを結合するしかない
ではどうすれば、テーブルごとの行数を確認できるのでしょうか？
実は、以下の記事にあるように、テーブルごとの行数を保持している「information_schema.tables 」というテーブルがあり、簡単に取得することができます。
MySQL の information_schema を使用して大きなテーブルの行数を調べる
しかし、この数字ざっくりとしたものでしかなく、実際のテーブルの行数と一致はしません。なので、エクスポートの正確性を確認するためには向いていません。
一つのテーブルを正確にカウントするのはSQLを多少知っている方にとってみれば簡単でしょう。
|
1 |
SELECT COUNT(*) FROM テーブル名; |
これをテーブル数分行って、表にまとめれば、この記事でやりたいことはできないことはないです。しかし、テーブル数が膨大だと、何時間かかるかわかりません。
そこで便利なのが「UNION」です。SQLのUNIONはSELECT文の結果同士を結合した表にすることができます。なので、全テーブル分のカウントの結果を結合するためのSQLを生成して、それを実行すればいいのです。「全テーブル分のカウントの結果を結合するためのSQL」を以降「カウント結合SQL」と呼びます。
「カウント結合SQL」は簡単に生成できます。テーブル一覧は簡単に取得できるので、それをSQLの形で出力してあげればいいのです。
以下が「『カウント結合SQL』を作るためのSQL」です。
|
1 2 3 4 |
select Concat('Select "',table_name, '" as tablename,count(*) from ',table_name,' Union ') as Query from INFORMATION_SCHEMA.TABLES WHERE table_schema = 'データベース名'; |
次の記事を参考にしました。
How to get record counts for all tables in MySQL database - MySQL developer Tutorial
これで、以下のようにテーブル数分の行となる「カウント結合SQL」が生成されます。
|
1 2 3 |
Select "ATABLE" as tablename,count(*) from ATABLE Union Select "BTABLE" as tablename,count(*) from BTABLE Union Select "CTABLE" as tablename,count(*) from CTABLE Union |
この最終行の最後の「Union」だけ、手動で削除してあげる必要があります。「Union」は前の結果と後の結果を結合するものなので、最終行には不要だからです。
これで「カウント結合SQL」は完成です。では、phpMyAdminでのやり方をご説明していきます。
phpMyAdminの事前設定
まず当方の環境だけ記載しておきます。
- MySQL 5.7.36-log
- phpMyAdmin 4.0.10.18
まずphpMyAdminを開き、「設定」から「Main panel」を開きます。
3つの設定値を変更します。
- 「行の最大表示数」をテーブル数より多く設定します。
- 「カラム名の差し込み間隔」を「0」にします。
- 「カラム文字制限」を「200」など大きな数字にします。
理由は後の手順をやればわかります。簡単に説明しておきます。
①：SQLの結果の表をコピペするのですが、１ページに全てのテーブル分の結果を表示して一度にコピペできるようにします。
②：デフォルトだと一定行おきにカラム名（見出し）が出力されてしまうのですが、これは不要なため0にして非表示にします。
③：「カウント結合SQL」を生成しますが、デフォルトの50文字だとSQLの文字列が表示しきれないので大きくします。
なお、この設定はセッションが変わるとクリアされます。
「カウント結合SQL」の生成と実行
データベースが選択された状態でSQLを実行する画面を開きます。「『カウント結合SQL』を作るためのSQL」のWHERE句のデータベース名に相当する部分をご自身のデータベース名にして、実行します。
出力された結果を全て選択してコピーします。
再度データベースが選択された状態でSQLを実行する画面を開き、コピーしたものをペーストします。そして、最終行の「Union」だけ削除し、実行します。
これでテーブルごとの正確な件数が取得できます。
取得した件数はExcelなどに貼り付けて比較
私の場合は、上記の表を全てコピーしてExcelやNumbersという表計算アプリに貼り付けます。
エクスポート元のデータと、ローカルの検証用データベースでも取得したデータのものを両方貼り付けて、値が一致しているか関数を使って判定します。単純に一致たらTRUEと表示されるようにしています。条件付き書式などを使って色をつけたらもっと見やすいです。
’
もちろん、両方テキストファイルにしてターミナルでdiffで比較するのもアリです。
ロギング系のテーブルは一致しないこともある
私の場合、アクセス解析のMatomoというアプリケーションでもこのデータベースを使っていました。その場合、システムを止めないと、アクセスがある度に新しいレコードが挿入されます。
なので、データをエクスポートしたタイミングと、ここまで説明してきたカウントをとる作業をするタイミングが違うと、わずかにレコード数が違う可能性も出てきます。
システムを完全に止めてからエクスポートやカウントをすればいいのですが、わざわざ止めるまでもない気もします。数字が一致しないテーブルがあれば、ロギング系のテーブルかどうかを見て、大きく違っていなければ大丈夫と考えてもいいと思います。
