How to retain comments in MySQL statements
There is a lot of buzz lately around retaining comments in MySQL’s logs and MySQL Stored procedures (Routines).
When analyzing a MySQL query, it’s nice to have a comment inside the query (or procedure) that can provide us with more info about the query.
For example, When analyzing MySQL’s logs (like Slow Query Log or General Log), seeing the calling function’s name that issued this slow query can help a lot. This way it’s much easier to find the query in the code or if it’s called from a script.
One solution is to write an inline comment (C multi column comment style) in the query’s body.
For example: “SELECT /* calling function name */ column1, column2 … FROM table …”.
However, when I tried to use it within a bash script the comment was stripped out.
I found that the MySQL client program is stripping out in-query (or in-procedure) comments.
Fortunately, there is a flag that controls this behavior whose default is to discard comments (–skip-comments).
To retain (preserve) comments in MySQL statements sent to the server (i.e. queries or MySQL Stored procedures) use the -–comments flag (or -c).
For example $ bin/mysql –comments -uxxxx -pyyyy
1. This flag is active from MySQL version 5.0.52. (see http://mirror.leaseweb.com/mysql/doc/refman/5.0/en/mysql-command-options.html)
2. The comments are retained within the scripting language with the embedded SQL libraries (such as PHP).