【MySQL】データベース一覧を確認する

SHOW DATABASESでデータベース一覧を確認する

MySQLでデータベース一覧を確認するには、SHOW DATABASES文を使う。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| analytics          |
| information_schema |
| mysql              |
| performance_schema |
| reportdb           |
| shopdb             |
| sys                |
+--------------------+

自分で作成したデータベースに加えて、初期状態から存在するinformation_schema・mysql・performance_schema・sysも表示される。
それぞれの役割は以下の通り。

information_schema: テーブルやカラムなどのメタデータを提供するビュー群
mysql: ユーザー権限や設定など、サーバー自体の管理情報を格納するシステムデータベース
performance_schema: サーバーの内部動作を監視するための統計情報を格納するデータベース
sys: performance_schemaの情報を見やすく加工したビュー群

結果は名前順にソートされて表示される。

名前で絞り込む

LIKE句を付けると、名前が一致するデータベースだけを表示する。

mysql> SHOW DATABASES LIKE 'shop%';
+------------------+
| Database (shop%) |
+------------------+
| shopdb           |
+------------------+

WHERE句でも絞り込める。

mysql> SHOW DATABASES WHERE `Database` NOT IN ('information_schema','mysql','performance_schema','sys');
+-----------+
| Database  |
+-----------+
| analytics |
| reportdb  |
| shopdb    |
+-----------+

mysqlコマンドに接続せずに一覧を表示する

mysqlshowコマンドを使うと、対話モードに入らずデータベース一覧だけを表示できる。

$ mysqlshow -u root -p

mysqlコマンドに-eオプションでSHOW DATABASESを渡しても同様の結果を得られる。

$ mysql -u root -p -e "SHOW DATABASES;"

information_schema.schemataでデータベース一覧を確認する

SQLでデータベース一覧を取得する場合はinformation_schema.schemataビューを参照する。
MySQLでは「データベース」と「スキーマ」は同じ意味であり、schemataはデータベース一覧を返す。

mysql> SELECT * FROM information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | information_schema | utf8mb3                    | utf8mb3_general_ci     |     NULL | NO                 |
| def          | performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | shopdb             | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | analytics          | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | reportdb           | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+

SHOW DATABASESとは異なり、結果は作成順に近い順序で返り、名前順にはソートされない。
名前順で取得したい場合はORDER BYを指定する。

SELECT SCHEMA_NAME FROM information_schema.schemata ORDER BY SCHEMA_NAME;

schemataの主なカラム

information_schema.schemataには以下のようなカラムが存在する。

CATALOG_NAME: カタログ名（MySQLでは常にdef）
SCHEMA_NAME: データベース名
DEFAULT_CHARACTER_SET_NAME: デフォルトの文字セット
DEFAULT_COLLATION_NAME: デフォルトの照合順序
DEFAULT_ENCRYPTION: テーブルスペースの暗号化をデフォルトで有効にするか

システムデータベースを除外する

システムデータベースを除外する場合はSCHEMA_NAMEを条件に指定する。

SELECT SCHEMA_NAME FROM information_schema.schemata
WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY SCHEMA_NAME;

+-------------+
| SCHEMA_NAME |
+-------------+
| analytics   |
| reportdb    |
| shopdb      |
+-------------+

シェルスクリプトから一覧を取得する

-N（カラム名の非表示）と-B（バッチモード）を組み合わせると、データベース名だけを1行ずつ出力できる。

$ mysql -u root -p -N -B -e "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')"
analytics
reportdb
shopdb

権限のあるデータベースだけが一覧に表示される

information_schema.schemataは権限で絞り込まれるビューであり、接続権限のないデータベースは一覧に表示されない。
SHOW DATABASESも同様に、SHOW DATABASES権限を持たないユーザーには、自身が何らかの権限を持つデータベースしか表示されない。

例えばshopdbにのみ権限を持つapp_userで接続すると、システムデータベースの一部とshopdbしか表示されない。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| shopdb             |
+--------------------+

rootで確認できたanalytics・reportdb・mysql・sysは表示されない。
PostgreSQLのpg_databaseはクラスタ全体で共有され、接続権限のないデータベースも一覧に表示されるため、この点はMySQLと挙動が異なる。
参考: 【PostgreSQL】データベース一覧を確認する

接続中のデータベースを確認する

一覧ではなく現在接続しているデータベースを確認する場合はDATABASE()関数を使う。

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| shopdb     |
+------------+

データベースを指定せずに接続した場合はNULLが返る。
mysqlのプロンプトにもデータベース名が表示され、\s（statusコマンド）で接続情報をまとめて確認できる。