Column Comments in MySQL

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';

22 comments to Column Comments in MySQL

  • 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

  • admin

    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.

  • admin

    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

  • admin

    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;

  • Mark

    There’s an easier way:

    SHOW FULL COLUMNS FROM myTable

  • Tyler Montbriand

    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?

  • admin

    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.

  • Bob Corrick

    Server version: 5.1.33-enterprise-gpl-advanced-log MySQL Enterprise Server – Advanced Edition (GPL) did not accept CHANGE COLUMN.

    I had to use MODIFY TABLE… ALTER COLUMN…

  • admin

    Hmmm… well it worked on whatever version I was using when I wrote the blog post.

  • David

    That’s why I don’t use it – changing table definitions in order to document them is too dangerous.

    I use a separate table, which I call data_dictionary, with three fields: table_name, column_name, and comment. A simple program (in perl, php, or whatever) uses the “show tables” and “describe table_name” MySQL commands to enumerate the actual database schema and then looks up the comments on the tables and columns. Presto! A self-documenting schema.

  • admin

    I used to use a method like that as well. However if you have your DDL under version control and you test the db changes on a staging server and only push them out to production when stable, the danger is minimized. I would not recommend anybody just barge out and hack on the database schema on a production server.

  • […] This post was mentioned on Twitter by RADAGA Proc. Dados, Fernando Schubert. Fernando Schubert said: @Loxford Isso ai q vc quer: http://dorkage.net/2009/01/column-comments-in-mysql/ […]

  • hello escreves temas muito interessantes no teu blogue….

  • Hi,

    I did what you suggested, but when I do DESCRIBE table_name, the comment is not there!
    What is going on? How do I see the comments?

    FYI, here is the test column that I created with comment as per your suggestion:
    ALTER TABLE compare temp VARCHAR(200) DEFAULT ‘Hello’ COMMENT ‘This is my comment about this column’;

    ThanX

  • Colleen

    I would’t put too much stock in this post, it’s waay out of date. My todo list clean out all these old posts but you can see the date on it.

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.