SHOW構文：テーブル情報を表示する

SHOW構文を知ろう！

SHOW構文

SHOWはデータベース、テーブル、フィールド、サーバについての情報を与える。LIKEが使用された場合、文字列は通常のSQLワイルドカード (% と _) です。上記の使用例のように、"テーブル名 FROM データベース名"の代わりに、"データベース名.テーブル名"が使用できます。

SHOW 構文

SHOW DATABASES [LIKE 文字列]
or SHOW [OPEN] TABLES [FROM データベース名] [LIKE 文字列]
or SHOW [FULL] COLUMNS FROM テーブル名 [FROM データベース名] [LIKE 文字列]
or SHOW INDEX FROM テーブル名 [FROM データベース名]
or SHOW TABLE STATUS [FROM db_name] [LIKE 文字列]
or SHOW STATUS [LIKE 文字列]
or SHOW VARIABLES [LIKE 文字列]
or SHOW LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR ユーザ名
or SHOW CREATE TABLE テーブル名
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS

SHOW構文の使用例

SHOW INDEX FROM mydb.mytable;

構文のキーワード

SHOW構文のキーワード

SHOW DATABASES

データベース名の一覧を表示します。

SHOW DATABASES;
+----------+
| Database |
+----------+
| mydb |
| mysql |
| test |
+----------+

SHOW TABLES

カレントデータベースのテーブル名を一覧表示します。

SHOW TABLES;
+----------------+
| Tables_in_MYDB |
+----------------+
| customer |
| goods |
| purchase |
| purchase_goods |
+----------------+

SHOW FIELDS / SHOW COLUMNS

SHOW COLUMNSは指定されたテーブルのフィールドを表示します。FULLオプションを指定した場合、各フィールドへの権限も表示されます。

SHOW COLUMNS FROM customer;
+-------+-----------------------+------+-----+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+
| id_c | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| sei | varchar(20) | YES | | NULL | |
| mei | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | tinyint(1) unsigned | YES | | NULL | |
+-------+-----------------------+------+-----+

SHOW KEYS / SHOW INDEX

SHOW INDEXはインデックス情報を表示します。

SHOW INDEX FROM customer;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| CUSTOMER | 0 | PRIMARY | 1 | id_c | A | 5 | NULL | NULL | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+

以下の項目が返ります。

SHOW VARIABLES

MySQLシステム変数のいくつかの値を示す。もし標準値が適さないなら、ほとんどの変数をmysqld起動時に命令文ラインのオプションとして与えることにより、変更できる。

SHOW VARIABLES [LIKE wild]

SHOW VARIABLESはMySQLシステム変数のいくつかの値を示します。 mysqlshow variables 命令文でも同じ情報が得られます。もし標準値が適さないなら、ほとんどの変数をmysqld起動時に命令文ラインのオプションとして与えることにより、変更できます。

buffer size, buffer length, stack size は byte単位で与えます。これらの値の後ろにKやMを追加すると、キロバイト、メガバイトになります。例えば、16Mは16メガバイトを示します。大文字小文字の区別はなく、16Mと16mは同じ意味になります。

SHOW PROCESSLIST

そのスレッドが走っているかを表示する。もし process 特権があるなら、全てのスレッドがみれる。しかし特権がないなら、自分のスレッドしか見れない。

SHOW PROCESSLIST はどのスレッドが走っているかを表示します。 mysqlshow processlist 命令文でも同じ情報が得られます。もし process 権限があるなら、全てのスレッドがみれます。しかし権限がないなら、自分のスレッドしか見れません。 「4.5.5 KILL 構文」節参照. FULL オプションを使用しない場合、各クエリの最初の100文字だけが表示されます。

This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the Process_priv privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users).

Some frequently states in mysqladmin processlist

• Checking table The thread doing an [automatic ?] checking of the table.
• Closing tables Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should check that you don't have a full disk or that the disk is not in very heavy use.
• Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory based temporary table to a disk based one to save memory.
• Creating tmp table The thread is creating a temporary table to hold a part of the result for the query.
• deleting from main table When executing the first part of a multi-table delete and we are only deleting from the first table.
• deleting from reference tables When executing the second part of a multi-table delete and we are deleting the matched rows from the other tables.
• Flushing tables The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
• Killed Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it may still take a short time for the thread to die. If the thread is locked by some other thread, the kill will take affect as soon as the other thread releases it's lock.
• Sending data The thread is processing rows for a SELECT statement and is also sending data to the client.
• Sorting for group The thread is doing a sort to satsify a GROUP BY.
• Sorting for order The thread is doing a sort to satsify a ORDER BY.
• Opening tables This simply means that the thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example an ALTER TABLE or a LOCK TABLE can prevent opening a table until the command is finished.
• Removing duplicates The query was using SELECT DISTINCT in such a way that MySQL couldn't optimize that distinct away at an early stage. Because of this MySQL has to do an extra stage to remove all duplicated rows before sending the result to the client.
• Reopen table The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table and is now trying to reopen it.
• Repair by sorting The repair code is using sorting to create indexes.
• Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
• Searching rows for update The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
• Sleeping The thread is wating for the client to send a new command to it.
• System lock The thread is waiting for getting to get a external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-locking option.
• Upgrading lock The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
• Updating The thread is searching for rows to update and updating them.
• User Lock The thread is waiting on a GET_LOCK().
• Waiting for tables The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. To be able to reopen the table it must however wait until all other threads have closed the table in question. This notification happens if another thread has used FLUSH TABLES or one of the following commands on the table in question: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.
• waiting for handler insert The INSERT DELAYED handler has processed all inserts and are waiting to get new ones.

Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated.

There are some other states that are not mentioned above, but most of these are only useful to find bugs in mysqld.

SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (バージョン 3.23 の新機能) は SHOWSTATUSのようですが、それぞれのテーブルについてより多くの情報を提供します。 mysqlshow --status db_name 命令文を実行しても同じものが得られます。以下の項目が返ってきます：

SHOW STATUS

サーバからのステータス情報を表示します。

ステータス変数

コメント

• もし Opened_tables が大きければ、 table_cache 変数が小さすぎるのでしょう。
• もし key_reads が大きければ、 key_cache が少なすぎるでしょう。キャッシュヒットレートは key_reads/key_read_requests で計算できます。
• もし Handler_read_rnd が大きければ、 MySQL にテーブルをスキャンさせるような多くのクエリや、 キーを使用しない JOIN がもてる。

SHOW LOGS

SHOW LOGS shows you status information about existing log files. It currently only displays information about Berkeley DB log files.

• File shows the full path to the log file
• Type shows the type of the log file (BDB for Berkeley DB log files)
• Status shows the status of the log file (FREE if the file can be removed, or IN USE if the file is needed by the transaction subsystem)

SHOW GRANTS

SHOW GRANTS FOR user はユーザの許可を複製するために発行する必要がある grant 命令文をリストします。

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

SHOW CREATE TABLE will quote table and column names according to SQL_QUOTE_SHOW_CREATE option.

DESCRIBE構文　Oracle互換

構文

{DESCRIBE | DESC} テーブル名 {フィールド名| 文字列}

DESCRIBE はフィールドについての情報を与えます。