Formatting MySQL with SQLinForm

MySQL is one of the most widely used databases in the world, and it has been part of SQLinForm since day one. Formatting MySQL correctly is more challenging than it might appear — here is what makes it complex.

---

Challenges Formatting MySQL SQL

The DELIMITER keyword

The DELIMITER keyword is unique to MySQL client tools and is not standard SQL. SQLinForm ignores DELIMITER declarations — it is not parsed or tracked. This means scripts that use a custom delimiter like // should have the DELIMITER lines removed before formatting, leaving only the standard SQL and stored procedure bodies. In practice, most developers format individual procedures rather than full client scripts, so this is rarely an issue.

-- Format this (without the DELIMITER lines):
CREATE PROCEDURE update_stock(IN product_id INT,
                               IN qty        INT)
BEGIN
    UPDATE products
    SET    stock = stock - qty
    WHERE  id = product_id;
END

BEGIN…END blocks without semicolons at the procedure level

In MySQL stored procedures, the outermost BEGIN…END that wraps the procedure body is not terminated with a semicolon. Only the inner statements are. This asymmetry means the formatter cannot treat all BEGIN…END pairs the same way — it requires careful context awareness to indent correctly.

CREATE PROCEDURE example()
BEGIN                      -- no semicolon here
    SET @x = 1;            -- semicolon required
    SELECT @x;             -- semicolon required
END                        -- no semicolon here either

DECLARE HANDLER and nested BEGIN blocks

Exception handling in MySQL uses DECLARE CONTINUE HANDLER and DECLARE EXIT HANDLER, which can appear inside BEGIN blocks alongside regular statements. Determining the correct indentation level for these constructs — especially when they contain their own BEGIN…END blocks — requires understanding the nesting hierarchy rather than just matching keywords.

CREATE PROCEDURE safe_insert(IN val INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred' AS message;
    END;

    START TRANSACTION;
    INSERT INTO log_table VALUES (val, NOW());
    COMMIT;
END

CASE in procedural vs. query context

MySQL’s CASE statement appears in two very different contexts: as a flow control statement inside stored procedures (which ends with END CASE) and as a CASE expression inside queries (which ends with END). The formatter must determine from context which form is being used to apply the correct indentation and line break logic.

-- CASE as flow control (stored procedure) -- ends with END CASE
CASE status
    WHEN 1 THEN SET label = 'Active';
    WHEN 2 THEN SET label = 'Inactive';
    ELSE        SET label = 'Unknown';
END CASE;

-- CASE as expression (query) -- ends with END
SELECT CASE status
           WHEN 1 THEN 'Active'
           WHEN 2 THEN 'Inactive'
           ELSE        'Unknown'
       END AS label
FROM   customers;

SIGNAL and RESIGNAL

The SIGNAL and RESIGNAL statements for error handling have a multi-part syntax with SET clauses. Formatting them consistently — especially across complex exception handler blocks — requires careful handling to avoid misaligned continuation lines.

CREATE PROCEDURE check_age(IN age INT)
BEGIN
    IF age < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Age cannot be negative',
                MYSQL_ERRNO  = 1001;
    END IF;
END

Mixed DDL and DML in migration scripts

Real-world MySQL scripts often mix DDL (CREATE TABLE, ALTER TABLE, DROP TABLE) with DML and procedural code. Recognizing statement boundaries without semicolons between DDL blocks, especially when those blocks appear inside IF NOT EXISTS guards, is one of the harder challenges in MySQL formatting.

DROP TABLE IF EXISTS orders_archive;

CREATE TABLE orders_archive (
    id         INT           NOT NULL,
    order_date DATE          NOT NULL,
    amount     DECIMAL(10,2)
);

INSERT INTO orders_archive
SELECT id,
       order_date,
       amount
FROM   orders
WHERE  order_date < '2023-01-01';

---

About SQLinForm: SQLinForm is a SQL formatter supporting 25+ database dialects with 200+ formatting options — available for VS Code, SSMS, JetBrains, Notepad++, DBeaver, and as an online formatter. Your SQL never leaves your machine.