I wrote column comments in MySQL database replica made for testing. Now I need to copy these comments to production database. Since comments are stored in table COLUMNS of information_schema database, and information_schema is not writeable, what would be the way to go to copy comments across? Test database version is 5.5.35 while production database is 5.1.71.

Is there any way to import these comments in production database other than enter them again?

Thank you


  • Is the database very big ??? Apr 4, 2014 at 15:27
  • @RolandoMySQLDBA 544 comments if that is what you mean, still a nuissance to enter them all by hand. Generally about 10000 records in the largest table, 27 tables in total.
    – domaniqs
    Apr 4, 2014 at 15:36

1 Answer 1


You could use the INFORMATION_SCHEMA to sculpt the SQL commands for you.

SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')
FROM (SELECT table_schema db,table_name tb,table_comment com
FROM information_schema.tables WHERE table_schema NOT IN
AND table_comment <> '') A;

Output this query to a text file and view the text file

SQL="SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,table_comment com"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('inforamtion_schema','performance_schema','mysql')"
SQL="${SQL} AND table_comment <> '') A"
mysql -uroot -ppass -ANe"${SQL}" > AddCommentsToTables.sql
less AddCommentsToTables.sql

If the output looks like what you need, then copy the script to the production server, login to mysql and run this to execute the script:

mysql> source AddCommentsToTables.sql

Give it a Try !!!

UPDATE 2014-04-04 16:34 EDT

It is a little harder to change each column definition's COMMENT. Why ???

According to the MySQL Documentation

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows:


The resulting column will be defined as BIGINT, but will not include the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain them, the statement should be:


Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.7, “Server SQL Modes”).

You would have to express the entire column definition from scratch each time. Doing that is not only tedious but dangerous because it would just copy the data into a fresh temp table and rename the temp table afterwards.

ADDITIONAL WARNING : StackOverflow has this : https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns

One of the comments on the accepted answer says : This solution can break the auto increments. – workdreamer Jul 31 '13 at 12:10

That's makes sense because of what I just said: The table get's copied, so there will be some auto_increment movement.

  • Thank you @RolandoMySQLDBA , it sounded very promising. After tweaking a bit your query, I have exported data that was held in table columns in column_comment: SQL="SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')" SQL="${SQL} FROM (SELECT table_schema db,table_name tb,column_comment com" SQL="${SQL} FROM information_schema.columns WHERE table_schema IN" SQL="${SQL} ('db')" SQL="${SQL} AND column_comment <> '') A" Import has been successfully finished, 345 queries executed. The problem is, nothing seems to be imported, the corresponding field in target database is empty.
    – domaniqs
    Apr 4, 2014 at 20:16
  • Try running 'FLUSH TABLES' and see if the information_schema catches anything new. Apr 4, 2014 at 20:18
  • I'm sorry for confusion. It actually inserted data into COMMENT. so the import was successful. I was expecting them in table columns in column_comment column. They were inserted into tables table into the field table_comment instead. Either way works for me. Out of curiosity, how do I insert them into desired field (column_comment in columns table)?
    – domaniqs
    Apr 4, 2014 at 20:25
  • That's a little harder. I'll explain why... Apr 4, 2014 at 20:27
  • Question: With the exception of the mysql schema, are all the table structures between Production and Test identical ??? Apr 4, 2014 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.