MySQL Comments

Comments are essential components of well-written SQL code. They help document your queries, explain complex logic, and provide valuable context for yourself and other developers. In MySQL, comments are non-executable text that the database engine ignores during query processing.

Why Use Comments in MySQL?

Comments serve several important purposes in your MySQL code:

• Documentation: Explain what your queries do and why
• Code organization: Separate logical sections of complex queries
• Debugging: Temporarily disable parts of a query without deleting them
• Collaboration: Help team members understand your thinking
• Future reference: Help your future self remember your implementation decisions

Types of MySQL Comments

MySQL supports three different comment styles:

1. Single-line comments (using -- or #)
2. Multi-line comments (using /* ... */)
3. Special MySQL-specific comments

Let's explore each type in detail.

Single-Line Comments

Single-line comments extend from the comment marker to the end of the line. MySQL supports two different single-line comment syntaxes:

Using Double Dash (--)

The double dash style is the ANSI SQL standard for comments. When using double dash, you must include a space after the dashes.

sql

-- This is a single-line comment using double dash
SELECT * FROM customers; -- This comment appears after a statement

Using Hash/Pound Symbol (#)

The hash symbol is specific to MySQL and works without requiring a space after it.

sql

# This is a single-line comment using hash symbol
SELECT * FROM products; # This retrieves all products

Multi-Line Comments

For longer comments spanning multiple lines, use the multi-line comment syntax with /* to start and */ to end the comment block:

sql

/* This is a multi-line comment
   that can span across several lines
   to explain complex queries or logic */
SELECT 
    customer_id,
    first_name,
    last_name
FROM customers
WHERE status = 'active';

Special MySQL Comments

MySQL has special comment syntax for handling optimizer hints and version-specific code. These comments are processed by MySQL in specific ways:

MySQL-Specific Comment Hints

sql

SELECT /*+ INDEX(employees idx_employee_name) */ 
    first_name, 
    last_name 
FROM employees;

This special comment provides a hint to the MySQL optimizer to use a specific index.

Version-Specific Comments

sql

/*!50616 ALTER TABLE customers ADD COLUMN phone_number VARCHAR(20) */;

The comment above will only execute in MySQL version 5.06.16 or higher. In earlier versions, it's treated as a comment and ignored.

Practical Examples of MySQL Comments

Documenting Complex Queries

sql

/* 
 * Customer Revenue Analysis
 * Purpose: Calculate monthly revenue by customer segment
 * Created: 2023-06-15
 * Author: Jane Doe
 */
SELECT 
    c.segment,
    YEAR(o.order_date) AS year,
    MONTH(o.order_date) AS month,
    SUM(oi.quantity * oi.unit_price) AS monthly_revenue
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
JOIN order_items oi 
    ON o.order_id = oi.order_id
WHERE 
    o.order_date >= '2023-01-01' -- Start of current year
    AND o.status = 'completed'   -- Only count completed orders
GROUP BY 
    c.segment, 
    YEAR(o.order_date),
    MONTH(o.order_date)
ORDER BY 
    c.segment,
    year,
    month;

Commenting Out Code for Debugging

sql

SELECT 
    product_id,
    product_name,
    unit_price,
    /* Temporarily removing category filter for testing
    category_id = 5 AND */
    stock_quantity > 0
FROM products
WHERE unit_price BETWEEN 10 AND 50;

Explaining JOIN Logic

sql

SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
/* INNER JOIN ensures we only get orders with matching customers
   This excludes any orphaned orders without customer records */
INNER JOIN customers c 
    ON o.customer_id = c.customer_id
/* LEFT JOIN to order_items ensures we include orders without items
   which might indicate abandoned or problem orders */
LEFT JOIN order_items oi 
    ON o.order_id = oi.order_id
GROUP BY 
    o.order_id, 
    o.order_date, 
    c.customer_name;

Documenting Table Structure Changes

sql

-- Add new field for storing customer loyalty points
ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;

/* 
 * Update retention policy to set deletion_flag instead of removing records
 * Requested by: Legal department
 * Ticket #: GDPR-2023-15
 */
ALTER TABLE user_logs 
ADD COLUMN deletion_flag BOOLEAN DEFAULT FALSE,
ADD COLUMN deletion_date DATETIME NULL;

Comments in Database Design

When creating database objects like tables, views, and stored procedures, comments help document your schema design decisions:

Comments in CREATE TABLE Statements

sql

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL, -- Legal name as shown on ID
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL, -- Formal start date from HR
    department_id INT,
    salary DECIMAL(10,2), /* Stored in base currency (USD) 
                            Converted for reporting when needed */
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Comments in Stored Procedures

sql

DELIMITER //

CREATE PROCEDURE update_inventory(
    IN product_id INT,
    IN quantity INT
)
BEGIN
    /* This procedure updates inventory levels for a product
       and logs the change in inventory_transactions table
       
       Parameters:
       - product_id: The product to update
       - quantity: Can be positive (add stock) or negative (remove stock)
    */
    
    -- Update the main inventory table
    UPDATE products 
    SET stock_quantity = stock_quantity + quantity
    WHERE product_id = product_id;
    
    -- Log the transaction
    INSERT INTO inventory_transactions (
        product_id, quantity_changed, transaction_date
    ) VALUES (
        product_id, quantity, NOW()
    );
END //

DELIMITER ;

Best Practices for MySQL Comments

Follow these guidelines to make the most of comments in your MySQL code:

1. Be concise but clear: Write comments that add value without excessive verbosity.

2. Use consistent style: Standardize your comment style (e.g., always use -- for single-line comments).

3. Comment complex logic: Focus on explaining "why" rather than "what" when the SQL is complex.

4. Update comments when code changes: Outdated comments are worse than no comments.

5. Include metadata: Add information like dates, author names, and ticket references for significant changes.

6. Group related comments: Use comments to create logical sections in longer scripts.

7. Don't over-comment: Simple, self-explanatory SQL doesn't need commenting.

Common Mistakes with MySQL Comments

Avoid these common pitfalls when using comments:

Nested Multi-line Comments

MySQL doesn't support nested multi-line comments. This will cause errors:

sql

/* Outer comment start
   /* Nested comment - will cause problems */
   Still in outer comment */
SELECT * FROM users;

Missing Spaces After Double Dash

When using the -- syntax, always include a space after the dashes:

sql

--This won't work as a comment (no space after --)
-- This will work correctly (space after --)

Using Comments in the Wrong Context

Some MySQL client tools handle comments differently. For example, in mysqldump output or when using MySQL Workbench's query tabs.

Summary

Comments are a powerful tool for making your MySQL code more maintainable, understandable, and collaborative. By using single-line comments (-- or #), multi-line comments (/* */), and special MySQL-specific comments, you can effectively document your database code.

Remember that well-commented code is a gift to your future self and your team members. Take the time to document complex queries, schema design decisions, and the reasoning behind your implementation choices.

Exercises

1. Convert the following multi-line comment to single-line comments:

   sql

   /* This query retrieves 
      active users who
      registered in the past 30 days */

2. Add appropriate comments to document this query:

   sql

   SELECT p.category_id, c.category_name, COUNT(*) as product_count, 
   AVG(p.unit_price) as avg_price FROM products p 
   JOIN categories c ON p.category_id = c.category_id
   WHERE p.discontinued = 0
   GROUP BY p.category_id, c.category_name
   HAVING COUNT(*) > 5;

3. Write a properly commented CREATE TABLE statement for a blog post table that includes fields for title, content, author_id, publish_date, and status.

4. Modify the following query to temporarily disable the WHERE clause using comments:

   sql

   SELECT customer_id, SUM(order_total) FROM orders
   WHERE order_date >= '2023-01-01'
   GROUP BY customer_id;

Additional Resources

• MySQL Documentation on Comments
• SQL Style Guide
• Clean Code principles applied to SQL