In many cases, we need to check the MySQL table comment, or a certain table under the comment of all fields, so this article to check and compare several ways to query the comment.

Create a test database

To begin, let’s create a database for the following demonstration.

Delete the database if it exists
drop database if exists test2022;
Create database
create database test2022;
Mysql > alter 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 id',
  `username` varchar(250) NOT NULL COMMENT 'Student Name',
  `mail` varchar(250) DEFAULT NULL COMMENT 'email',
  `class_id` int(11) DEFAULT NULL.PRIMARY KEY (`id`)
) comment='Student List' ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
Copy the code

Query all table comments

You can query comments for all tables under a database using the following SQL:

SELECTTable_name Specifies the table name, table_comment specifies the table descriptionFROM information_schema.TABLES 
  WHERE table_schema='database name'
  ORDER BY table_name
Copy the code

Example: query all tables in test2022 database

SELECTTable_name Specifies the table name, table_comment specifies the table descriptionFROM information_schema.TABLES 
  WHERE table_schema='test2022'
  ORDER BY table_name
Copy the code

The execution result is as follows:

Query all field comments

Field comment Query method 1

The query syntax is as follows:

show full columns fromThe name of the table.Copy the code

Select * from student; select * from student;

show full columns from student;
Copy the code

The execution result is as follows:

Field Comment Query method 2

The query syntax is as follows:

selectCOLUMN_NAME Field name, column_COMMENT field description,column_type field type, column_key constraintfrom information_schema.columns 
  where table_schema='database name' and table_name='the name of the table'; 
Copy the code

Select * from student; select * from student;

selectCOLUMN_NAME Field name, column_COMMENT field description,column_type field type, column_key constraintfrom information_schema.columns 
  where table_schema='test2022' and table_name='student';
Copy the code

The execution result is as follows:

Field comment Query method 3

The DDL (Data Definition Language) of the query table can also see the annotated contents of the fields. The SQL syntax is as follows:

show create tableThe name of the table.Copy the code

Select * from student; select * from student;

show create table student;
Copy the code

The execution result is as follows:

Field Comment Query method 4

If you are using Navicat, you can right-click on the table and click Design to go to the design page and see the field annotations, as shown below:But this kind of operation is a little dangerous, careful hand shaking table structure changed wrong.

Field Comment Query method 5

In Navicat, you can also see the field annotations by viewing the DDL statement of the table. Select the table and click the “Show the right window” option, and then click DDL to display the field annotations, as shown in the picture below:

Modify table comments and field comments

Modify table comment

Alter table comment syntax:

alter tableThe name of the table the comment='Modified table comments';
Copy the code

Alter table student;

alter table student  comment ='Student Table V2';
Copy the code

The execution result is as follows:

Modify field comments

Alter table comment syntax:

alter tableThe name of the table the modifycolumnThe field nameint comment 'Comment information';
Copy the code

Alter table student; alter table student;

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

The execution result is as follows:

conclusion

This article describes the SQL for viewing table comments and modifying table and field comments. It also describes five methods for viewing field comments: three command line operations and two navicat-based operations. SQL is recommended: “Show full columns from table name” to view column comments. This type of query is simple and does not worry about changing the structure of the table.

Judge right and wrong from yourself, praise to listen to others, gain and loss in the number.

Public account: Java Chinese Community