This section describes how to write comments in your SQL code. It also points out a shortcoming of the mysql client program with respect to comment interpretation. Comments are often used in query files that are executed using mysql in batch mode, so you should be particularly aware of this limitation when you're writing such files.
The MySQL server understands three types of comments:
Anything from '#' to the end of the line is treated as a comment. This syntax is the same as is used in most shells and Perl.
Anything between '/*' and '*/' is treated as a comment. This form of comment may span multiple lines. This syntax is the same as is used in the C language.
As of MySQL 3.23.3, you can begin a comment with '-- ' (that is, two dashes and a space); everything from the dashes to the end of the line is treated as a comment. This comment style is used by some other databases, except that the space is not required. MySQL requires the space as a disambiguation character so that expressions such as value1-value2, where value2 is negative, will not be treated as comments.
Comments are ignored by the server when executing queries, with the exception that C-style comments that begin with '/*!' are given special treatment. The text of the comment should contain SQL keywords, and the keywords will be treated by the MySQL server as part of the statement in which the comment appears. For example, the following lines are considered equivalent by the server:
INSERT LOW_PRIORITY INTO mytbl SET ... ; INSERT /*! LOW_PRIORITY */ INTO mytbl SET ... ;
This form of comment is intended to be used for MySQL-specific extensions and keywords. MySQL will recognize the keywords, and other SQL servers will ignore them. This makes it easier to write queries that take advantage of MySQL-specific features but that still work with other database systems. The '/*!' comment style was introduced in MySQL 3.22.7.
As of MySQL 3.22.26, you can follow the '/*!' sequence with a version number to tell MySQL to ignore the comment unless the server version number is at least as recent as that version. The comment in the following UPDATE statement is ignored unless the server is version 3.23.3 or later:
UPDATE mytbl SET mycol = 100 WHERE mycol < 100 /*!32303 LIMIT 100 */;
The mysql client is more limited than the MySQL server in its ability to understand comments. mysql gets confused if certain constructs appear in C-style comments because it uses a less sophisticated parser than the server. For example, a quote character appearing inside of a C-style comment will fool mysql into thinking it's parsing a string, and it continues looking for the end of the string until a matching quote is seen. The following statements demonstrate this behavior:
mysql> SELECT /* I have no quote */ 1; +---+ | 1 | +---+ | 1 | +---+ mysql> SELECT /* I've got a quote */ 1; '>
mysql parses the first statement without problems, sends it to the server for execution, and prints another mysql> prompt. The second statement contains a comment with an unmatched quote. As a result, mysql goes into string-parsing mode. It's still in that mode after you enter the line, as indicated by the '> prompt. To escape from this, type a matching quote followed by a \c command to cancel the query. (See Appendix E, "MySQL Program Reference," for more information on the meaning of mysql's prompts.)
Semicolon is another character that will confuse mysql if it occurs within a C-style comment.