Alter MySQL table to add comments on columns

MysqlAlter Table

Mysql Problem Overview

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
-- ???

Mysql Solutions

Solution 1 - Mysql


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

Solution 2 - Mysql

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

MODIFY COLUMN your_column
your_previous_column_definition COMMENT "Your new comment"


  • 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`)
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.

Solution 3 - Mysql

Script for all fields on database:

        '` CHANGE `',
        '` `',
        '` ',
		' ',
		IF(is_nullable = 'YES', '' , 'NOT NULL '),
		IF(column_default IS NOT NULL, concat('DEFAULT ', IF(column_default IN ('CURRENT_TIMESTAMP', 'CURRENT_TIMESTAMP()', 'NULL', 'b\'0\'', 'b\'1\''), 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 ', ''),
        ' COMMENT \'',
        '\' ;') as script
    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.

Solution 4 - Mysql

The information schema isn't the place to treat these things (see DDL database commands).

When you add a comment you need to change the table structure (table comments).

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.


Solution 5 - Mysql

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.



All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJhonny D. Cano -Leftware-View Question on Stackoverflow
Solution 1 - MysqlRufinusView Answer on Stackoverflow
Solution 2 - MysqlMark AmeryView Answer on Stackoverflow
Solution 3 - MysqlworkdreamerView Answer on Stackoverflow
Solution 4 - MysqlmpolettoView Answer on Stackoverflow
Solution 5 - MysqlNageswara RaoView Answer on Stackoverflow