Mysql, types of comments

Question

I had already realized that mysql, maybe not even the only case, but I would like an explanation on top of this to be more objective.

I have several types of comments that are accepted:

1) -- ESTA LINHA ESTA COMENTADA ?

2) # ESTA LINHA ESTA COMENTADA ?

3) /*ESTA LINHA ESTA COMENTATA 
   E CONTINUA COMENTADA*/

4) /*!40101 SET character_set_client = utf8 */;

I know I can use both option 1 and 2 to comment on a single line and 3 and 4 for multiple lines but I still have to understand other details.

My question is/are:

• Using -- or # makes a difference, have some recommendation when using one or the other?
• Are the instructions contained in 4) executed even commented? If yes, what is the reason for placing them inside the comment if it is possible to do them outside it?

Answer 1

As @Pauloroberto mentioned and referenced in his answer, the four types of comment work, but only the comment with -- is ANSI SQL; other Dbms do not accept other types of comments.

I recommend, therefore, that you avoid using the other comment formats for code that you write yourself: the day you want to migrate to something more harsh (and you going you will need to migrate to something more serious the day you want to do something more complicated), you will need to tweak less code.

---

As for comments with !40101, they are conditional comments: they are processed only by versions of Mysql larger or equal to the given number (in the example you gave, the SET will only be run by versions of Mysql 4.1.1); earlier versions will ignore the content of the comment (and, I presume, mess up all accented characters in the entry contained in the rest of the mysql_dump).

They have an important trick: they don’t work inside stored procedures, since the parser discards comments; if you want to run commands conditioned to the Mysql version, you’ll need to do something smarter, probably involving dynamic SQL.

Answer 2

The Mysql documentation has an entire chapter dedicated to this and this in English, has specific snippets and shows how, why and where to use each type of comment.

Mysql server supports comment styles # at the end of the line, -- end of line and /* end of line or multiple line */

mysql> select 1+1; # This comment continues to the end of the line mysql> select 1+1; -- This command continues to the end of the line mysql> select 1 /* This is a line comment / + 1; mysql> select 1+ / This is a multi-line commentary */ 1; Note that the comment style -- requires at least one space after code --!

Although the server understands the comment syntax described here, there are some limitations in the way the mysql client parses the commenting /* ... */:

Single quote and double quote characters are used to indicate the beginning of a quote string, even inside a comment. If the quotation marks do not match a second quotation mark inside the comment, the parser does not realize that the comment has an end. If you are running mysql interactively, you may notice the confusion occurred because of the change of the mysql> prompt to '> or ">.

A semicolon is used to indicate the end of an SQL statement and anything that comes after it indicates the beginning of the next instruction.

These limitations apply to both when running mysql interactively as when placing the commands in a file and asks mysql to read the entries of this file with the mysql command < some-file.

Mysql supports SQL-99 '--' comment style only if the second dash is followed by space See more information about this in the Section 1.8.4.7, "'-' as Comment Start".

6.1.6. Comment Syntax 1.8.4.7. '-' as Comment Start