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';
Listen to this post 
Wow, thank you for this post. You’re absolutely right, it’s quite hard to find info on how to create a MySQL table. I know that this will come in handy for many people.
Uhhh… it’s actually pretty easy to find the info on how to create a mySQL table if you google it. But the comment is kind of a buried option and they don’t really say much about it.
Stumbled
Cool post thanks
I checked and JS did in fact stumble this post and that’s why I didn’t spam file his comment. But please people, refer to something in the post when you comment because the most common spam comment is “cool post” or “great post” or something like that. There is nothing in the comment to show me that he was a real human who actually read the post, only the stumble trail made it real. I do give some luv to my top commentators, so I am a target for the linkless “great post” spam so they get their link on the blog’s home page if they do it enough.
Which editor are you using in this post ? I mean on that screenshots.
I am using a plugin to do the code fragments called Google Syntax Highlighter for Wordpress. It’s pretty nifty but it forgets things if you edit your post later.
True, its quite hard to find info on how to create a MySQL table…. Bt i thnk nw its much easy…
Thanx 4 sharing
Hey I found your post when looking for info on how to retrieve comments, but I had a feeling MySQL would offer better so I browsed the manual:
mysql> use information_schema;
mysql> show tables;
mysql> select column_name, column_comment from columns where table_name=’your-table’;
This is a much more convenient and flexible way to view table and column comments.
Thanks for the post, though.
Anil
Thanks for the improvement. This will work with newer MySQL versions, starting with 5 I think. Until recently I was trapped on 4.1 seemingly forever with one client because they refused to upgrade. I will update the post to reflect this.
To view field comments and other information You can use
show full columns from TABLE_NAME;
There’s an easier way:
SHOW FULL COLUMNS FROM myTable
Having to alter the table itself to update a column comment is ludicrous and dangerous… One typo and it could do anything to the data. And I don’t see how to safely apply this to composite keys… You can’t specify that it’s *part of* a key when you do the alter statement; when it’s done, will it still be?
I agree this is ludicrous, that’s why so many people didn’t do it, or didn’t update them. But surely no one would just bash something like that directly into a production database. It would always be good to test on a copy and also put it under version control. There may be a better way now–his post is kinda old and based on MySQL 4.1 I think. And anyway a lot more people generate their SQL create table statements with automatic tools these days. So perhaps commenting the YAML would work as well or better.