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 use vuex in Vue project

Table of contents What is Vuex? Vuex usage cycle ...

MySQL conditional query and or usage and priority example analysis

This article uses examples to illustrate the usag...

CSS float property diagram float property details

Using the CSS float property correctly can become...

JS Easy to understand Function and Constructor

Table of contents 1. Overview 1.1 Creating a func...

Best Practices for Sharing React Code

When any project develops to a certain complexity...

Docker's four network types principle examples

Four network types: None: Do not configure any ne...

A brief discussion on VUE uni-app custom components

1. Parent components can pass data to child compo...

Detailed installation process of nodejs management tool nvm

nvm nvm is responsible for managing multiple vers...

A Deeper Look at SQL Injection

1. What is SQL injection? Sql injection is an att...

How to install the graphical interface in Linux

1. Linux installation (root user operation) 1. In...

WeChat applet realizes chat room function

This article shares the specific code of WeChat a...

HTML Code Writing Guide

Common Convention Tags Self-closing tags, no need...

Vue + element dynamic multiple headers and dynamic slots

Table of contents 1. Demand 2. Effect 3. All code...

Summary of javascript date tools

let Utils = { /** * Is it the year of death? * @r...

A brief discussion on front-end network, JavaScript optimization and development tips

1. Network Optimization YSlow has 23 rules. These...