業務上で「複数テーブルの行数を調べたい」という場面があり、MySQLのinformation_schemaにかるーく触れる機会があり、初めて知ったことが多かったので軽くまとめ
複数テーブルの行数をまとめて出せる？
1つのDB内の複数テーブルの行数を調べたいということがあった。
ただテーブルひとつずつにcountをしていくのも非常に面倒。。。
なのでinfomation_schemaを使用して出してみた
※使用しているのはMySQLのサンプルDBであるSalkilaDBです。
- どうやらinfomation_schemaには行数カウントできる
TABLE_ROWSというものがあるらしい👀 -
TABLE_SCHEMAはDB名なので、これでDBを指定できる -
TABLE_NAMEはテーブル名なので、これでテーブルを指定できる（複数可）
→なんだかいけそうなのでとりま試してみよう
mysql> SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sakila'
AND TABLE_NAME IN ('rental', 'payment', 'film');
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| film | 1000 |
| payment | 0 |
| rental | 0 |
+------------+------------+
3 rows in set (0.02 sec)
あれ、、どのテーブルもレコードは入っているはずなのにpaymentとrentalが0行だ。。。
一応各テーブルの行数を見てみた
mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
| 16044 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from rental;
+----------+
| count(*) |
+----------+
| 16044 |
+----------+
1 row in set (0.05 sec)
filmテーブルの行数は合っているようだが、paymentとrentalテーブルのレコードは入っているようだ👀
TABLE_ROWSは「概算」しか出さない
- 公式リファレンスの
TABLE_ROWSにちゃんとそれっぽい理由が書いてあった
InnoDB などのほかのストレージエンジンの場合、この値は概算であり、実際の値と 40% から 50% まで異なる可能性があります。 このような場合、正確な数を取得するには SELECT COUNT(*) を使用します。
- 正直
1000しか入っていないfilmが1000で、それより多く入っているテーブル2つが0というのがかなり不可解だが、何にしてもTABLE_ROWSで出される数値は正確な行数ではないということがわかった - 結論：レコード入っているテーブルが0行にならなかったら「レコードが入っているか否か」をまとめて確認したいときに使用できそうだけど、駄目だこれだと使えなさそう。。。。。。。。。。
information_schemaとは
軽くinfomation_schema自体についても調べてみた
- DBサーバは、DBに挿入するデータを格納するのはもちろん、データを格納するために作成された全てDBオブジェクト（
テーブル・ビュー・インデックス等）に関する情報も格納している- これらは一般に
メタデータと呼ばれる（「データに関するデータ」みたいなもん） - もうちょい専門的な言葉だと
データディクショナリ(data dictionary)やシステムカタログ(system catalog)なんて呼ばれたりもするらしい
- これらは一般に
- これらメタデータは永続的に格納する必要があり、かつSQLを実行するためにこのメタデータを素早く取り出したり、変更できるようにしておく必要がある
-
information_schemaはMySQLにおいて上記を実現するための特別なデータベース（ちなSQL Serverにも同名のものがあるが、こちらはスキーマにあたるらしい）
-
- MySQLのinformation_schemaに含まれるオブジェクトは全てビューである
- MySQLの場合は最初からデータベースとして入っている
mysql> SHOW databases; -- DBとして最初から入っている
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
+--------------------+
5 rows in set (0.02 sec)
良い使い方がありそうなので、色々調べていきたい