Alter MySQL table to add comments on columns

问题:

I have been checking the MySQL Documentation for ALTER TABLE and it does not seem to include a way to add or modify a comment to a column. How can I do this?

// for table
ALTER TABLE myTable COMMENT 'Hello World'

// for columns
// ???

回答1:

try:

 ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'  

回答2:

You can use MODIFY COLUMN to do this. Just do...

ALTER TABLE YourTable
MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"

substituting:

• YourTable with the name of your table
• your_column with the name of your comment
• your_previous_column_definition with the column's column_definition, which I recommend getting via a SHOW CREATE TABLE YourTable command and copying verbatim to avoid any traps.*
• Your new comment with the column comment you want.

For example...

mysql> CREATE TABLE `Example` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `some_col` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> ALTER TABLE Example
    -> MODIFY COLUMN `id`
    -> int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Look, I''m a comment!';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE Example;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Example | CREATE TABLE `Example` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Look, I''m a comment!',
  `some_col` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

* Whenever you use MODIFY or CHANGE clauses in an ALTER TABLE statement, I suggest you copy the column definition from the output of a SHOW CREATE TABLE statement. This protects you from accidentally losing an important part of your column definition by not realising that you need to include it in your MODIFY or CHANGE clause. For example, if you MODIFY an AUTO_INCREMENT column, you need to explicitly specify the AUTO_INCREMENT modifier again in the MODIFY clause, or the column will cease to be an AUTO_INCREMENT column. Similarly, if the column is defined as NOT NULL or has a DEFAULT value, these details need to be included when doing a MODIFY or CHANGE on the column or they will be lost.

回答3:

Script for all fields on database:

SELECT 
table_name,
column_name,
CONCAT('ALTER TABLE `',
        table_name,
        '` CHANGE `',
        column_name,
        '` `',
        column_name,
        '` ',
        column_type,
        ' ',
        IF(is_nullable = 'YES', '' , 'NOT NULL '),
        IF(column_default IS NOT NULL, concat('DEFAULT ', IF(column_default = 'CURRENT_TIMESTAMP', column_default, CONCAT('\'',column_default,'\'') ), ' '), ''),
        IF(column_default IS NULL AND is_nullable = 'YES' AND column_key = '' AND column_type = 'timestamp','NULL ', ''),
        IF(column_default IS NULL AND is_nullable = 'YES' AND column_key = '','DEFAULT NULL ', ''),
        extra,
        ' COMMENT \'',
        column_comment,
        '\' ;') as script
FROM
    information_schema.columns
WHERE
    table_schema = 'my_database_name'
ORDER BY table_name , column_name

1. Export all to a CSV
2. Open it on your favorite csv editor

Note: You can improve to only one table if you prefer

The solution given by @Rufinus is great but if you have auto increments it will break it.

回答4:

The Rufinus' answer is appropriate. I would prefer to use MODIFY instead of to use CHANGE if you don't need to change the name of the column.

I don't agree with the Marcus Pope comment. The mysql information database schema or any place where resides the information schema and that contains the data definition of any database isn't the place to treat these things. The information schema is used by the SGBD to record the changes required by any DDL command that was executed against database. Why we would need the DDL commands?

There is two types of SQL commands to use in any relational database: DML and DDL. When you add a comment you need to change the table structure.

From MySQL 5.6 documentation:

"INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them."

Chapter 21 INFORMATION_SCHEMA Tables

回答5:

As per the documentation you can add comments only at the time of creating table. So it is must to have table definition. One way to automate it using the script to read the definition and update your comments.

Reference:

http://cornempire.net/2010/04/15/add-comments-to-column-mysql/

http://bugs.mysql.com/bug.php?id=64439