MySQL 如何通过查询获取MySQL中的数据库结构

How to get database structure in MySQL via query

提问by Jakub Arnold

Is it possible to somehow get structure of MySQL database, or just some table with simple query?

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

Or is there another way, how can I do it?

或者有另一种方法，我该怎么做？

回答by Bill the Lizard

I think that what you're after is DESCRIBE

我认为你所追求的是 DESCRIBE

DESCRIBE table;

You can also use SHOW TABLES

你也可以使用 SHOW TABLES

SHOW TABLES;

to get a list of the tables in your database.

获取数据库中表的列表。

回答by Eemeli Kantola

To get the whole database structure as a set of CREATE TABLEstatements, use mysqldump:

要将整个数据库结构作为一组CREATE TABLE语句获取，请使用mysqldump：

mysqldump database_name --compact --no-data

For single tables, add the table name after db name in mysqldump. You get the same results with SQL and SHOW CREATE TABLE:

对于单表，在mysqldump的db name后面加上表名。使用 SQL 和SHOW CREATE TABLE可以获得相同的结果：

SHOW CREATE TABLE table;

Or DESCRIBEif you prefer a column listing:

或者DESCRIBE如果你喜欢列清单：

DESCRIBE table;

回答by Zenshai

Take a look at the INFORMATION_SCHEMA.TABLEStable. It contains metadata about all your tables.

看一看INFORMATION_SCHEMA。TABLES桌子。它包含有关所有表的元数据。

Example:

例子：

SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE TABLE_NAME LIKE 'table1'

The advantage of this over other methods is that you can easily use queries like the one above as subqueries in your other queries.

与其他方法相比，这种方法的优点是您可以轻松地将上述查询用作其他查询中的子查询。

回答by duckyflip

using this:

使用这个：

SHOW CREATE TABLE `users`;

will give you the DDLfor that table

将为您提供该表的DDL

DESCRIBE `users`

will list the columns in that table

将列出该表中的列

回答by ashish

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products'; 

where Table_schemais database name

Table_schema数据库名称在哪里

回答by soulmerge

That's the SHOW CREATE TABLEquery. You can query the SCHEMA TABLES, too.

这就是SHOW CREATE TABLE查询。您也可以查询SCHEMA TABLES。

SHOW CREATE TABLE YourTableName;

回答by LGG

A variation of the first answer that I found useful

我发现有用的第一个答案的变体

Open your command prompt and enter (you dont have to be logged into your mysql server)

打开您的命令提示符并输入（您不必登录到您的 mysql 服务器）

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

回答by Raja Nagendra Kumar

SELECT COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA='bodb' AND TABLE_NAME='abc';

COLUMN_NAME从 中选择INFORMATION_SCHEMA。COLUMNSWHERE TABLE_SCHEMA='bodb' AND TABLE_NAME='abc';

works for getting all column names

适用于获取所有列名

回答by Shudipta Sharma

In the following example,

在下面的例子中，

playgroundis the database name and equipmentis the table name

playground是数据库名，equipment是表名

Another way is using SHOW-COLUMNS:5.5(available also for 5.5>)

另一种方法是使用SHOW-COLUMNS:5.5（也可用于5.5>）

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

And the output:

和输出：

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

One can also use mysqlshow-client(also available for 5.5>) like following:

还可以使用mysqlshow-client（也可用于5.5>），如下所示：

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

And the output:

和输出：

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

回答by Sh4dy

Nowadays, people use DESCinstead of DESCRIPTION. For example:- DESC users;

如今，人们使用DESC代替DESCRIPTION. 例如：- DESC users;