让MySQL搜索区分大小写或排序时分大小写方法如下: 1.在SQL中强制 SELECT `field` FROM `table` WHERE BINARY…
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: 图解 inner join、left join、right join、full outer join、union、union all的区别
Ubuntu 16.04 安装 LAMP Install Apache, MySQL, PHP (LAMP) stack on Ubuntu 16.04
PHP5: mysqli 插入, 查询, 更新和删除 Insert Update Delete Using mysqli (CRUD)
命令行导出和导入数据库 How to export and import MySQL database using command line Interface
本文: MySQL: 获取表结构 Get a MySQL table structure with DESCRIBE