Search code examples
mysqlcreate-tablecomments

Is it possible to have a MySQL table comment defined before its create statement?


In order to document SQL code in a more linear fashion, I wanted to make appear the description of a table that is going to be created before the creation statement. So my first idea was to put that in a user-defined variable with which I could fill the comment instruction, but it seems to be unsupported (at least in the 5.6 version I have to deal with):

set @description = 'The following table is a useless dummy test.';
drop table if exists `test`;
create table `test` (dummy int) comment @test; -- syntax error
show table status where name='test';

Is there an other way to achieve the initial goal? Of course it's always possible to use -- SQL comments before the creation statement, but then it wouldn't appear in the recorded database structure, or at the price of a awful duplication.


Solution

  • Although it doesn't match the question, here is a solution to its underlying problem: having the description of a table directly next to it's creation statement.

    The MySQL syntax require to put the comment attached to the table at the end, but if the table have many fields, each declared on one or several lines, then the table comment can quickly be sent of sight of its declaration start.

    Now, one can easily create a table with it's comment as sole attached statement, then alter it to include fields it should contains.

    CREATE TALBE `test` COMMENT 'Informative description';
    ALTER TABLE test
      ADD id
        INT UNSIGNED NOT NULL AUTO_INCREMENT
        COMMENT 'Description of the id field',
      ADD data
        BLOB
        COMMENT 'Description of the data field',
      ;