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.
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
# -----------------------------------------------------