mysql查看表结构的几种方式

在实际的开发中，我们肯定经常要查看表结构的，特别是遇到自己不是最初开发的项目的时候，通过表结构，大概就能看出表里存什么数据，每个字段代表什么意思。实际上有很多查看表结构的方式，下面就拿rails里面的表schema_migrations介绍一下我自己常用的命令：

desc table_name;

mysql> desc schema_migrations;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| version | varchar(255) | NO   | PRI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

它是我们最常用的命令，这个命令也是describe schema_migrations的缩写。

show fields from table_name;

mysql> show fields from schema_migrations;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| version | varchar(255) | NO   | PRI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

和describe命令达到的效果一样。

在它的基础上就有一个更强大的命令：

show full fields from table_name

mysql> show full fields from schema_migrations;
+---------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field   | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+---------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| version | varchar(255) | utf8_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
+---------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.01 sec)

每个字段的详细信息也就出来了，但是遗憾的是索引没有处理，说到查看索引，很明显就会想到下面的命令：

show index from table_name

mysql> show index from schema_migrations;
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| schema_migrations |          0 | unique_schema_migrations |            1 | version     | A         |          21 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

这个命令和 show keys from table_name得到的效果一样。

实际上有个终极命令，既可以看到本身的字段又可以看到索引等其他信息，还能得到完整的创建表结构的命令，那就是：

show create table table_name

mysql> show create table schema_migrations;
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                   |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| schema_migrations | CREATE TABLE `schema_migrations` (
  `version` varchar(255) NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过命令执行的结果可以看到创建表的sql语句，字段名，索引，搜索引擎，编码方式等信息，是不是很强大。