DbVisualizer

Posted on Oct 20

Commenting in MySQL: Syntax, Hints, and Practical Examples

#mysql #sql

SQL comments do more than explain — they document reasoning, help debugging, and prevent mistakes. MySQL gives you flexibility: single-line -- or #, multi-line /* */, and even executable /*!...*/ blocks for version control or optimizer hints.

Whether you’re annotating scripts or managing migrations, mastering these forms helps you write cleaner and more maintainable SQL.

Basic Syntax

-- single line comment
# alternative single line
/* multi-line comment
   used for explanations */

Comments are skipped during execution and won’t affect performance.

Versioned Comments and Hints

MySQL adds its own flavor:

/*!80000 SET sql_safe_updates = 1 */;
SELECT /*+ NO_RANGE_OPTIMIZATION(orders) */ * FROM orders;

The first executes only on servers version 8.0.0 and above; the second provides optimizer hints to control query behavior.

Practical Examples

Disable a filter without deleting it

SELECT * FROM products
-- WHERE stock < 10
ORDER BY price DESC;

Annotate complex joins

SELECT /* join with discounts to include seasonal pricing */
       p.name, d.rate
FROM products p
LEFT JOIN discounts d ON p.id = d.product_id;

Explain data cleanup

/* remove users who haven’t logged in for 2 years */
DELETE FROM users
WHERE last_login < NOW() - INTERVAL 2 YEAR;

Include comments in stored procedures

DELIMITER //
CREATE PROCEDURE clear_logs()
BEGIN
  /* Only clear logs older than 90 days */
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 90 DAY;
END //
DELIMITER ;

Versioned change for schema upgrades

/*!80024 ALTER TABLE employees ADD COLUMN hire_source VARCHAR(50) */;

Hint for optimizer

SELECT /*+ INDEX(orders idx_customer_date) */ *
FROM orders
WHERE customer_id = 10;

Best Practices

Keep comments short and relevant.
Use /* */ for multi-line explanations, - for brief notes.
Use versioned blocks (/*! */) for safe, cross-version migrations.
Avoid secrets in comments.
Regularly review and remove outdated notes.

FAQ

How do I write comments in MySQL?

Use -- or # for single lines, or /* ... */ for multi-line comments.

What are versioned comments?

/*!80000 ... */ runs only if MySQL’s version matches or exceeds the number.

Can I use optimizer hints inside comments?

Yes, hints like /*+ STRAIGHT_JOIN */ influence how the query optimizer behaves.

Do comments affect runtime?

No. They are stripped during parsing and ignored by the engine.

Conclusion

Comments transform plain SQL into maintainable, understandable code. MySQL extends standard syntax with version-specific and optimizer hints that give you more control. Whether you’re writing data migrations, stored procedures, or analytical queries, thoughtful commenting keeps your work clear and future-proof.

For full syntax details and advanced examples, read the Commenting in MySQL: Definitive Guide.

DEV Community