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

最新推荐文章于 2025-02-14 21:47:29 发布

双木青橙

最新推荐文章于 2025-02-14 21:47:29 发布

阅读量912

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/hl1hl/article/details/108313103

版权

MySQL 专栏收录该内容

4 篇文章

订阅专栏

常用的两种方式

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)