8

According to the documentation, there are two ways you can comment to the end of a line in MySql:

  • "#"
  • "-- "

Is there any difference between these?

If so, when should I use one vs. the other?

If not, does anyone know why both are supported?

Seems strange to me, especially when the hyphenated version still differs slightly from the standard SQL Syntax.

Jeremy Wiggins
  • 7,239
  • 6
  • 41
  • 56
  • 1
    [An example of where the difference between # and -- matters](https://stackoverflow.com/a/4300676/6085060) – Dan L. Sep 22 '17 at 19:30

3 Answers3

12

It only really matters if you want your SQL to be portable. For instance -- comments are OK in Sqlite and Postegresql while # are not. Your best bet is to use -- with a space following. (As far as I can remember I've hardly ever seen anything else)

Sean Redmond
  • 3,974
  • 22
  • 28
6

As the link you provided clearly explain it -- is the standard SQL "comment separator". Where MySQL departs from standard is in requiring an space after -- to be recognized as a comment. "Standard" SQL does not require this.

To provide an example, in the following code, -- is recognized as a comment token:

mysql> CREATE TABLE T(C int);   -- This is my new table
Query OK, 0 rows affected (0.18 sec)

But notice how the interactive interpreter misbehave without space after --:

mysql> CREATE TABLE T(C int);   --This is my new table
Query OK, 0 rows affected (0.24 sec)

    -> ;
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 '--This is my new table' at line 1

MySQL support some other comment format to accommodate with habit of various programmers : # like many script language and /* ... */ like C. It is quite astounding that // is not yet part of them.

mysql> CREATE TABLE T(C int);   /* This is my new table */
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TABLE T(C int);   # This is my new table
Query OK, 0 rows affected (0.24 sec)
Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • that space thing was really throwing me off. It seems so arbitrary, do you have any idea why they require the space? Is it actually ambiguous with something else or is it just a random idiosyncrasy? – Twisted on STRIKE at1687989253 May 09 '22 at 19:02
0

Both Oracle and SQL Server (as well as DB2 and others) support the -- and /* */ (multi-line) comment standard. So it's a good habit to use the same style in MySQL, as you will probably encounter situations where you're programming in multiple environments.

The requirement for a space after -- is a MySQL oddity, but is hardly a big deal as it helps clarity.

If, of course, you are using a lot of perl and MySQL, then you may opt for the # comment for similar reasons...

Stelios Touchtidis
  • 21
  • 4