Comments in MySQL

1. Introduction

Comments are essential in any software code, and SQL is no exception. They help document the query, provide context, and can even temporarily turn off parts of the code. Comments can be single-line, a part of a line, or span multiple lines.

In this tutorial, we’ll look at various types of comments in MySQL and discuss when to use each type. We’ll use MySQL 8 for the sample queries. However, everything should work the same in other versions as well.

2. Single-Line Comments

Single-line comments are the most commonly used in SQL. They comment out everything from their starting point to the end of the line.

MySQL supports two types of single-line comments: those starting with — as in ANSI SQL, and those beginning with #.

2.1. Using —

ANSI SQL supports single-line comments using the special syntax –. Any text following — until the end of the line is treated as a comment and isn’t executed.

Let’s look at a simple usage:

-- This query returns all Students in the University
SELECT * FROM Student;

In the example above, we used a comment to explain the query.

Instead of commenting from the start of the line, it’s also possible to comment out text from within a line:

SELECT * FROM Student -- WHERE NAME = 'John'

In this case, the WHERE clause has been commented out. However, it’s important to note that — must be followed by a space character. Otherwise, the subsequent text won’t be treated as a comment.

2.1. Using #

In addition to the ANSI Standard comment syntax, MySQL supports single-line comments using the # symbol:

# This query shows all the students
SELECT * from Student;

Similarly, we can also begin a comment in the middle of a line:

SELECT * FROM Student # WHERE NAME = 'John'

This effectively comments out the WHERE condition from the query.

Unlike the — comment, # comments work without a space character afterward.

However, this isn’t part of the ANSI SQL standard. So, migrating queries to another database system may be challenging and require additional preprocessing if we use a lot of these comments.

3. Block Comments

Unlike single-line comments, which comment out from the point of occurrence until the end of the line, block comments in MySQL can span multiple lines or be used inline within the same line.

Like in many programming languages, block comments in MySQL are enclosed within /* and  */.

3.1. Inline Use

Let’s explore how we can use block comments as inline comments:

SELECT * FROM Student WHERE NAME /* wild card matching for name */ LIKE '%John%';

In this query, we included an inline comment just before the LIKE keyword.

Used this way, block comments are the same as single-line comments.

3.2. Multi-Line Comments

Using /* */ for multi-line comments is part of the ANSI SQL standard:

SELECT * FROM Student WHERE NAME 
/* wild card matching for name.
Change it based on the requirement
*/ 
LIKE '%John%';

These comments are useful for temporarily turning off large sections of SQL code during the development and testing phases. Further, they can provide detailed annotations within the script.

3.3. Block Comment Variants

MySQL provides a variant of multi-line comments that are treated differently. Let’s look at an example:

SELECT /*! STRAIGHT_JOIN */ *
FROM Student /*+ USE INDEX (PRIMARY) */
WHERE id = 1001;

In this query, we use /*! … */ and /*+ … */ as special comments.

The code block /*! … */ denotes a valid query part in MySQL but is treated as a regular comment in other databases.

Further, we provide query optimization hints using /*+ … */, which guide the query optimizer in choosing the best execution plan. This allows us to specify optimization mechanisms exclusively for MySQL, as other databases will treat these hints as regular comments and ignore them.

4. Conclusion

In this article, we examined various approaches to commenting within MySQL. While adhering to ANSI standards for comment syntax, MySQL offers additional methods to enhance flexibility and manage comments effectively within SQL scripts.