MySQL之資料庫物件查看工具mysqlshow

標籤：mysql mysqlshow

    mysqlshow：資料庫物件查看工具，用來快速尋找存在哪些資料庫、資料庫中的表、表中的列或索引。

選項：--count    顯示資料庫和表的統計資訊

      -k         顯示指定的表中的索引

      -i         顯示表的狀態資訊

不帶任何參數顯示所有資料庫

[[email protected] mysql-5.6.30]# mysqlshow

+--------------------+

|     Databases      |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

不指定資料庫，顯示資料庫和表的統計資訊

[[email protected] mysql-5.6.30]# mysqlshow --count

+--------------------+--------+--------------+

|     Databases      | Tables |  Total Rows  |

+--------------------+--------+--------------+

| information_schema |     59 |        13157 |

| mysql              |     28 |         2474 |

| performance_schema |     52 |        16615 |

| test               |      2 |           24 |

+--------------------+--------+--------------+

4 rows in set.

指定資料庫，顯示統計資訊

[[email protected] mysql-5.6.30]# mysqlshow -uroot test --count

Database: test

+----------+----------+------------+

|  Tables  | Columns  | Total Rows |

+----------+----------+------------+

| backt118 |        4 |         12 |

| t118     |        4 |         12 |

+----------+----------+------------+

2 rows in set.

指定資料庫和表，顯示統計資訊

[[email protected] mysql-5.6.30]# mysqlshow -uroot test t118 --count

Database: test  Table: t118  Rows: 12

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field   | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| name    | char(10) | utf8_general_ci | YES  | MUL |         |       | select,insert,update,references |         |

| age     | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

| chushen | date     |                 | YES  |     |         |       | select,insert,update,references |         |

| shengao | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

顯示指定表所有的索引，前部分是表的結構，後部分是表的索引資訊

[[email protected] mysql-5.6.30]# mysqlshow -uroot test t118 -k

Database: test  Table: t118

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field   | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| name    | char(10) | utf8_general_ci | YES  | MUL |         |       | select,insert,update,references |         |

| age     | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

| chushen | date     |                 | YES  |     |         |       | select,insert,update,references |         |

| shengao | int(3)   |                 | YES  |     |         |       | 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 |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t118  | 1          | index_name | 1            | name        | A         | 12          |          |        | YES  | BTREE      |         |               |

| t118  | 1          | index_ccc  | 1            | name        | A         | 12          |          |        | YES  | BTREE      |         |               |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

顯示表的狀態資訊

[[email protected] mysql-5.6.30]# mysqlshow -i -uroot test t118

Database: test  Wildcard: t118

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

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

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| t118 | InnoDB | 10      | Compact    | 12   | 1365           | 16384       | 0               | 0            | 0         |                | 2017-04-29 22:33:47 |             |            | utf8_general_ci |          |                |         |

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

本文出自 “一萬年太久，只爭朝夕” 部落格，請務必保留此出處http://zengwj1949.blog.51cto.com/10747365/1920764

MySQL之資料庫物件查看工具mysqlshow