blocks|key|1550419|text|我想你要找的是DESCRIBE|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1550420|DESCRIBE+table;|code-block|syntax|javascript|1550421|您也可以使用SHOW+TABLES|1550422|SHOW+TABLES;|1550423|以获取数据库中的表的列表。|1550424|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/4.1/en/describe.html|1|https://dev.mysql.com/doc/refman/5.0/en/show-tables.html^0|7|8|7|8|0|0|0|6|B|6|B|1|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]]|E|$]]|$1|F|3|G|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@$9|1A|A|1B|1|1C]]|E|$]]|$1|M|3|N|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]]]

I think that what you're after is <a href="http://dev.mysql.com/doc/refman/4.1/en/describe.html" rel="noreferrer"><code>DESCRIBE</code></a>

<pre><code>DESCRIBE table;
</code></pre>

You can also use <a href="https://dev.mysql.com/doc/refman/5.0/en/show-tables.html" rel="noreferrer"><code>SHOW TABLES</code></a>

<pre><code>SHOW TABLES;
</code></pre>

to get a list of the tables in your database.

blocks|key|1335063|text|要以一组CREATE+TABLE语句的形式获取整个数据库结构，请使用mysqldump|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1335064|mysqldump+database_name+--compact+--no-data|code-block|syntax|javascript|1335065|对于单个表，在mysqldump中的db+name后面添加表名。使用SQL和SHOW+CREATE+TABLE可以得到相同的结果。|1335066|SHOW+CREATE+TABLE+table;|1335067|或者，如果您更喜欢列列表，则为DESCRIBE：|1335068|DESCRIBE+table;|1335069|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/create-table.html|1|http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html|2|http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html|3|http://dev.mysql.com/doc/refman/5.1/en/describe.html^0|4|C|0|Y|9|1|0|0|12|H|2|0|0|F|8|3|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]|$A|18|B|19|1|1A]]|C|$]]|$1|D|3|E|5|F|7|1B|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]]|C|$]]|$1|K|3|L|5|F|7|1G|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|1H|8|@]|9|@$A|1I|B|1J|1|1K]]|C|$]]|$1|O|3|P|5|F|7|1L|8|@]|9|@]|C|$G|H]]|$1|Q|3|-4|5|6|7|1M|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]|10|$5|T|U|V|C|$W|11]]|12|$5|T|U|V|C|$W|13]]]]

To get the whole database structure as a set of <a href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html" rel="noreferrer">CREATE TABLE</a> statements, use <a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html" rel="noreferrer">mysqldump</a>:

<pre><code>mysqldump database_name --compact --no-data
</code></pre>

For single tables, add the table name after db name in mysqldump. You get the same results with SQL and <a href="http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html" rel="noreferrer">SHOW CREATE TABLE</a>:

<pre><code>SHOW CREATE TABLE table;
</code></pre>

Or <a href="http://dev.mysql.com/doc/refman/5.1/en/describe.html" rel="noreferrer">DESCRIBE</a> if you prefer a column listing:

<pre><code>DESCRIBE table;
</code></pre>

blocks|key|1335035|text|看看INFORMATION_SCHEMA.TABLES表。它包含所有表的元数据。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1335036|示例：|1335037|SELECT+*+FROM+`INFORMATION_SCHEMA`.`TABLES`
WHERE+TABLE_NAME+LIKE+'table1'|code-block|syntax|javascript|1335038|与其他方法相比，这种方法的优点是，您可以很容易地在其他查询中使用像上面这样的查询作为子查询。|1335039|entityMap^0|2|I|L|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

Take a look at the <code>INFORMATION_SCHEMA</code>.<code>TABLES</code> table. It contains metadata about all your tables.

Example:

<pre><code>SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE TABLE_NAME LIKE 'table1'
</code></pre>

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

blocks|key|1334877|text|使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1334878|SHOW+CREATE+TABLE+`users`;|code-block|syntax|javascript|1334879|将为您提供该表的DDL|offset|length|1334880|DESCRIBE+`users`|1334881|将列出该表中的列|1334882|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Data_Definition_Language^0|0|0|8|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

using this:

<pre><code>SHOW CREATE TABLE `users`;
</code></pre>

will give you the <a href="http://en.wikipedia.org/wiki/Data_Definition_Language" rel="noreferrer">DDL</a> for that table

<pre><code>DESCRIBE `users`
</code></pre>

will list the columns in that table

blocks|key|1335121|text|SELECT+*
FROM+INFORMATION_SCHEMA.COLUMNS+
WHERE+TABLE_SCHEMA+=+'test'+AND+TABLE_NAME+='products';+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1335122|其中Table_schema是数据库名称|unstyled|offset|length|style|CODE|1335123|entityMap^0|0|2|C|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products'; 
</code></pre>

where <code>Table_schema</code> is database name

blocks|key|1550475|text|这就是SHOW+CREATE+TABLE查询。您也可以查询SCHEMA+TABLES。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1550476|SHOW+CREATE+TABLE+YourTableName;|code-block|syntax|javascript|1550477|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html|1|http://dev.mysql.com/doc/refman/5.0/en/information-schema.html^0|3|H|0|T|D|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

That's the <a href="http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html" rel="noreferrer">SHOW CREATE TABLE</a> query. You can query the <a href="http://dev.mysql.com/doc/refman/5.0/en/information-schema.html" rel="noreferrer">SCHEMA TABLES</a>, too.

<pre><code>SHOW CREATE TABLE YourTableName;
</code></pre>

blocks|key|1550604|text|第一个答案的变体，我觉得很有用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1550605|打开命令提示符并输入(您不必登录到mysql服务器)|1550606|mysqldump+-hlocalhost+-u<root>+-p<password>++<dbname>++--compact+--no-data+>+</path_to_mydump/>mysql.dmp|code-block|syntax|javascript|1550607|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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)

<pre><code>mysqldump -hlocalhost -u&lt;root&gt; -p&lt;password&gt; &lt;dbname&gt; --compact --no-data &gt; &lt;/path_to_mydump/&gt;mysql.dmp
</code></pre>

blocks|key|1335158|text|从bodb中选择COLUMN_NAME。COLUMNS+WHERE+TABLE_SCHEMA='bodb‘AND+TABLE_NAME='abc'；|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1335159|用于获取所有列名|1335160|entityMap^0|8|B|K|7|Y|C|1L|A|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

SELECT <code>COLUMN_NAME</code> 
FROM <code>INFORMATION_SCHEMA</code>.<code>COLUMNS</code> 
WHERE <code>TABLE_SCHEMA</code>='bodb' 
 AND <code>TABLE_NAME</code>='abc';

works for getting all column names

blocks|key|1550685|text|现在，人们不再使用DESCRIPTION，而是使用DESC。例如：-+DESC+users;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1550686|entityMap^0|9|B|P|4|Z|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

Nowadays, people use <code>DESC</code> instead of <code>DESCRIPTION</code>. For example:-
<code>DESC users;</code>

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

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

How to get database structure in MySQL via query

SQL数据库

数据库

服务器

MySQL

有没有可能以某种方式获得MySQL数据库的结构，或者只是一些简单查询的表？或者还有其他方法，我该怎么做呢？

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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