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
 
Important notes:
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).

MySQL Quiz
11 Comments
  1. chris.kennedy says:

    re: within a bash script the comment was stripped out.
    This doesn’t sound right. Did you try to escape the comment character(s) with a backslash. This should work.

  2. Hazan Ilan says:

    Escaping them will not works as the client striping out these comments. Excessive escaping will cause the query to be invalid.

  3. Jesper Wisborg Krogh says:

    One potential downside having inline comments is that if the same query is executed with different comments, then it prevents the query cache to consider the two queries identical.

  4. Amy says:

    Escaping them will not works as the client striping out these comments. Excessive escaping will cause the query to be invalid.

  5. ROBINSON says:

    Learned something new today after all. Thank you.

  6. BernieR says:

    Greatings, Come On
    Gracias

    BernieR

  7. tiffany jewellery says:

    Thanks for good information that comes out to

    read.

  8. Best Registry Cleaner says:

    One again, your article is very nice

  9. TomPier says:

    great post as usual!

  10. Sook Jourdain says:

    Thankfully, whilst I was searching bing I came across your quality internet site. I must say the content and information here is top grade, and will be really helpful to me and I suspect others. To be sure I come back regularly I have added the website to my favourites.

  11. Jesse says:

    Thank you, this helped immensely. Couldn’t figure out why the comment was being stripped out in some places and not in others.

Leave a Reply