Commenting in MySQL:
Syntax, Best Practices, and Examples

When it comes to writing clear and maintainable SQL code, comments can enhance code readability by explaining the purpose or logic behind specific statements or queries, as well as the structure of stored procedures and scripts. For example, in long-term projects, developers, data analysts, or database administrators can rely on comments to understand, review, and troubleshoot code, and to document and clarify complex JOINs or queries.

dbForge Studio for MySQL, a MySQL GUI, simplifies this process by providing intelligent code formatting, making it easy to add, manage, and maintain inline and block comments across your scripts. with the advanced SQL Editor syntax highlighting and quick navigation, teams can collaborate more efficiently ensuring code remains well-documented and readable over time.

Understanding MySQL comments

MySQL comments serve as notes added before, after, or within a SQL statement, or at the beginning of a script or stored procedure. They are used to explain the context of a query or provide additional information for readers or reviewers. The MySQL engine ignores comments and doesn't execute or process them, meaning comments have no impact on query execution.

In MySQL, you can add comments to different pieces of code, including:

• SQL statements, for example, to document the purpose of SELECT, INSERT, UPDATE, or DELETE
• Table definitions, for example, to provide context for columns or constraints in CREATE TABLE or ALTER TABLE
• Stored procedures, functions, and triggers, for example, to explain logic or expected behavior inside routines
• Complex JOINs and subqueries, for example, to clarify logic behind nested queries and JOINs
• Temporary logic or debugging steps, for example, to temporarily turn off some parts of code for testing purposes
• Entire script sections, for example, to mark sections for better organization or to use headers
• Configuration and environment setup, for example, to document settings, variables, or environment requirements
• Tables, columns, indexes, partitions, or subpartitions, for example, to describe their structures

Types of comments in MySQL

MySQL supports two types of comments:

• Single-line comments written on a single line
• Multi-line comments entered on multiple lines of code

Single-line comments

If you want to annotate your code or add a brief note, you can use a single-line comment. It starts with double hyphens --, followed by your comment. Everything after double hyphens till the end of the line is considered as a comment and ignored by the MySQL server. In addition, this type of comment is supported by most SQL clients and tools, making it a safe choice for cross-platform scripting.

A syntax example is as follows:

SELECT * FROM employees;  -- This query retrieves all employee records 

Another way to write short explanations within a SQL query is to use single-line comments that begin with a hash symbol #. They are usually placed on their line or at the end of a line of code, followed by your comment.

Here is a syntax example:

# Select all employees in the Sales department
SELECT * 
FROM employees 
WHERE department = 'Sales';

Multi-line comments

When you need to temporarily turn off code blocks during debugging or testing or document complex logic in scripts or stored procedures, multi-line comments may be suitable. They are enclosed with the /* and */ symbols. Everything inside those symbols is treated as a comment and ignored during execution.

The syntax is as follows:

/* Retrieve all employees who joined after 2020
   and belong to the Marketing department */
SELECT * 
FROM employees
WHERE join_date > '2020-01-01'
  AND department = 'Marketing';

Best practices for commenting in MySQL

Let's share some tips that can help improve code readability and maintainability when writing comments.

Clarity and conciseness

It is recommended to write clear, easy-to-understand comments, with no fluff. They should explain why the code is written that way instead of describing what the SQL does. The latter should be clear from the code itself.

Consistency

It is a good practice to maintain a consistent style across all your SQL scripts and among the team. For example, choose -- instead of # for single-line comments and /* ... */ for blocks, and apply it uniformly. In addition, you can consider using internal style guides or including commenting conventions in your development documentation.

Avoiding sensitive information

Regarding sensitive or confidential information, such as passwords and personal identifiers, never use it in your comments. For example, when you use version control or make a backup, this information can be saved, and anyone who has access to it may see those details.

Consider some additional tips:

• Explain any non-obvious JOINs, subqueries, or calculations that may confuse readers
• Avoid an excessive number of comments in your code
• Keep comments up-to-date with code changes
• Write comments at the top of procedures or complex views to describe their purpose, parameters, and expected results
• Use TODO or FIXME to flag places that need further work, review, or optimization

Adding comments to MySQL database structures

Now, let's take a closer look at how to comment different database objects, including tables, columns, indexes, partitions, subpartitions, procedures, functions, and events. This section also includes examples to illustrate each case.

Adding comments to table definitions

In MySQL, you can write comments for tables using the COMMENT clause as part of the CREATE TABLE or ALTER TABLE statement.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
COMMENT = 'Your table comment here';

where table_name is the table to which you want to add a comment.

Example

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100)
) COMMENT = 'Stores information about company employees';

The comment here explains the purpose of the CREATE TABLE statement.

Adding comments to column definitions

To add comments to individual columns, use the COMMENT clause as part of the column definition.

Syntax

column_name datatype COMMENT 'Your comment here'

where column_name is the column in which you want to enter the comment.

Example

CREATE TABLE products (
    product_id INT PRIMARY KEY COMMENT 'Unique identifier for each product',
    product_name VARCHAR(100) COMMENT 'Name of the product',
    price DECIMAL(10,2) COMMENT 'Retail price in USD',
    in_stock BOOLEAN COMMENT 'Availability status'
);

If you want to view the comments, use the SHOW CREATE TABLE or SHOW FULL COLUMNS statements.

Adding comments to partitions and subpartitions

Similar to tables and columns, you can add comments to partitions or subpartitions using the same approach. To add the comment, use the COMMENT clause within the PARTITION BY section of a CREATE TABLE statement.

Syntax - Adding comments to partitions

CREATE TABLE table_name (
    column_name datatype
)
PARTITION BY partition_type(partition_expression) (
    PARTITION partition_name VALUES LESS THAN (value)
    COMMENT 'partition_comment',
    ...
);

Syntax - Adding comments to subpartitions

CREATE TABLE table_name (
    column_name datatype
)
PARTITION BY partition_type(partition_expression)
SUBPARTITION BY subpartition_type(subpartition_expression)
SUBPARTITIONS num_subpartitions (
    PARTITION partition_name VALUES LESS THAN (value)
    COMMENT 'partition_comment' (
        SUBPARTITION subpartition_name COMMENT 'subpartition_comment',
        ...
    ),
    ...
);

Example

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023) COMMENT 'Sales data for 2022',
    PARTITION p2023 VALUES LESS THAN (2024) COMMENT 'Sales data for 2023',
    PARTITION p_future VALUES LESS THAN MAXVALUE COMMENT 'Future sales'
);

You should add the COMMENT clause directly after the VALUES LESS THAN (...) clause. If the table uses subpartitioning, you can apply comments to each PARTITION or SUBPARTITION. In addition, the comments are stored in metadata queries. To view comments, execute the SHOW CREATE TABLE query.

Adding comments to index definitions

You can also place descriptive comments for indexes using the COMMENT clause in CREATE INDEX or within the index definition in a CREATE TABLE statement. This may be useful when you need to clarify the usage of each index, for example, in complex schemas.

Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...)
COMMENT 'Description of the index';

where index_name is the name of the index to which you want to add the comment, and table_name is the name of the table for which the index is created.

Example

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE INDEX idx_customer_order
ON orders (customer_id, order_date)
COMMENT 'Speeds up lookups by customer and date';

The comment will be stored in the information schema. To view the comment, use SHOW INDEX or information_schema.STATISTICS.

Adding comments to procedures, functions, and events

Finally, you can add comments to stored procedures, functions, and events just as you do for tables, columns, and indexes—by using the COMMENT clause in the CREATE or ALTER statements. The comments will be stored in the database metadata.

Syntax

COMMENT 'your_comment'

Example of a stored procedure

CREATE PROCEDURE getAllOrders()
COMMENT 'Retrieves all orders from the orders table'
BEGIN
    SELECT * FROM orders;
END;

Example of a function

CREATE FUNCTION getDiscount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
COMMENT 'Calculates a 10% discount on the input price'
DETERMINISTIC
BEGIN
    RETURN price * 0.9;
END;

Example of an event

CREATE EVENT clear_temp_data
ON SCHEDULE EVERY 1 DAY
COMMENT 'Clears temporary data daily at midnight'
DO
  DELETE FROM temp_table;

You can view comments by executing queries against information_schema.ROUTINES (for procedures and functions) or information_schema.EVENTS (for events).

Utilizing comments for code debugging

In addition to explaining your code logic or the purpose of the query, comments are also a common debugging technique to temporarily turn off code blocks without deleting them. For example, if you need to check the behavior or output of part of a query or stored procedure, commenting out specific sections can help isolate issues. You can also use comments to test alternative code logic, preserve original code when making changes, or avoid accidental execution errors when dealing with dependencies.

For instance, when troubleshooting data inconsistencies or performance bottlenecks, you can comment out entire queries or individual code blocks. This allows you to test different conditions, such as turning off an active filter, to better understand the query behavior.

-- Full query with all filters
-- SELECT * FROM customers WHERE country = 'USA' AND active = 1;

-- Debug version: testing without the 'active' filter
SELECT * FROM customers WHERE country = 'USA';

Enhancing productivity with dbForge Studio for MySQL

Let's see how dbForge Studio for MySQL, a comprehensive IDE for MySQL and MariaDB database development and management, can assist in managing comments. The Studio enhances your productivity, improves your user experience, and helps you handle database tasks of any complexity. With its intuitive graphical user interface and rich feature set, this IDE is suitable for both experienced developers and beginners.

With dbForge Studio, you can easily manage comments and thus improve code readability:

• Use Ctrl+K, C to comment or Ctrl+K, U to uncomment the selected piece of the code or the one on which the cursor is placed
• Validate your code and detect errors to immediately fix them using syntax highlighting
• Keep or improve comment layout with SQL Formatter when formatting code
• Hover over your code or place a cursor on it to view object information in an object definition tooltip
• Write your code without errors and avoid typos or errors by using the code completion context-based query hints
• Unify documentation standards with code snippets or templates containing predefined comment sections
• ... and much more!

Conclusion

Proper and accurate commenting is important for maintaining consistency and readability in your code. Comments help you explain the logic behind queries and make it easier to understand for others. The article introduced the fundamentals of code commenting in MySQL, including comment syntax rules and comment types.

To keep your code clean and aligned with best practices, you can follow the tips outlined in the article. For easier and more efficient management of database-related tasks, consider using dbForge Studio for MySQL - an advanced IDE for MySQL and MariaDB development, administration, and deployment.