In many scenarios, we need to view the table comments in MySQL or the comments of all fields under a table, so this article will count and compare several ways to query comments.

Create test database

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

-- If it exists, delete the database first
drop database if exists test2022;
-- Create database
create database test2022;
-- Switch database
use test2022;
-- Create tables and fields (and corresponding comments)
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
  `sn` varchar(50) DEFAULT NULL COMMENT 'Student number',
  `username` varchar(250) NOT NULL COMMENT 'Student name',
  `mail` varchar(250) DEFAULT NULL COMMENT 'mailbox',
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) comment='Student list' ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Query all table comments

The following SQL can be used to query the comments of all tables under a database:

SELECT table_name Table name, table_comment Table description
  FROM information_schema.TABLES 
  WHERE table_schema='Database name'
  ORDER BY table_name

Case: query all table annotations in test2022 database:

SELECT table_name Table name, table_comment Table description
  FROM information_schema.TABLES 
  WHERE table_schema='test2022'
  ORDER BY table_name

The execution results are shown in the following figure:

Query all field comments

Field annotation query method 1

The query syntax is as follows:

show full columns from Table name;

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

show full columns from student;

The execution results are shown in the figure below:

Field annotation query method 2

The query syntax is as follows:

select COLUMN_NAME Field name,column_comment Field description,column_type Field type,
  column_key constraint from information_schema.columns 
  where table_schema='Database name' and table_name='Table name'; 

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

select COLUMN_NAME Field name,column_comment Field description,column_type Field type,
  column_key constraint from information_schema.columns 
  where table_schema='test2022' and table_name='student';

The execution results are shown in the following figure:

Field annotation query method 3

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

show create table Table name;

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

show create table student;

The execution results are shown in the following figure:

Field annotation query method 4

If Navicat is used, you can right-click the table and click design to view the field notes on the design page, as shown in the following figure:

But this operation is a little dangerous. Be careful to shake your hand and change the wrong watch structure.

Field annotation query method 5

You can also see the field comments by viewing the DDL statement of the table in Navicat. Select the table, click the "show right window" option in the lower right corner, and then click DDL. The specific operation steps are shown in the following figure:

Modify table and field comments

Modify table comments

Modify the syntax of table comments:

alter table Table name comment ='Modified table comments';

Case: modify student's table comments:

alter table student  comment ='Student list V2';

The execution results are shown in the following figure:

Modify field comments

Modify the syntax of table comments:

alter table Table name modify column Field name int comment 'Annotation information';

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

alter table student modify column username int comment 'Student name V2';

The execution results are shown in the following figure:

summary

This paper introduces the SQL for viewing table comments and modifying table and field comments. It also introduces five methods for viewing field comments: three command-line operation modes and two Navicat based operation modes. It is recommended to use SQL: "show full columns from table name" to view field comments, This SQL query is simple and there is no need to worry about changing the table structure by mistake.
​

Right and wrong are judged by ourselves, bad reputation is heard by others, and the number of gains and losses is safe.

The official account: Java interview