Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to query field comments in MySQL, I hope you have gained something after reading this article, let's discuss it together!

create the test database

To get started, let's create a database for the following demonstration.

--drop database if exists test2022;--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',`sn` varchar(50) DEFAULT NULL COMMENT ' student',`username` varchar(250) NOT NULL COMMENT 'student',`mail` varchar(250) DEFAULT NULL COMMENT 'Mailbox',`class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) comment ='Student table' ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 Query all table comments

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

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

Example: Query all tables in test2022 database Comments:

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 Fields Comments Field Comments Query Method 1

The query syntax is as follows:

show full columns from table names;

Example: Query comment information for all fields in student table:

show full columns from student;

The execution results are shown in the following figure:

Field comment 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';

Example: Query comment information for all fields in 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 Comment Query Method 3

Query the DDL (Data Definition Language) of the table and you can also see the comment content of the field. The SQL syntax executed is as follows:

show create table name;

Example: Query comment information for all fields in student table:

show create table student;

The execution results are shown in the following figure:

Field Comment Query Method 4

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

But this kind of operation is a bit dangerous, careful hand shake to correct the wrong structure of the watch.

Field comment query method 5

You can also see field comments when viewing DDL statements of tables in Navicat. Select the table and click "Show right window" option at the lower right foot, and then click DDL to display it. The specific operation steps are shown in the following figure:

Modify table comments and field comments Modify table comments

Modify syntax for table comments:

alter table name comment ='modified table comment';

Case study: modifying student's table comments:

alter table student comment ='Student table V2';

The execution results are shown in the following figure:

Modify field comments

Modify syntax for table comments:

alter table table name modify column field name int comment 'comment information';

Example: Modify the comment for name in the student table:

alter table student modify column username int comment 'studentnameV2';

The execution results are shown in the following figure:

After reading this article, I believe you have a certain understanding of "how to query field comments in MySQL". If you want to know more about it, please pay attention to the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.