第115回　mysqlshowコマンド

MySQLをインストールすると、ユーティリティとしてmysqldumpコマンドやmysqladminコマンドが使用可能になります。こうしたユーティリティの中にmysqlshowコマンドがありますが、これはMySQLのスキーマ、テーブル、カラムの一覧情報を取得するコマンドです。

今回はmysqlshowコマンドを使って、MySQLの情報の取得をやっていきます。なお、検証環境はCentOS 7, MySQLのバージョンは8.0.18を利用しています。

mysqlshowコマンド

mysqlshowコマンドを利用するには、mysqlshow -u <user名> -pを実行し、パスワードを入力することで実行できます。デフォルトの設定では/var/lib/mysql/mysql.sockを見てソケット接続を試みるため、もしsocketファイルの場所を指定する場合は--socket（または-S）でソケットファイルの場所を指定します。IP、PORTを指定して接続する場合は--host（または-h）と--port（または-P）を指定して接続します。

mysqlコマンドと同様に、環境変数MYSQL_PWDを利用することでパスワードの入力なしに利用することができますが、安全ではないため、お勧めではありません。

mysqlshow -u root --socket=/data/sample_mysql/mysql.sock -p
mysqlshow -u root -p -h 127.0.0.1 -P3309

この状態ではデータベース一覧が表示されます。mysqlコマンドでSHOW DATABASESを実行しても同じ結果が得られます。

$ mysqlshow -u root --socket=/data/sample_mysql/mysql.sock -p
Enter password:
+--------------------+
|     Databases      |
+--------------------+
| d1                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+

テーブル一覧の取得

mysqlshowにスキーマ名を渡すことで、テーブルの一覧を取得することができます。また、--verbose（-v）オプションを利用することで、カラムの数やテーブルのデータの件数を取得することができます。verbose（v）1回はカラムの個数、2回はテーブルの件数を表示します。

$ mysqlshow -u root --socket=/data/sample_mysql/mysql.sock -p d1 -vv
Enter password:
Database: d1
+------------+----------+------------+
|   Tables   | Columns  | Total Rows |
+------------+----------+------------+
| _dummy_new |        6 |      10000 |
| dummy      |        6 |      10000 |
| t1         |        2 |          2 |
| t10        |        1 |          0 |
| t2         |        2 |          3 |
| t7         |        1 |          0 |
| t9         |        1 |          0 |
+------------+----------+------------+
7 rows in set.

ここで取得されるデータの件数（Total Rows）は統計情報ではなく、mysqlshowが実際にSELECT COUNT(*) FROM <テーブル名>というクエリを実行するため、データが多いテーブルがある場合は注意が必要です。

なお、d1.t1のようにスキーマ名.テーブル名の形式で指定した場合、スキーマ名.テーブル名というデータベース名で検索してしまい、取得することができません。

$ mysqlshow -u root -p --socket=/data/sample_mysql/mysql.sock d1.t1
mysqlshow: Unknown database 'd1.t1'

また、--status（-i）を用いることで、各テーブルの追加情報を取得することができます。ここで取得できる情報はmysqlコマンドでSHOW TABLE STATUSを実行した結果と同じです。

$ mysqlshow -u root -p --socket=/data/sample_mysql/mysql.sock d1 t1 -i
Database: d1  Wildcard: t1
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation   | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+
| t1   | InnoDB | 10      | Dynamic    | 1    | 16384          | 16384       | 0               | 16384        | 0         |                | 2020-02-04 18:39:43 |             |            | utf8mb4_bin |          |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------+----------+----------------+---------+

カラムの情報の取得

カラム情報は、mysqlshowにスキーマ名、テーブル名を渡すことで取得することができます。この結果はSHOW FULL COLUMNS FROM <table_name>を実行した時と同じ結果になります。

$ mysqlshow -u root -p --socket=/data/sample_mysql/mysql.sock d1 t1
Database: d1  Table: t1
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation   | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)      |             | NO   | PRI |         |       | select,insert,update,references |         |
| name  | varchar(256) | utf8mb4_bin | YES  | MUL |         |       | select,insert,update,references |         |
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+

INDEX情報の取得

mysqlshowコマンドはINDEXの情報も取得することが可能です。取得したいINDEXがあるテーブルを指定し、--keys（-k）をつけて実行します。mysqlコマンドではSHOW KEYS FROM <table_name>を実行すると同等の結果を取得できます。

$ mysqlshow -u root -p --socket=/data/sample_mysql/mysql.sock d1 t1 -k
Database: d1  Table: t1
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation   | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)      |             | NO   | PRI |         |       | select,insert,update,references |         |
| name  | varchar(256) | utf8mb4_bin | YES  | MUL |         |       | select,insert,update,references |         |
+-------+--------------+-------------+------+-----+---------+-------+---------------------------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1    | 0          | PRIMARY  | 1            | id          | A         | 1           |          |        |      | BTREE      |         |               | YES     |            |
| t1    | 1          | idx_name | 1            | name        | A         | 1           |          |        | YES  | BTREE      |         |               | YES     |            |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

ワイルドカードの利用

mysqlshowではSQL のワイルドカード文字を利用することが可能です。複数のテーブルを確認したい場合などは「%」や「_」を用いることで曖昧検索を実行することができます。

mysqlshowの実行権限

mysqlshowコマンドはテーブルに対する権限を持っていない場合、情報を取得することができません。適切な権限を持ったユーザーで実行するか、実行するユーザーに権限を与える必要があります。もし実行権限がないテーブルに対して情報を取得しようとした場合、下記のようにエラーとなってしまいます。

$ mysqlshow -u fukamachi -p --socket=/data/sample_mysql/mysql.sock d1 t2 -k
mysqlshow: Cannot list columns in db: d1, table: t2: SELECT command denied to user 'fukamachi'@'localhost' for table 't2'

まとめ

今回は、mysqlshowコマンドを使って各テーブル等の情報を取得するやり方を紹介しました。mysqlコマンドでも同等のことは実行できるので、必要なタイミングは少ないかもしれませんが、もし利用する機会があれば利用してみてください。