MySQL: 获取表结构 Get a MySQL table structure with DESCRIBE

Example table

The example table used in this post was created with the following SQL:

CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(100) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`visible` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`product_id`),
UNIQUE KEY `url` (`url`),
KEY `visible` (`visible`)
)

 

Using DESCRIBE

The SQL query to get the table structure is:

DESCRIBE products;

 

You can run this from the MySQL CLI; phpMyAdmin; or using a programming language like PHP and then using the functions to retrieve each row from the query.

The resulting data from the MySQL CLI looks like this for the example table above:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| product_id  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| url         | varchar(100)        | NO   | UNI | NULL    |                |
| name        | varchar(50)         | NO   |     | NULL    |                |
| description | varchar(255)        | NO   |     | NULL    |                |
| price       | decimal(10,2)       | NO   |     | NULL    |                |
| visible     | tinyint(1) unsigned | NO   | MUL | 1       |                |
+-------------+---------------------+------+-----+---------+----------------+

 

Using PHP

If you were using PHP, for example, you could do something like this:

$res = mysql_query('DESCRIBE products');
while($row = mysql_fetch_array($res)) {
    echo "{$row['Field']} - {$row['Type']}\n";
}

 

The output using the example table would be this:

product_id - int(10) unsigned
url - varchar(100)
name - varchar(50)
description - varchar(255)
price - decimal(10,2)
visible - tinyint(1) unsigned

 

Querying the INFORMATION_SCHEMA

My next MySQL post will look at how to do the same using the INFORMATION_SCHEMA, which provides more information than DESCRIBE, although I have found querying the INFORMATION_SCHEMA can run a little slowly sometimes myself.

 

原文: http://www.electrictoolbox.com/mysql-table-structure-describe/

本文: MySQL: 获取表结构 Get a MySQL table structure with DESCRIBE