如何通过查询在MySQL中获得数据库结构?

是否有可能以某种方式获得MySQL数据库的结构，或只是一些简单的查询表?

或者有没有别的方法，我怎么做?

当前回答

在下面的例子中，

Playground是数据库名，equipment是表名

另一种方法是使用SHOW-COLUMNS:5.5(也适用于5.5>)

$ mysql -uroot -p<password> -h<host> -P<port> -e \
    "SHOW COLUMNS FROM playground.equipment"

输出:

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| type  | varchar(50) | YES  |     | NULL    |                |
| quant | int(11)     | YES  |     | NULL    |                |
| color | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

你也可以像下面这样使用mysqlshow-client(也适用于5.5>):

$ mysqlshow -uroot -p<password> -h<host> -P<port> \
    playground equipment

输出:

mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: playground  Table: equipment
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| type  | varchar(50) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
| quant | int(11)     |                   | YES  |     |         |                | select,insert,update,references |         |
| color | varchar(25) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

2019-07-11 10:32:08

其他回答

第一个答案的变化，我觉得很有用

打开你的命令提示符并输入(你不需要登录到你的mysql服务器)

mysqldump -hlocalhost -u<root> -p<password>  <dbname>  --compact --no-data > </path_to_mydump/>mysql.dmp

2013-02-20 16:11:06

在下面的例子中，

Playground是数据库名，equipment是表名

另一种方法是使用SHOW-COLUMNS:5.5(也适用于5.5>)

$ mysql -uroot -p<password> -h<host> -P<port> -e \
    "SHOW COLUMNS FROM playground.equipment"

输出:

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| type  | varchar(50) | YES  |     | NULL    |                |
| quant | int(11)     | YES  |     | NULL    |                |
| color | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

你也可以像下面这样使用mysqlshow-client(也适用于5.5>):

$ mysqlshow -uroot -p<password> -h<host> -P<port> \
    playground equipment

输出:

mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: playground  Table: equipment
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| type  | varchar(50) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
| quant | int(11)     |                   | YES  |     |         |                | select,insert,update,references |         |
| color | varchar(25) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

2019-07-11 10:32:08

用这个: