Additional languages
« 9.4 User-Defined Variables
10 Character Set Support »
Section Navigation      [Toggle]

9.5. Comment Syntax

MySQL Server supports three comment styles:

  • From a ‘#’ character to the end of the line.

  • From a ‘-- ’ sequence to the end of the line. This style is supported as of MySQL 3.23.3. In MySQL, the <squo;-- ’ (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.9.5.7, “'--' as the Start of a Comment”.

  • From a /* sequence to the following */ sequence, as in the C programming language. This syntax allows a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line.

The following example demonstrates all three comment styles: 

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

MySQL Server supports some variants of C-style comments. These enable you to write code that includes MySQL extensions, but is still portable, by using comments of the following form: 

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers will ignore the extensions. For example, MySQL Server recognizes the STRAIGHT_JOIN keyword in the following statement, but other servers will not: 

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the ‘!’ character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher: 

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

The comment syntax just described applies to how the mysqld server parses SQL statements. The mysql client program also performs some parsing of statements before sending them to the server. (It does this to determine statement boundaries within a multiple-statement input line.) However, there are some limitations on the way that mysql parses /* ... */ comments:

  • A semicolon within the comment is taken to indicate the end of the current SQL statement and anything following it to indicate the beginning of the next statement. This problem was fixed in MySQL 4.0.13.

  • A single quote, double quote, or backtick character is taken to indicate the beginning of a quoted string or identifier, even within a comment. If the quote is not matched by a second quote within the comment, the parser doesn't realize the comment has ended. If you are running mysql interactively, you can tell that it has gotten confused like this because the prompt changes from mysql> to '>, ">, or `>. This problem was fixed in MySQL 4.1.1.

For affected versions of MySQL, these limitations apply both when you run mysql interactively and when you put commands in a file and use mysql in batch mode to process the file with mysql < file_name.


User Comments

Posted by Brat Wizard on May 27 2003 12:05am[Delete] [Edit]

Some tiny gotchas-- nothing serious. Using two dashes ('--') inside a regular comment block (/* ... */) __MAY__ confuse the parser and in which case you will get strange errors. Also using single apostrophe's (') can wig it out too. Not sure why this happens.

/*
this is a good comment
*/

/* this is a bad comment-- it has two dashes
and __MIGHT__ wig out the parser
(for some reason it doesn't always happen, haven't
figure out the how/when part yet)
*/

Posted by Stan Bartsch on June 4 2003 5:52pm[Delete] [Edit]

My guess is the Parser "Wigs Out" when you include the double dash on the same line as the */, so that it looks like this:

/* Start a Comment
-- Here's a nested comment */


Now, the parser saw the /* and the --, but because of the --, it didn't see the */ and so it thinks the original /* comment is still active. It closed the -- comment on the carriage return.

The parser should either NOT recognize nested comments, or be adjusted to allow for closing one comment while "technically" inside another.

Posted by Philip on January 5 2004 3:39am[Delete] [Edit]

note that mysql parser does not always interpret correctly C -style comments
here's an example:

correct behaviour:
mysql> select 1+ /* aaa */ 2 jj; 

+----+
| jj |
+----+
|  3 |
+----+
1 row in set (0.00 sec)


"funny" :
mysql> select 1+ /* aaa */ 2;
+----------------+
| 1+ /* aaa */ 2 |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

and if you have new lines in the comment (just copy the example from the documentation) it gets too funny

Posted by [name withheld] on March 8 2004 10:39am[Delete] [Edit]

One combination in a source'd file that just caused me grief is # embedded within /* */:

/* Insert #1 of 4 */
insert into foo (bar) values (1);

The insert is skipped (0 rows affected)

This is better:
/* Insert number 1 of 4 */
insert into foo (bar) values (1);

Posted by Hoang Nguyen on September 18 2006 9:21am[Delete] [Edit]

If your .sql files spontaneously stop working, make sure no text editors 'accidently' changed the newlines to MAC (CR), which would make # and -- comments run until the end of the file (MySQL won't output an error either).

Add your own comment.