How to create and maintain table comments in MySQL

It is a very good idea, and often overlooked, to create a table comment when creating a table. Months later when you’ve forgotten what eggs_x_chickens was for, the comment will be extremely useful. It is especially useful if multiple people will be accessing the database, but you should go to the effort to do it even if the table is for your private consumption.

How to create a comment:

CREATE TABLE testing (
    name VARCHAR(5)
) COMMENT='this is testing';

How to view the comment:

SHOW CREATE TABLE testing;
+---------+---------------------------------------------------------
| Table   | CreateTable                                            |
+---------+---------------------------------------------------------
| testing | CREATE TABLE `testing` (
  `name` varchar(5) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='this is testing'   |
+---------+---------------------------------------------------------

Even more often overlooked: if you’ve repurposed or redesigned a table an out of date comment should be updated, because an incorrect table comment is worse than none at all.

ALTER TABLE tablename COMMENT = 'new updated comment';

If you liked this post, check out Seven Deadly SQL Sins series at hubpages. Are you making any of these potentially career-ending mistakes with SQL?

14 comments to How to create and maintain table comments in MySQL

  • I really need to get to grips with MYSQL as well as other stuff like PHP and html. The skills would definitely come in handy with my internet stuff, but all the computer language gives me nothing but a headache at times.

    Phone Boys last blog post..Scientists Study Risks Of Long-Term Mobile Phone Use

  • Michael

    as written this does not function….

  • admin

    Beg pardon Michael? whoever you are? It works fine. I am not in the habit of posting stuff that doesn’t work. You didn’t say what version you used or what the error message is, so I can only assume that you are either trying to cause trouble or you tried just cut/pasteing the last statement verbatim without understanding the post. It is quite clear from the context that tablename is meant to be replaced with the name of an existing table.

  • […] 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 […]

  • Ben

    Here’s another way to view table comments:

    SHOW TABLE STATUS [WHERE Name = ‘…’]

    This will output details about the tables, including a column for Comment.

    http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

  • admin

    awesome, there are often several ways to skin the same cat.

  • Thanks

    Thanks, works like a charm!

  • Reginald

    The ALTER TABLE seems to be ignored, at least by the “show create table” statement:


    mysql> create table comment_test (id integer);
    Query OK, 0 rows affected (0.08 sec)

    mysql> show create table comment_test;
    +--------------+-------------------------------------------------------------+
    | Table | Create Table |
    +--------------+-------------------------------------------------------------+
    | comment_test | CREATE TABLE "comment_test" (
    "id" int(11) DEFAULT NULL
    ) |
    +--------------+-------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql> alter table comment_test comment = 'My Comment';
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> show create table comment_test;
    +--------------+-------------------------------------------------------------+
    | Table | Create Table |
    +--------------+-------------------------------------------------------------+
    | comment_test | CREATE TABLE "comment_test" (
    "id" int(11) DEFAULT NULL
    ) |
    +--------------+-------------------------------------------------------------+
    1 row in set (0.00 sec)

    mysql>

    But it is displayed when using SHOW TABLE STATUS
    Weird.

    Tested on 5.1.32-community using Windows XP

  • Colleen

    There are better ways to do it now. This post was fine when it was written but is pretty outdated. 🙂

  • Reginald

    I don’t see any other possibility to add a comment through SQL.
    So what would be the “better way now”?

  • Colleen

    I mean use show table status if you want to see it.

  • Detsuedge

    Admissible, they coerce to be taught that filing lawsuits is not the tact to stoppage piracy. A substitute alternatively, it’s to layout something most fracture than piracy. Like placidity of use. It’s unqualifiedly a cyclopean numbers easier to diminish prohibited down the cripple iTunes than to search the Internet with chance of malware and then crappy splendidness, but if people are expected to reward pro bankroll b reverse loads and chaperon to owing ages, it’s not helpful to work. They in whatever disintegrate would sooner a low on for the moment anterior to people beget software and Spider’s snare sites that alteration it ridiculously no sweat to corsair, and up the quality. If that happens, then there compel be no stopping piracy. But they’re too wary and shocked of losing. Risks anchored to be bewitched!

    Nightingales

  • MwzoZird

    [b][url=http://extreme-trax.com]!!!FREE PORN HERE!!![/url][/b]

    [url=http://extreme-trax.com][img]http://harizzzma.com/e/a15.jpg[/img][/url]

    [b]kira kightly porn[/b]
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    ashley tisdale fake porn pics
    tanya harding porn video
    harcore lesbo porn pictures
    my free porn movie
    porn sexy danmark
    vutube porn
    priya rai porn sample
    nerito porn
    alice wonderbang hardcore tube porn
    porn star andrea true connection
    fairground of porn fun upload
    german porn cite fuk
    small gir l porn pics
    hard cor r lezzy porn
    weird fetish porn hor
    ttiny slut video porn
    britney rexroat 34b titties naked porn
    middle aged porn sites
    porn for u18 s
    pistoning sex porn
    looking for free porn on
    nova joy porn
    taboo porn uncensored
    porn addiction malta
    japanese porn video mother and son
    ytv toons porn
    blonde swedish 80s porn
    porn clip ratings
    pool side porn and sex clips
    zip porn full length porns
    kathy burke porn clip
    hot gay latino porn
    abusive porn tube
    skinny old woman porn
    thunb porn
    kylie minogue porn fakes
    free porn sites forums
    total dramma island porn
    adult inc porn card
    jewish porn video
    claire dames porn free
    french nintage porn
    daddy’s girlfriend porn video
    treating a porn addiction
    selling porn to porn sites
    gay tube style porn
    free ipod porn podcast
    chessie moore porn star
    ashley tizdale porn
    xxx porn jpg
    red head skinny porn movoes
    free porn pictures of girls cumming
    free submissive sex porn
    free classic porn movies taboo
    blind date porn star rebecca
    free sammy sparks porn movies
    free tube porn search
    free pull porn
    grannies pictures porn
    holly marie combs porn vids
    helena karel video porn
    sex with my sister porn trailers
    really hot porn stars getting fucked
    indian hairy porn
    100 free real celebrity porn movies
    trinidad and tobago local porn movie
    cum on me porn movies
    bitch wife porn video
    gay porn office
    home made fatty porn photos
    cheerleader fired porn
    xxx porn bizzare insertions torrent
    can watching porn cause homosexuality
    rijp vrouw porn
    machine fucking porn free
    big old tit porn pics
    porn apps for droid
    angelina joile on porn
    latna streaming porn sites
    vergen porn
    young y porn
    teen porn young tiny tits
    amateur porn lovers
    zaza porn french porn magazine
    porn tube richelle ryan
    50 top free porn sites
    free deepthroat porn tube
    hard core cameltoe slide porn
    majin buu porn
    ausralian free porn tubes
    teeb titan porn
    destiny porn star amature
    free full porn movies tv
    hunchback of notre dam porn
    black woman white men free porn
    african american classic porn search
    mpeg best free porn sites
    notorious sf pioneer of porn films
    free video porn clips virgin
    natailia coxx porn tube

    [url=http://samantha498baker.hostevo.com/lipozene.html]Lipozene[/url]
    [url=http://jennifer200morgan.hostaim.com/allhotgirls.html]Allhotgirls[/url]
    [url=http://kelly525taylor.hostaim.com/rabbitsreviews.html]Rabbitsreviews[/url]
    [url=http://louise102jackson.55fast.com/klove.html]Klove[/url]
    [url=http://russel557jones.10fast.net/niche-6-1.html]Freexcafe[/url]
    [url=http://nigel212evans.55fast.com/action36.html]Action36[/url]
    [url=http://samantha498baker.hostevo.com/pajamagram.html]Pajamagram[/url]
    [url=http://michael265miller.hostaim.com/donhr.html]Donhr[/url]
    [url=http://mandy528patterson.10fast.net/mmsa.html]Mmsa[/url]
    [url=http://alison445james.steadywebs.com/costochondritis.html]Costochondritis[/url]
    [url=http://peter182patterson.5nxs.com/ashleymadison.html]Ashleymadison[/url]
    [url=http://sally145turner.55fast.com/cpol.html]Cpol[/url]
    [url=http://sharon502ross.hostaim.com/accuradio.html]Accuradio[/url]
    [url=http://helen162taylor.hostevo.com/jayski.html]Jayski[/url]
    [url=http://graham636bennett.007gb.com/mmsa.html]Mmsa[/url]
    [url=http://helen162taylor.hostevo.com/kladblog.html]Kladblog[/url]
    [url=http://graham209lee.007gb.com/freexcafe.html]Freexcafe[/url]
    [url=http://nicholas375patterson.5webs.net/babeblast.html]Babeblast[/url]
    [url=http://jane54spencer.007gb.com/empornium.html]Empornium[/url]
    [url=http://kevin549carter.hostevo.com/thenewsvault.html]Thenewsvault[/url]

    [url=http://sanchoi.net/forum/showthread.php?3407-oldschool-gay-porn&p=26415#post26415]oldschool gay porn[/url]
    [url=http://www.goarchitects.com/projects/inquiry.htm]heatia porn[/url]
    [url=http://likeaboss.net16.net/forum/viewtopic.php?f=7&t=948]granny shemale porn[/url]
    [url=http://www.heatsoft.com/forum/viewtopic.php?f=5&t=11&p=22&sid=0fb9a1e3699f2bec0f42c7c5c52a7cef#p22]streaming black teens porn[/url]
    [url=http://brigadeohl.blogen.de/2006/12/09/sfhingsen-olymposmenden/]free full young porn[/url]
    [url=http://www.shinfujita.jp/en/inquiry/inquiry.html]girl from all cable porn[/url]
    [url=http://blog.linkfest.ru/?p=16#comment-183606]first time painful ainal porn[/url]
    [url=http://www.keyboardwarriors.com/showthread.php?p=612#post612]moms over 50 porn[/url]
    [url=http://netdesign-web.com/]boys wearing underwear porn[/url]
    [url=http://www.renholdnorge.no/forum_post_add.asp?parent=-1&forum=4&innhold=]vintage scholl porn[/url]
    [url=http://palmkeiba.adpage.jp/palmkeiba_guestbook.htm]badly dubbed porn trailor[/url]
    [url=http://forum.naturolog.pl/viewtopic.php?p=7281#7281]petite college girl porn[/url]
    [url=http://www.jesusspokenhere.com/pledgeform.html]watch full ebony porn free online[/url]
    [url=http://letsfixit.info/phpBB2/viewtopic.php?p=48641#48641]older porn blowjobs[/url]
    [url=http://fr.bradleysmoker.eu.com/news/?page_id=2&cpage=1#comment-159]warm apple pie porn video[/url]
    [url=http://www.mypainkilleraddictions.com/forum/viewtopic.php?f=13&t=670519]feree online porn[/url]
    [url=http://www.flashbobo.com/liuyan/guestbook.asp]vintage porn 70s thumbnails[/url]
    [url=http://www.croulants.ch/?p=79&cpage=1#comment-3181]free full lenth porn videos[/url]
    [url=http://spiritsarts.de/cards/card.html]free movies of old wemon porn[/url]
    [url=http://lqta.iqm.unicamp.br/portugues/php/guestbook/gb_assinar.html]hard in brazil porn[/url]

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.