6

I have a series of scripts for creating a schema, with a comment like the following before each instruction: 

--------------------------------------------------------
--  Table TABLE_NAME
--------------------------------------------------------

When I execute the script from mysql on the command line, I get a bunch of errors like the following: 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '------------------------------------------------------
------------------------' at line 1

(actually, I get one error for each comment, in spite of the message always referring to line 1).

To quickly solve my problem I simply removed the comments and the script ran without problems, but I was surprised to see such a behaviour and to be unable to find a relevant question here on stackoverflow. Does anyone have an explanation? Did anyone ever observe such an odd behaviour?

I am running mysql 5.6.30, the default for 5.6 on ubuntu at this time.

Improve this question

4 Answers 4

Reset to default
10

From the MySQL Manual:

From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (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.8.2.4, “'--' as the Start of a Comment”.

(Emphasis Mine)

tl;DR Your -- indicating a comment must be followed by at least one whitespace or control character.

Fixed code of yours:

-- -----------------------------------------------------
--  Table TABLE_NAME
-- -----------------------------------------------------

In MySQL You can also use this syntax:

/* 
*    Table TABLE_NAME
*/

Or even this:

# -----------------------------------------------------
#   Table TABLE_NAME
# -----------------------------------------------------
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

And, unlike Oracle syntax, MySql allows comments /* in this style */ as well as -- in this style.
@OllieJones Even # in this style :D
I accepted this answer as the most complete. I am used to full lines of dashes interpreted as comments in SQL, so I was surprised to see that they raised errors. I will inform the providers of the software I am installling that the content of their "mysql" software is actually not MySQL-compliant :)
3

You need a space after two dashes to indicate a comment. Without it it is just a string:

-- ------------------------------------------------------
--  Table TABLE_NAME
-- ------------------------------------------------------
Improve this answer

Comments

1

Personally, I only use the two dashes -- when commenting a single line. When working with block comments, I tend to use the following format:

/**
 * Table TABLE_NAME
 *
 */
Improve this answer

Comments

0

From: http://dev.mysql.com/doc/refman/5.7/en/comments.html The space after double dash i.e. "-- " is part of the comment identification! Rationale behind MySQL decision: is here http://dev.mysql.com/doc/refman/5.7/en/comments.html

The space is required to prevent problems with automatically generated SQL queries that use constructs such as the following, where we automatically insert the value of the payment for payment:

UPDATE account SET credit=credit-payment
UPDATE account SET credit=credit--1
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.