Comments in MySql view scripts
mysql
Solution
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 `VIEW`s, but that "feature" was removed in 5.1 and letter. See here for more details.
Problem
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.