Comments in MySql view scripts

Question

Is it possible to do so? I've tried multiple gui(mysql workbench, navicat, toad for mysql) and none of them save the comments like this:

 -- something important
 select .....
-- something else important

etc.

Is there a setting I am passing by or is this something that simply cannot be done? I ask since TOAD for Oracle saves what I posted in the code block above.

Answer 1

The mysql command line client will save comments for EVENTs, FUNCTIONs, PROCEDUREs, TRIGGERs, but only if you include the --comments option.

You can always have mysql include comments, by creating a ~/.my.cnf file with the following:

[mysql]
comments=1

Unfortunately, MySQL doesn't seem to preserve comments for VIEWs, even if this option is provided.

The only way I have determined to store comments inside a VIEW, is to include a dummy string at the end of the ORDER BY fields. For example:

CREATE
DEFINER = `root`@`localhost`
SQL SECURITY INVOKER
VIEW  
ex
AS
SELECT
*
FROM 
mysql.user
ORDER BY
user,
'a comment can go here';

Visit the MySQL Manual for more details.

Before MySQL 5.1, you could use MySQL-specific comments (/*! a comment */) inside VIEWs, but that "feature" was removed in 5.1 and letter. See here for more details.