大部分开源应用都是用了 MySQL 数据库。了解如何获取 MySQL 数据库的高阶信息,对于调试开源应用程序非常重要。在这篇文章中,我将用 9 个例子来说明如何查看任何 MySQL 数据库的数据库、表、列和索引的信息。
在接下来的 mysqlshow 示例中,你可以通过以下 2 种方式的任何一种来输入密码:
1. 列出所有可用的数据库
请将 「 tmppassword 」 替换成你的 MySQL 数据库的 root 用户密码。
1 2 3 4 5 6 7 8 9
| # mysqlshow -u root -ptmppassword
+--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | sugarcrm | +--------------------+
|
2. 列出数据库中的所有表
下边这个示例会列出 sugarcrm 数据库中的所有表。
1 2 3 4 5 6 7 8 9 10
| # mysqlshow -u root -ptmppassword sugarcrm
Database: sugarcrm +--------------------------------+ | Tables | +--------------------------------+ | accounts | | accounts_audit | | accounts_bugs | +--------------------------------+
|
3. 列出数据库中的所有表并附带每张表的列数
1 2 3 4 5 6 7 8 9 10 11
| # mysqlshow -v -u root -p sugarcrm
Enter password: Database: sugarcrm +--------------------------------+----------+ | Tables | Columns | +--------------------------------+----------+ | accounts | 33 | | accounts_audit | 10 | | accounts_bugs | 5 | +-------------------------------------------+
|
4. 列出数据库中的所有表并附带每张表的列数和行数
请记住下边这个命令要输入 2 个 -v
。
1 2 3 4 5 6 7 8 9 10 11
| # mysqlshow -v -v -u root -p sugarcrm
Enter password: Database: sugarcrm +--------------------------------+----------+------------+ | Tables | Columns | Total Rows | +--------------------------------+----------+------------+ | accounts | 33 | 252 | | accounts_audit | 10 | 63 | | accounts_bugs | 5 | 0 | +---------------------------------------------------------+
|
5.列出某表的所有列
这个示例中,显示了 sugarcrm 库中的 accounts 表的所有可用的列名和该列其他信息。
1 2 3 4 5 6 7 8
| # mysqlshow -u root -ptmppassword sugarcrm accounts id
Database: sugarcrm Table: accounts Wildcard: id +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | char(36) | utf8_general_ci | NO | PRI | | | select,insert,update,references | | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
|
6. 列出某表的某列的详细信息
在这个示例中,列出了 accounts 表的 id 列的信息。
1 2 3 4 5 6 7 8
| # mysqlshow -u root -ptmppassword sugarcrm accounts id
Database: sugarcrm Table: accounts Wildcard: id +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | char(36) | utf8_general_ci | NO | PRI | | | select,insert,update,references | | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
|
7. 显示某张表的所有的元信息
1
| # mysqlshow -i -u root -ptmppassword sugarcrm accounts
|
这结汇列出关于 accounts 表的以下所有信息。
- 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
8. 列出某张表的列和索引信息
请记住索引信息会显示在输出的末尾,列信息的下方。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| # mysqlshow -k -u root -ptmppassword sugarcrm accounts
Database: sugarcrm Table: accounts +-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | char(36) | utf8_general_ci | NO | PRI | | | select,insert,update,references | | | name | varchar(150) | utf8_general_ci | YES | | | | select,insert,update,references | | +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | accounts | 0 | PRIMARY | 1 | id | A | 252 | | | | BTREE | | | accounts | 1 | idx_accnt_id_del | 1 | id | A | | | | | BTREE | | +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
|
9. 显示某张表的索引但是不包含列信息
可以通过提供非法的列名来骗过 mysqlshow
。因为 invalide_col_name
在 accounts
表中不存在,所以下边这个命令仅会输出 accounts
表的索引信息。
1 2 3 4 5 6 7 8 9 10 11 12 13
| # mysqlshow -k -u root -ptmppassword sugarcrm accounts invalid_col_name
Database: sugarcrm Table: accounts Wildcard: invalid_col_name +-------+------+-----------+------+-----+---------+-------+------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+------+-----------+------+-----+---------+-------+------------+---------+ +-------+------+-----------+------+-----+---------+-------+------------+---------+ +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | accounts | 0 | PRIMARY | 1 | id | A | 254 | | | | BTREE | | | accounts | 1 | idx_accnt_id_del | 1 | id | A | | | | | BTREE | | +----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
|
说明
本篇文章翻译自 mysqlshow – Get Quick Info On MySQL DB, Table, Column and Index,笔者出于分享的目的翻译了该文章。如有侵权,请联系本人删除本文章。