Shulou(Shulou.com)06/01 Report--

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

The addition of comments

Comments are added by adding the COMMENT keyword to the end of the table or column, which is up to 1024 characters long.

You can add comments to the table and columns when you create the table.

CREATE TABLE test_comment (id SERIAL PRIMARY KEY, col1 INT comment 'column comments') comment 'Table comments'

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

It can then be viewed through SHOW CREATE TABLE.

Mysql > SHOW CREATE TABLE test_comment\ gateway * 1. Row * * Table: test_commentCreate Table: CREATE TABLE `test_ comment` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `col1` int (11) DEFAULT NULL COMMENT 'column comments', PRIMARY KEY (`id`) UNIQUE KEY `id` (`id`)) Note'1 row in set (0.00 sec) of the ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' table

View of comments

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

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

SHOW TABLE STATUS WHERE name='table_name'

The following is the result viewed through SHOW TABLE STATUS:

Mysql > SHOW TABLE STATUS WHERE name='test_comment'\ gateway * 1. Row * * Name: test_comment Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 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: note 1 of the table row in set (2019 sec)

Through SHOW FULL COLUMNS, you can view the comments for the column, and its syntax is:

SHOW FULL COLUMNS FROM

The following is the result viewed through SHOW FULL COLUMNS:

Mysql > SHOW FULL COLUMNS FROM test_comment\ gateway * 1. Row * * Field: id Type: bigint (20) unsigned Collation: NULL Null: NO Key: PRI Default: NULL Extra: auto_incrementPrivileges: 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: comment 2 of the column rows in set (0.00 sec)

You can also view comments for tables or columns with the help of tables in INFORMATION_SCHEMA.

For example, check 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 | +-+ | comments on the table | +-+ 1 row in set (0.01 sec)

View the comments for the 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 comments | +-+ 1 row in set (0.00 sec)

Update of comments

For existing tables and columns, you can add comments through the appropriate update and modification operations.

Add column comments, update

CHANGE is equivalent to MODIFY, except that CHANGE rewrites the definition column and needs to write a complete column definition, including a new column name, even if you don't want to change the column name, while MODIFY does not need to specify a new column name.

Through the CHANGE syntax:

Note 2 in the mysql > ALTER TABLE test_comment CHANGE col1 col1 INT COMMENT 'column, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

Through the MODIFY syntax:

Note 2 in the mysql > ALTER TABLE test_comment MODIFY col1 INT COMMENT 'column, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

View the results of the modification:

Mysql > SHOW CREATE TABLE test_comment\ Gateway * 1. Row * * Table: test_commentCreate 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`)) Note'1 row in set (0.00 sec) of the ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' table

Addition, update of table comments

Through ALTER TABLE to complete the addition and update of table comments.

Note 2 to the mysql > ALTER TABLE test_comment comment 'table: query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

View the update results:

Mysql > SHOW CREATE TABLE test_comment\ Gateway * 1. Row * * Table: test_commentCreate 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`)) Note 2: 1 row in set of the ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' table (0.00 sec)

Deletion of comments

You can specify empty when updating the comment.

Mysql > ALTER TABLE test_comment COMMENT'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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\ gateway * 1. Row * * Table: test_commentCreate 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_ci1 row in set (0.00 sec)

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.