MySQL – Copy Table Comments from One Database to Another

information-schemaMySQL

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

Maciej

Best Answer

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
('inforamtion_schema','performance_schema','mysql')
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:

ALTER TABLE t1 MODIFY col1 BIGINT;

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:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

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.

Related Question