MySQL - Comments

MySQL: Using Comments in Your SQL

Table of Content

- The Role of Comments in SQL
- Single-Line Comments
- Multi-line Comments
- Best Practices for Placing Comments
- Using Comments in Client Programs

The Role of Comments in SQL

Comments are non-executable text within your SQL code used to document and explain its logic. The database engine ignores them entirely. They are crucial for maintainability, helping other developers (and your future self) understand the purpose of complex queries, stored procedures, or specific lines of code.

MySQL supports three styles of comments.

Single-Line Comments

Single-line comments are used for brief notes. They start with two hyphens (-- ) or a hash symbol (#) and continue to the end of the line. Note: The -- style requires a space after the two hyphens to be standard SQL, although MySQL is often lenient.

Example

-- Fetch all active users from the customers table.
SELECT *
FROM customers
WHERE is_active = 1; # We only want currently active customers

-- The following query is temporarily disabled for testing.
-- SELECT * FROM old_customers;

Multi-line Comments

Multi-line comments, also known as block comments, are used for longer explanations or to comment out a block of code. They begin with /* and end with */. Everything between these markers is ignored.

Example

Here, a multi-line comment is used to explain a more complex query and to temporarily comment out a part of the SELECT clause.

/*
  This query calculates the total order value for each customer
  in the last quarter. It joins the customers and orders tables.
  - Author: J. Doe
  - Last Modified: 2023-10-27
*/
SELECT
    c.customer_name,
    /* c.customer_email, -- Temporarily removed for privacy review */
    SUM(o.order_value) AS total_spent
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    o.order_date >= CURDATE() - INTERVAL 3 MONTH
GROUP BY
    c.customer_id, c.customer_name;

Best Practices for Placing Comments

You can place comments almost anywhere in your SQL code. Effective commenting focuses on the ‘why’, not the ‘what’.

• At the top of a script or procedure: Use a block comment to describe its overall purpose, author, and revision history.
• Before a complex statement: Explain the business logic or goal of a complicated query.
• Inline to clarify a specific part: Use a single-line comment to explain a tricky WHERE condition or a non-obvious calculation.

/*
  Stored Procedure: GetHighValueCustomers
  Purpose: Finds all customers with a lifetime value over a specified threshold.
*/
DELIMITER //
CREATE PROCEDURE GetHighValueCustomers(IN threshold DECIMAL(10, 2))
BEGIN
    -- The subquery calculates the lifetime value for each customer.
    SELECT customer_id, total_spent
    FROM (
        SELECT customer_id, SUM(order_value) as total_spent
        FROM orders
        GROUP BY customer_id
    ) AS customer_totals
    WHERE total_spent > threshold; -- Filter based on the input threshold.
END //
DELIMITER ;

Using Comments in Client Programs

Since comments are part of the SQL string, you can include them when executing queries from any programming language. This is particularly useful for debugging, as you can see the documented query in your database logs.

Example: Commenting out columns dynamically

In this example, we build a query that conditionally includes or comments out columns based on application logic.

Python
Node.js
PHP
Java



**Setup:** `pip install mysql-connector-python`

```python
import mysql.connector

# --- Config ---
db_config = { 'host': 'localhost', 'user': 'root', 'password': 'password', 'database': 'TUTORIALS' }

# --- Logic ---
include_sensitive_data = False

sensitive_cols = "c.email, c.phone" if include_sensitive_data else "/* c.email, c.phone -- Excluded */"

query = f"""
    -- Fetch customer data
    SELECT
        c.id,
        c.name,
        {sensitive_cols}
    FROM
        CUSTOMERS c
    WHERE
        c.city = %s;
"""

try:
    with mysql.connector.connect(**db_config) as conn:
        with conn.cursor(dictionary=True) as cursor:
            cursor.execute(query, ('Mumbai',))
            results = cursor.fetchall()
            print("Query executed successfully. Records found:")
            for row in results:
                print(row)
except mysql.connector.Error as e:
    print(f"Error: {e}")

Setup: npm install mysql2

const mysql = require('mysql2/promise');

// --- Config ---
const dbConfig = { host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS' };

// --- Logic ---
const includeSensitiveData = false;

const sensitiveCols = includeSensitiveData ? "c.email, c.phone" : "/* c.email, c.phone -- Excluded */";

const query = `
    -- Fetch customer data
    SELECT
        c.id,
        c.name,
        ${sensitiveCols}
    FROM
        CUSTOMERS c
    WHERE
        c.city = ?;`;

async function runQuery() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(query, ['Mumbai']);
        console.log('Query executed successfully. Records found:');
        console.log(rows);
    } catch (error) {
        console.error(`Error: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

runQuery();

Setup: Use Composer for dependencies.

<?php
// --- Config ---
$dbConfig = ['host' => 'localhost', 'user' => 'root', 'password' => 'password', 'database' => 'TUTORIALS'];

// --- Logic ---
$includeSensitiveData = false;

$sensitiveCols = $includeSensitiveData ? "c.email, c.phone" : "/* c.email, c.phone -- Excluded */";

$query = "
    -- Fetch customer data
    SELECT
        c.id,
        c.name,
        {$sensitiveCols}
    FROM
        CUSTOMERS c
    WHERE
        c.city = ?;";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli($dbConfig['host'], $dbConfig['user'], $dbConfig['password'], $dbConfig['database']);
    $stmt = $mysqli->prepare($query);
    $city = 'Mumbai';
    $stmt->bind_param('s', $city);
    $stmt->execute();
    $result = $stmt->get_result();

    echo "Query executed successfully. Records found:\n";
    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }
} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

?>

Setup: Add MySQL JDBC driver to your project (e.g., via Maven).

import java.sql.*;

public class CommentsInQuery {
    public static void main(String[] args) {
        // --- Config ---
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";

        // --- Logic ---
        boolean includeSensitiveData = false;

        String sensitiveCols = includeSensitiveData ? "c.email, c.phone" : "/* c.email, c.phone -- Excluded */";

        String query = String.format("""
            -- Fetch customer data
            SELECT
                c.id,
                c.name,
                %s
            FROM
                CUSTOMERS c
            WHERE
                c.city = ?;""", sensitiveCols);

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(query)) {

            pstmt.setString(1, "Mumbai");
            ResultSet rs = pstmt.executeQuery();

            System.out.println("Query executed successfully. Records found:");
            while(rs.next()){
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}