How to use "--" in MySQL as a comment start tag

Some other SQL databases take "--" as a comment start flag. The MySQL server uses "#" as the starting character for the comment. For MySQL server, you can also use the C-style annotation: */* This is the note/*.

MySQL server 3.23.3 and later versions support the "--" annotation style, but require comments followed by 1 spaces (or control characters, such as new lines). The reason for using spaces is to prevent problems associated with automatic generation of SQL queries, using code similar to the following, where automatic "!payment!" Insert value for "payment":

UPDATE account SET credit=credit-!payment!

Consider what happens if the value of "payment" is negative such as "-1":

UPDATE account SET credit=credit--1

"Credit--1" is a valid expression in SQL, but if "--1" is interpreted as a comment, some of the expressions will be discarded. As a result, the meaning of an expression is completely different from the intended meaning.

UPDATE Account SET Credit=credit The statement does not make any changes to the value! This suggests that allowing comments to start with "-" can have serious consequences.

With this kind of annotation method in MySQL server 3.23.3 and later versions, "Credit--1" is actually safe.

Another security feature is that the MySQL command-line client deletes all rows that begin with "--".

The following information makes sense only when using MySQL that is higher than 3.23.3:

If you have an SQL program in the form of 1 text files that contains a "--" annotation, you should use the Replace utility as follows to convert it to a comment that uses the "#" character:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql db_name

Rather than the usual:

shell> mysql db_name < text-file-with-funny-comments.sql

You can also edit the annotation file and change the "--" note to the "#" NOTE:

shell> replace " --" " #" -- text-file-with-funny-comments.sql

Use the following command to change it back:

shell> replace " #" " --" -- text-file-with-funny-comments.sql