「MySQL 8.0.29」showコマンドでデータベース、テーブル、カラムの情報を確認する
環境
Ubuntu 22.04 LTS
MySQL 8.0.29
1.DB一覧を表示します
書式
SHOW DATABASES [like_or_where]
操作例
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.05 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.05 sec)
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.05 sec)
2.テーブル一覧を表示します
書式
SHOW TABLES FROM データベース名 [like_or_where]
操作例
mysql> SHOW TABLES FROM information_schema LIKE "%INDEX%";
+----------------------------------------+
| Tables_in_information_schema (%INDEX%) |
+----------------------------------------+
| INNODB_CACHED_INDEXES |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
+----------------------------------------+
6 rows in set (0.00 sec)
mysql> SHOW TABLES FROM information_schema LIKE "%INDEX%";
+----------------------------------------+
| Tables_in_information_schema (%INDEX%) |
+----------------------------------------+
| INNODB_CACHED_INDEXES |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
+----------------------------------------+
6 rows in set (0.00 sec)
mysql> SHOW TABLES FROM information_schema LIKE "%INDEX%"; +----------------------------------------+ | Tables_in_information_schema (%INDEX%) | +----------------------------------------+ | INNODB_CACHED_INDEXES | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX_RESET | | INNODB_FT_INDEX_CACHE | | INNODB_FT_INDEX_TABLE | | INNODB_INDEXES | +----------------------------------------+ 6 rows in set (0.00 sec)
3.テーブルのカラムを表示します
書式
SHOW COLUMNS FROM テーブル名 FROM データベース名 [like_or_where]
操作例
mysql> SHOW COLUMNS FROM slow_log FROM mysql;
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
| start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6) |
| user_host | mediumtext | NO | | NULL | |
| query_time | time(6) | NO | | NULL | |
| lock_time | time(6) | NO | | NULL | |
| rows_sent | int | NO | | NULL | |
| rows_examined | int | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int | NO | | NULL | |
| insert_id | int | NO | | NULL | |
| server_id | int unsigned | NO | | NULL | |
| sql_text | mediumblob | NO | | NULL | |
| thread_id | bigint unsigned | NO | | NULL | |
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
12 rows in set (0.05 sec)
mysql> SHOW COLUMNS FROM slow_log FROM mysql;
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
| start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6) |
| user_host | mediumtext | NO | | NULL | |
| query_time | time(6) | NO | | NULL | |
| lock_time | time(6) | NO | | NULL | |
| rows_sent | int | NO | | NULL | |
| rows_examined | int | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int | NO | | NULL | |
| insert_id | int | NO | | NULL | |
| server_id | int unsigned | NO | | NULL | |
| sql_text | mediumblob | NO | | NULL | |
| thread_id | bigint unsigned | NO | | NULL | |
+----------------+-----------------+------+-----+----------------------+--------------------------------------------------+
12 rows in set (0.05 sec)
mysql> SHOW COLUMNS FROM slow_log FROM mysql; +----------------+-----------------+------+-----+----------------------+--------------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-----------------+------+-----+----------------------+--------------------------------------------------+ | start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6) | | user_host | mediumtext | NO | | NULL | | | query_time | time(6) | NO | | NULL | | | lock_time | time(6) | NO | | NULL | | | rows_sent | int | NO | | NULL | | | rows_examined | int | NO | | NULL | | | db | varchar(512) | NO | | NULL | | | last_insert_id | int | NO | | NULL | | | insert_id | int | NO | | NULL | | | server_id | int unsigned | NO | | NULL | | | sql_text | mediumblob | NO | | NULL | | | thread_id | bigint unsigned | NO | | NULL | | +----------------+-----------------+------+-----+----------------------+--------------------------------------------------+ 12 rows in set (0.05 sec)