「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)
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)
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)