146

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 -- ???
Improve this question
0

5 Answers 5

Reset to default
179

try: 

 ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT 'id of user'
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

It seems to work fine, but is there any other way to do it without including the column definition ?
This solution can break the auto increments.
Note that altering a comment will cause a full resconstruction of the table. So you may choose to live without it on very big table.
@MarcusPope it's impossible. See dba.stackexchange.com/questions/78268/…
@user2045006 That is not (or no longer) true, as long as the column definition matches the existing definition exactly. Comments can be added without causing table reconstruction.
|
59

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.

Improve this answer

1 Comment

This will save many people's lives. AUTO_INCREMENT usually is added to the primary key. It will be crazy if AUTO_INCREMENT is lost.
18

Script for all fields on database:

SELECT table_name, column_name, CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', 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 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 ', ''), 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.

Improve this answer

7 Comments

According to a new user that doesn't have enough rep to comment, dump.aux_comment, needs to be column_comment,. Would you mind checking whether it is true?
Sorry for the mistake.
Of course, if you have an id auto_increment, you need to do ALTER TABLE MODIFY id INT NOT NULL AUTO_INCREMENT COMMENT 'id of user';. This don't break auto increments.
@workdreamer I was refering to Rufinus solution that you say it could break the auto increment. No, the given solution doesn't break it.
Awesome amazing!
|
4

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.

Chapter 21 INFORMATION_SCHEMA Tables

Improve this answer

Comments

-2

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

Improve this answer

2 Comments

This answer is wrong (it's possible to update the comments after table creation even if it's tedious), and it only links to other websites, so it's not useful in terms of a Stack Overflow answer.
If it’s tedious, it’s error prone. Someone in a comment on a other response, said it may also break auto‑increments. It’s clear it was not intended, and it only works with workarounds. So what says Nageswara Rao is formally correct and is backed by the officiel source he mentioned. Yes, it’s a pity this obvious feature is missing, but it is not Nageswara Rao’ fault.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.