大部分开源应用都是用了 MySQL 数据库。了解如何获取 MySQL 数据库的高阶信息,对于调试开源应用程序非常重要。在这篇文章中,我将用 9 个例子来说明如何查看任何 MySQL 数据库的数据库、表、列和索引的信息。
在接下来的 mysqlshow 示例中,你可以通过以下 2 种方式的任何一种来输入密码:
在 mysqlshow
命令后紧跟密码(不要输入任何空格)。在你直接使用 shell 脚本时很有用。
在 mysqlshow
命令后仅仅输入 -p
选项,但不输入密码,之后会提示你输入密码。当你从命令行中交互式地使用 mysqlshow
命令时,推荐使用这种方式。
请将 「 tmppassword 」 替换成你的 MySQL 数据库的 root 用户密码。
# mysqlshow -u root -ptmppassword
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| sugarcrm |
+--------------------+
下边这个示例会列出 sugarcrm 数据库中的所有表。
# mysqlshow -u root -ptmppassword sugarcrm
Database: sugarcrm
+--------------------------------+
| Tables |
+--------------------------------+
| accounts |
| accounts_audit |
| accounts_bugs |
+--------------------------------+
# mysqlshow -v -u root -p sugarcrm
Enter password:
Database: sugarcrm
+--------------------------------+----------+
| Tables | Columns |
+--------------------------------+----------+
| accounts | 33 |
| accounts_audit | 10 |
| accounts_bugs | 5 |
+-------------------------------------------+
请记住下边这个命令要输入 2 个 -v
。
# 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 |
+---------------------------------------------------------+
这个示例中,显示了 sugarcrm 库中的 accounts 表的所有可用的列名和该列其他信息。
# 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 | |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
在这个示例中,列出了 accounts 表的 id 列的信息。
# 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 | |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
# mysqlshow -i -u root -ptmppassword sugarcrm accounts
这结汇列出关于 accounts 表的以下所有信息。
请记住索引信息会显示在输出的末尾,列信息的下方。
# 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 | |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
可以通过提供非法的列名来骗过 mysqlshow
。因为 invalide_col_name
在 accounts
表中不存在,所以下边这个命令仅会输出 accounts
表的索引信息。
# 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,笔者出于分享的目的翻译了该文章。如有侵权,请联系本人删除本文章。
暂时没有留言