MySQL Column Comments



  • I just discovered MySQL has the ability to store text comments for each column and table. Any opinions on whether this is an effective form of developer documentation?



  • Every DB I've worked with has this. Except MongoDB.

    I've never worked at a place where it was actually used.



  • I've seen it used but like most comments, it's usually out of date.



  • I think the maximum comment length is quite short, so you may not be able to put much information in there.



  • I may just use it to document some specific fields in the create database script.

    Looks like column comments support up to 255 chars, and table comments only 60. Ought to be enough. Anything more than that, I suppose I can do a standard SQL comment line above the item.



  • @mott555 said:

    Looks like column comments support up to 255 chars, and table comments only 60.

    Ah, okay. Maybe it was table comments I'd had trouble with.



  • The MS SQL Server version is like a key-value store attached to the database object. You can have as many "extended properties" as you want on any object, and they can be up to 7.5k in length.

    Definitely a powerful enough system to do good stuff with but, like I said, I've never seen it used in practice.


  • Discourse touched me in a no-no place

    @mott555 said:

    any opinions on whether this is an effective form of developer documentation?

    Mine? Just the usual trope about "comments and code disagree? Both are wrong."


  • ♿ (Parody)

    I think the only place it's relatively useful is in an enterprisey environment where stuff is actually reviewed and kept in sync, and you have enough people potentially looking at stuff that they might be useful.



  • Worse, DB comments tend to be even worse at being kept in sync in my experience.



  • @Arantor said:

    Worse, DB comments tend to be even worse at being kept in sync in my experience.

    Because nobody actually looks at the database, right?



  • Pretty much, and even if they do, they aren't likely to update the comments, assuming they have permission.



  • I used to use it when I ran a small web design business that focused on php/mysql.

    Don't use it. It's a nightmare to maintain, and the comments aren't in a useful place. Just use normal methods like a wiki, a word doc, an excel sheet, or whatever is appropriate for your documentation purposes.

    Run far away from mysql comment meta field.



  • At my work, developers are explicitly forbidden from seeing production data stores, and nobody sees production databases except for the analysts who usually already know what the field(s) are for - and if not, they make something up that seems to fit.



  • I was overthinking things. All I wanted was a description of some fields in the createDB.sql file so they're easy to find and not horribly separated. Wasn't expecting anyone to query the database to get column comments. Really I could just do

    -- This field does a really cool thing
    MyCoolField VARCHAR(50) NOT NULL

    and anyone looking at the script can see why that field is special enough to need extra explanation.



  • In one of my DB-s I'm using a-table-per-entity strategy (each "project" gets its own table based on the same row-type). I had plans to use comments as table metadata (maybe store JSON or something), but in the end, I had no need for it. Probably for the better.



  • It's been several years, but the last time I used mysql I used PHPMyAdmin to manage the database, and I don't think the export scripts ever exported any data from the comments of the fields. YMMV and years may have changed that, but I still strongly recommend a managed excel sheet over database comments.



  • @Matches said:

    At my work, developers are explicitly forbidden from seeing production data stores

    Do your dev/test environments never mirror prod then? Are they full of fake test data? Or do you have some process to de-identify/desensitize production data for dev/test use?



  • Frequently aren't in sync and frequently have faked data more than a year out of date. Data is supposed to be mostly correct except some scrubbing.


  • BINNED

    @blakeyrat said:

    I've never seen it used in practice.

    I've used it at a previous job. We put meanings of flag fields in there e.g. a status field would have a comment like 'D = Done, O = ongoing, H = held'. Was very appreciated by support staff who might not be that familiar with the specific field or table to remember all specific code meanings.
    Of course as with all documentation: if you don't update it it turns vile very quickly.


Log in to reply