Of Stack

Summary of comments for MySQL tables and columns

  • 2021-12-04 20:01:51
  • OfStack

Like Code 1, you can add comments to the table and the columns in the table to make it easier for others to know its functions. For a few fields, after a certain period of time, the creator may not be able to remember their specific meanings, so comments are particularly important.

Addition of annotations
Comments are added by adding the COMMENT keyword at the end of the table or column definition, which supports up to 1024 characters.

You can add appropriate comments to tables and columns when you create them. 


CREATE TABLE test_comment 
 ( 
   id  SERIAL PRIMARY KEY, 
   col1 INT comment ' Comments on columns ' 
 ) 
comment ' Comments on tables ';

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

It can then be passed through SHOW CREATE TABLE < table_name > To check. 


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 ' Comments on columns ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Comments on tables '
1 row in set (0.00 sec)

Viewing of comments

Except SHOW CREATE TABLE < table_name > Syntax, and there are other ways to view comments.

SHOW TABLE STATUS can view the annotations of the table, and its syntax is:

SHOW TABLE STATUS WHERE name='table_name';
Here are the results viewed 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 on tables 
1 row in set (0.00 sec)

While SHOW FULL COLUMNS allows you to view the column's comments, the syntax is:

SHOW FULL COLUMNS FROM < tablename >

Here are the results viewed 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:  Comments on columns 
2 rows in set (0.00 sec)

Tables in INFORMATION_SCHEMA also allow you to view annotations for tables or columns.

For example, look at the comments of the table: 


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

Implementation results: 


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

View the column's comments: 


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

Implementation results: 


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

Update of annotations
For existing tables and columns, comments can be added through corresponding update and modification operations.

Add, update column comments
CHANGE and MODIFY are equivalent, the difference is that CHANGE rewrites the definition column, requires writing the complete column definition, including the new column name, even if you do not want to modify the column exemption, while MODIFY does not need to specify the new column name.

Through the CHANGE syntax: 


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

Through the MODIFY syntax: 


mysql> ALTER TABLE test_comment MODIFY col1 INT COMMENT ' Comments on columns 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 ' Comments on columns ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Comments on tables '
1 row in set (0.00 sec)
0

Adding and updating table comments
The table annotations are added and updated through ALTER TABLE. 


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 ' Comments on columns ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Comments on tables '
1 row in set (0.00 sec)
1

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 ' Comments on columns ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Comments on tables '
1 row in set (0.00 sec)
2

Deletion of comments
You can specify null when updating comments. 


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 COMMENT ' Comments on columns ',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Comments on tables '
1 row in set (0.00 sec)
4

Related articles: