A while back I was lamenting that table comments in MySQL get overlooked, and did a post on it, both how to create them, how to update them, and how to view them. The fact that my table comment post remains quite popular indicates that mySQL hasn’t SEO’d their pages on it thoroughly enough, and people are obviously looking for information on it. So… I thought I should follow with a column comment post. As a matter of fact in mySQL you can also comment at the column level, and probably should whenever the function of a field is not obvious.
“COMMENT” is just an option you can add for each column as well as at the end of the table when creating a table:
CREATE TABLE boo ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'The KEY obviously', . .
Updating comments is not so user friendly, in that it requires you to repeat the whole column description including the column name, as in
ALTER TABLE myTable CHANGE COLUMN myColumn myColumn BIGINT NOT NULL COMMENT 'This is the most important primary key ever';
Just like for table comments, you can see the column comments if you show the CREATE TABLE:
SHOW CREATE TABLE myTable;
UPDATE (in case you don’t read all the comments): Anil Gulati offers the following way to see comments in a more granular way. This works on newer MySQL 5.0 and up because previous versions did not have information_schema. information_schema is a meta table that encompasses information about (potentially) multiple databases. It is a potential security issue for shared hosting. Be careful with your perms or users will be able to log in as their own MySQL user and see information about databases they don’t own which might help them do a MySQL injection attack on their neighbors.
mysql> use information_schema; mysql> show tables; mysq> select column_name, column_comment from columns where table_name='your-table';