MySQL-查询表结构的两种常用方式

常用的两种方式

1 desc tableName

• 当然你也可以用describe tableName来查询,结果与desc是一样的，查询的内容有字段名称、类型、是否可为空、键类型、默认值、是否自增等信息。

1.1样例

mysql> desc users;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name     | varchar(20) | NO   |     | NULL    |                |
| user_password | char(20)    | NO   | UNI | NULL    |                |
| user_power    | tinyint(1)  | YES  |     | 1       |                |
| id_incre      | int(11)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

2 show 命令

show 有如下四种语法

• 语法1

show columns from tableName;

• 语法2
  查询当前的创建语法

show create table tableName;

• 语法3
  比 desc多了权限Privileges 和字段描述Comment 以及 字符校对规则Collation

show full fields from tableName;

• 语法4
  与desc查询内容一致

show fields from tableName;

2.1 样例

mysql> show full fields from users;
+---------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field         | Type        | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id            | int(11)     | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| user_name     | varchar(20) | utf8mb4_0900_ai_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| user_password | char(20)    | utf8mb4_0900_ai_ci | NO   | UNI | NULL    |                | select,insert,update,references |         |
| user_power    | tinyint(1)  | NULL               | YES  |     | 1       |                | select,insert,update,references |         |
| id_incre      | int(11)     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
+---------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)