MySQL table and column comments summary

MySQL table and column comments summary

Just like code, you can add comments to tables and columns in them to help others understand their functions. For some fields, the creator may not be able to remember their specific meaning after a certain period of time, so annotations are particularly important.

Adding comments <br /> Comments are added by adding the COMMENT keyword at the end of the table or column definition. The maximum supported length is 1024 characters.

You can add corresponding comments to tables and columns when creating tables.

CREATE TABLE test_comment 
 ( 
   id SERIAL PRIMARY KEY, 
   col1 INT comment 'column comment' 
 ) 
comment 'Comments on the table';

After executing the above statement, a table named test_comment is created, and corresponding comments are specified for the table and the col1 column.

Then you can view it through SHOW CREATE TABLE <table_name>.

mysql> SHOW CREATE TABLE test_comment\G
*************************** 1. row ***************************
    Table: test_comment
Create Table: CREATE TABLE `test_comment` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `col1` int(11) DEFAULT NULL COMMENT 'Column comment',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table comments'
1 row in set (0.00 sec)

View comments

In addition to the SHOW CREATE TABLE <table_name> syntax, there are other ways to view comments.

SHOW TABLE STATUS can view the comments of the table. Its syntax is:

SHOW TABLE STATUS WHERE name='table_name';
The following are the results of viewing through SHOW TABLE STATUS:

mysql> SHOW TABLE STATUS WHERE name='test_comment'\G
*************************** 1. row ***************************
      Name: test_comment
     Engine: InnoDB
    Version: 10
   Row_format: Dynamic
      Rows: 0
 Avg_row_length: 0
  Data_length: 16384
Max_data_length: 0
  Index_length: 16384
   Data_free: 0
 Auto_increment: 1
  Create_time: 2019-05-11 15:41:01
  Update_time: NULL
   Check_time: NULL
   Collation: utf8mb4_general_ci
    Checksum: NULL
 Create_options:
    Comment: Comments for table 1 row in set (0.00 sec)

The column comments can be viewed through SHOW FULL COLUMNS. The syntax is:

SHOW FULL COLUMNS FROM <tablename>

The following is the result of viewing it through SHOW FULL COLUMNS:

mysql>SHOW FULL COLUMNS FROM test_comment\G
*************************** 1. row ***************************
   Field: id
   Type: bigint(20) unsigned
 Collation: NULL
   Null: NO
    Key: PRI
  Default: NULL
   Extra: auto_increment
Privileges: select,insert,update,references
  Comment:
*************************** 2. row ***************************
   Field: col1
   Type: int(11)
 Collation: NULL
   Null: YES
    Key:
  Default: NULL
   Extra:
Privileges: select,insert,update,references
  Comment: Column comment 2 rows in set (0.00 sec)

The comments for a table or column can also be viewed using the tables in INFORMATION_SCHEMA.

For example, view the comments of the table:

SELECT table_comment 
FROM information_schema.tables 
WHERE table_name = 'test_comment';

Execution Result:

mysql> SELECT table_comment
  -> FROM information_schema.tables
  -> WHERE table_name = 'test_comment';
+---------------+
| TABLE_COMMENT |
+---------------+
| Table Notes |
+---------------+
1 row in set (0.01 sec)

View the comments for a column:

SELECT column_comment 
FROM information_schema.columns 
WHERE column_name = 'col1';

Execution Result:

mysql> SELECT column_comment
  -> FROM information_schema.columns
  -> WHERE column_name = 'col1';
+----------------+
| COLUMN_COMMENT |
+----------------+
| Column Notes |
+----------------+
1 row in set (0.00 sec)

Update of comments <br /> For existing tables and columns, you can add comments by performing corresponding update and modification operations.

Addition and update of column comments
CHANGE and MODIFY are equivalent, the difference is that CHANGE rewrites the column definition, you need to write the complete column definition, including the new column name, even if you do not want to modify the column, while MODIFY does not require the specification of a new column name.

Via CHANGE syntax:

mysql> ALTER TABLE test_comment CHANGE col1 col1 INT COMMENT 'Column comment 2';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Via MODIFY syntax:

mysql> ALTER TABLE test_comment MODIFY col1 INT COMMENT 'column comment 2';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

View the modification results:

mysql> SHOW CREATE TABLE test_comment\G
*************************** 1. row ***************************
    Table: test_comment
Create Table: CREATE TABLE `test_comment` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `col1` int(11) DEFAULT NULL COMMENT 'Column comment 2',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table comments'
1 row in set (0.00 sec)

Adding and updating table comments are done through ALTER TABLE.

mysql> ALTER TABLE test_comment comment 'Table comment 2';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

View the update results:

mysql> SHOW CREATE TABLE test_comment\G
*************************** 1. row ***************************
    Table: test_comment
Create Table: CREATE TABLE `test_comment` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `col1` int(11) DEFAULT NULL COMMENT 'Column comment 2',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table comment 2'
1 row in set (0.00 sec)

Deleting comments <br /> When updating comments, just specify empty.

mysql> ALTER TABLE test_comment COMMENT '';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test_comment MODIFY col1 INT COMMENT '';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

View the deletion results:

mysql> SHOW CREATE TABLE test_comment\G
*************************** 1. row ***************************
    Table: test_comment
Create Table: CREATE TABLE `test_comment` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `col1` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

You may also be interested in:
  • Mysql method to copy a column of data in one table to a column in another table
  • MySQL FAQ series: When to use temporary tables
  • Sql query MySql database table name and description table field (column) information
  • An example of how to use Java+MySQL recursion to concatenate tree-shaped JSON lists
  • Summary of Mysql table, column, database addition, deletion, modification and query problems
  • How to get the field list after querying the results of the Python module pymysql
  • Implementation of MySQL custom list sorting by specified field

<<:  Install CentOS7 in VMware (set static IP address) and install mySql database through docker container (super detailed tutorial)

>>:  Several ways to manually implement HMR in webpack

Recommend

How to move a red rectangle with the mouse in Linux character terminal

Everything is a file! UNIX has already said it. E...

How to install iso file in Linux system

How to install iso files under Linux system? Inst...

Solve the problem of using less in Vue

1. Install less dependency: npm install less less...

Detailed explanation of the usage of image tags in HTML

In HTML, the <img> tag is used to define an...

Analysis and description of network configuration files under Ubuntu system

I encountered a strange network problem today. I ...

MySQL installation tutorial under Linux centos7 environment

Detailed introduction to the steps of installing ...

Solution to define the minimum height of span has no effect

The span tag is often used when making HTML web pa...

Mysql timeline data to obtain the first three data of the same day

Create table data CREATE TABLE `praise_info` ( `i...

MySQL 5.6.24 (binary) automatic installation script under Linux

This article shares the mysql5.6.24 automatic ins...

JavaScript implements click to change the image shape (transform application)

JavaScript clicks to change the shape of the pict...

JavaScript implements the most complete code analysis of a simple magnifying glass (ES5)

This article shares the specific code of JavaScri...

A brief analysis of the differences between undo, redo and binlog in MySQL

Table of contents Preface 【undo log】 【redo log】 【...

Common failures and reasons for mysql connection failure

=================================================...

Summary of common tool functions necessary for front-end development

1. Time formatting and other methods It is recomm...

The perfect solution for highlighting keywords in HTML

I recently encountered a feature while working on...