How to query field comments in MySQL? Check out and compare 5 methods!

How to query field comments in MySQL? The following article will review and compare 5 methods for querying MySQL field comments, and introduce how to modify table comments and field comments. I hope it will be helpful to you.

In many scenarios, we need to view table comments in MySQL, or comments on all fields under a certain table, so this article will inventory and compare several query comments. way. [Related recommendations: mysql video tutorial]

Create a test database

Before we start, let’s create a database for the following demonstration.

-- 如果存在就先删除数据库
drop database if exists test2022;
-- 创建数据库
create database test2022;
-- 切换数据库
use test2022;
-- 创建表和字段（以及相应的注释）
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `sn` varchar(50) DEFAULT NULL COMMENT '学号',
  `username` varchar(250) NOT NULL COMMENT '学生名称',
  `mail` varchar(250) DEFAULT NULL COMMENT '邮箱',
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) comment='学生表' ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Query all table comments

Use the following SQL to query the comments of all tables under a database:

SELECT table_name 表名, table_comment 表说明
  FROM information_schema.TABLES 
  WHERE table_schema='数据库名'
  ORDER BY table_name

Case: Query the comments of all tables in the test2022 database:

SELECT table_name 表名, table_comment 表说明
  FROM information_schema.TABLES 
  WHERE table_schema='test2022'
  ORDER BY table_name

The execution result is shown in the figure below:

Query all field comments

Field comment query method 1

The query syntax is as follows:

show full columns from 表名;

Case: Query the comment information of all fields in the student table:

show full columns from student;

The execution results are as shown in the figure below:

Field comment query method 2

The query syntax is as follows:

select COLUMN_NAME 字段名,column_comment 字段说明,column_type 字段类型,
  column_key 约束 from information_schema.columns 
  where table_schema='数据库名' and table_name='表名';

Case: Query the comment information of all fields in the student table:

select COLUMN_NAME 字段名,column_comment 字段说明,column_type 字段类型,
  column_key 约束 from information_schema.columns 
  where table_schema='test2022' and table_name='student';

The execution result is shown in the figure below:

Field comment query mode 3

DDL (data definition) of the query table Language) You can also see the annotation content of the field. The executed SQL syntax is as follows:

show create table 表名;

Case: Query the annotation information of all fields in the student table:

show create table student;

The execution result is as shown below:

Field comment query method 4

If you are using the Navicat tool, you can right-click on the table and click Design. You can view field comments on the design page, as shown in the following figure:

#But this operation is a bit dangerous, be careful if your hands shake and the table structure is changed incorrectly.

Field comment query method 5

View the DDL statement of the table in Navicat and you can also see the field comments. Select the table and click the "Show right window" option in the lower right foot. , and then click DDL to display it. The specific operation steps are as shown below:

Modify table comments and field comments

Modify table Comment

Modify the syntax of the table comment:

alter table 表名 comment ='修改后的表注释';

Case: Modify the table comment of student:

alter table student  comment ='学生表 V2';

The execution result is shown in the figure below:

Modify field comments

The syntax of modifying table comments:

alter table 表名 modify column 字段名 int comment '注释信息';

Case: Modify the comment of name in the student table:

alter table student modify column username int comment '学生姓名 V2';

The execution results are shown in the figure below:

Summary

This article introduces the SQL for viewing table comments, and modifying table and field comments SQL, and also introduces 5 ways to view field comments: 3 command line operation methods to view, and two Navicat-based operation methods to view field comments. Among them, it is recommended to use SQL: "show full columns from table name" to view field comments. This Querying SQL is simple and there is no need to worry about changing the table structure by mistake.

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of How to query field comments in MySQL? Check out and compare 5 methods!. For more information, please follow other related articles on the PHP Chinese website!