Modify MySQL table to add column comments

Question

I checked the MySQL documentation for ALTER TABLE, and there seems to be no way to add or change a comment on a column in it. How can i do this?

-- for table ALTER TABLE myTable COMMENT 'Hello World' -- for columns -- ??? 

Answer 1

try:

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

Answer 2

You can use MODIFY COLUMN for this. Just do it ...

 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 a your_previous_column_definition column, which I recommend getting with the SHOW CREATE TABLE YourTable and copy verbatim to avoid any pitfalls. *
• Your new comment with the comment of the column 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''ma 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''ma 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 the MODIFY or CHANGE clauses in an ALTER TABLE statement, I suggest you copy the column definition from the output of the SHOW CREATE TABLE statement. This will protect you from accidentally losing the important part of the column definition without realizing that you need to include it in the MODIFY or CHANGE clause. For example, if you are a MODIFY a AUTO_INCREMENT , 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 a column is defined as NOT NULL or is DEFAULT , this data must be included when executing MODIFY or CHANGE in the column or it will be lost.

Answer 3

Script for all fields in the 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 

• Export all to CSV
• Open it in your favorite csv editor

Note. You can improve only one table if you want

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

Answer 4

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

I disagree with Marcus Papa's comment. The mysql information database schema or any other place where the information schema is located and which contains the data definition of any database is not the place to handle these things. The information schema is used by the SGBD to record the changes required by any DDL command that has been executed for the database. Why do we need DDL commands?

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

From the MySQL 5.6 documentation:

"INFORMATION_SCHEMA is the database in each instance of MySQL, a place where information about all other databases that the MySQL server supports is stored. 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 install triggers on them. In addition, there is no database directory with that name.

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

Chapter 21 Tables INFORMATION_SCHEMA

Answer 5

According to the documentation, you can add comments only during table creation. Therefore, you must have a table definition. One way to automate it with a script is to read the definition and update your comments.

Link:

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

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