How MySQL text is read before formatting
MySQL bends several lexical rules that other databases keep strict, so the dialect setting changes how your query is split into tokens before any line breaks are chosen:
- Backticks quote identifiers:
`order`,`created at`. Under PostgreSQL or Oracle a backtick is an error. - Single and double quotes both make strings.
"Berlin"is a value in MySQL, not a column name. - Backslash escapes work inside strings, so
'O\'Brien'is one complete literal. Doubled quotes ('O''Brien') also work. - Comments can start with
#,--or/* */. Block comments do not nest. - Executable comments such as
/*!40001 SQL_NO_CACHE */and optimizer hints like/*+ MAX_EXECUTION_TIME(1000) */are recognised as hints. - Variables and placeholders:
@cutoff,@@session.sql_modeand the?placeholders used by prepared statements and most connectors stay intact.
Charset introducers (_utf8mb4'café'), hex literals (x'4F4B') and bit literals (b'0101') are kept as single tokens too.
MySQL-only clauses
ON DUPLICATE KEY UPDATE starts its own clause, with each assignment on its own line. One quirk: the old VALUES(qty) function inside the update list is mistaken for a new VALUES clause and gets split across lines. The row alias form introduced in MySQL 8.0.19 (VALUES (...) AS new ON DUPLICATE KEY UPDATE qty = inventory.qty + new.qty) formats cleanly and is also the form MySQL now recommends.
LIMIT 40, 20 keeps the offset-comma-count order exactly as you wrote it, and LIMIT 20 OFFSET 40 puts OFFSET on its own line. The JSON operators -> and ->> get a space on each side. Table options after a CREATE TABLE column list (ENGINE = InnoDB, DEFAULT CHARSET = utf8mb4) stay on the closing-parenthesis line.
Stored procedures, triggers and DELIMITER
The DELIMITER command belongs to the mysql client and Workbench, not the server, and the formatter does not understand it: DELIMITER // comes out as DELIMITER / /, which no longer works. Delete the DELIMITER lines and end the routine with a plain semicolon before formatting, then put them back afterwards.
Inside a BEGIN ... END body, each statement is formatted on its own, but the body is not indented and the first DECLARE stays on the CREATE PROCEDURE line. The result is readable for review; tidy the routine header by hand if you commit it.
Options that suit MySQL code
Keyword case defaults to UPPER. Choose “As written” when you want to keep lower-case code from an ORM log. Indent style → Tabular, right-aligned lines keywords up in a right-justified column, a layout common in older MySQL codebases. Commas → Leading puts the comma first on each line, so adding a column to a SELECT touches one line in a diff.
Minify collapses a query onto as few lines as possible for a config file or a log grep. Strings, backtick identifiers and /*! */ executable comments are left untouched, so a minified dump-style statement still behaves the same on the server.
When to choose a different dialect
If your SQL runs on MariaDB, pick MariaDB: it knows RETURNING and system-versioned tables, which MySQL does not. The reverse mistake costs more. A MySQL query run through the PostgreSQL rules fails on 'O\'Brien', because the backslash no longer escapes the quote. Under Standard SQL, a leading # comment is rejected outright. For layout conventions such as alias naming and join order, see the SQL style guide.
Examples
Inventory upsert with a row alias
Shows ON DUPLICATE KEY UPDATE as its own clause, using the MySQL 8 row alias instead of the VALUES() function.
insert into `inventory` (`sku`, `warehouse_id`, `qty`) values ('SKU-1001', 3, 40), ('SKU-1002', 3, 12) as new on duplicate key update `qty` = `inventory`.`qty` + new.`qty`, updated_at = now();INSERT INTO
`inventory` (`sku`, `warehouse_id`, `qty`)
VALUES
('SKU-1001', 3, 40),
('SKU-1002', 3, 12) AS new
ON DUPLICATE KEY UPDATE
`qty` = `inventory`.`qty` + new.`qty`,
updated_at = now();
Paginated paid-orders report
A # comment, a backslash-escaped quote, the ->> JSON operator and LIMIT offset, count laid out with right-aligned keywords.
# page 3 of paid orders
select o.id, o.total, c.email, o.attrs->>'$.channel' as channel from orders o left join customers c on c.id = o.customer_id where o.status = 'paid' and c.last_name <> 'O\'Brien' order by o.created_at desc limit 40, 20;# page 3 of paid orders
SELECT o.id,
o.total,
c.email,
o.attrs ->> '$.channel' AS channel
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
AND c.last_name <> 'O\'Brien'
ORDER BY o.created_at DESC
LIMIT 40, 20;
Order items table definition
Each column and index of a CREATE TABLE gets its own line, with the table options kept after the closing parenthesis.
create table `order_items` (`id` bigint unsigned not null auto_increment, `order_id` bigint unsigned not null, `sku` varchar(32) not null, `qty` int not null default 1, primary key (`id`), key `idx_order` (`order_id`)) engine=InnoDB default charset=utf8mb4;CREATE TABLE `order_items` (
`id` BIGINT UNSIGNED NOT NULL auto_increment,
`order_id` BIGINT UNSIGNED NOT NULL,
`sku` VARCHAR(32) NOT NULL,
`qty` INT NOT NULL default 1,
PRIMARY KEY (`id`),
KEY `idx_order` (`order_id`)
) engine = InnoDB default charset = utf8mb4;
Minified query with hints
Minify drops the ordinary comment but keeps the optimizer hint, the executable comment and the double space inside the string.
select /*+ MAX_EXECUTION_TIME(1000) */ id,
total -- grand total
from orders /*!40001 SQL_NO_CACHE */
where customer_id = ?
and note = 'gift wrap';select/*+ MAX_EXECUTION_TIME(1000) */id,total from orders/*!40001 SQL_NO_CACHE */where customer_id= ?and note='gift wrap';Common errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
This string is never closed — the ' has no matching 'Explained | In MySQL a backslash escapes the next character, so a Windows path such as 'C:' swallows its own closing quote. | Double the backslash (‘C:\’) so it stands for one literal backslash. File paths and regex patterns are the usual culprits. |
"[Order" is not valid MySQL syntax | The query uses SQL Server square-bracket identifiers, which MySQL does not accept. | Replace [Order Total] with Order Total, or switch the Dialect to T-SQL (SQL Server) if that is where the query runs. |
"::int" is not valid MySQL syntax | The :: cast operator comes from PostgreSQL and has no meaning in MySQL. | Rewrite it as CAST(total AS SIGNED) or CAST(total AS DECIMAL(12,2)), or format the query as PostgreSQL. |
This quoted identifier is never closed — the ` has no matching ` | A backtick-quoted table or column name is missing its closing backtick, often after a copy from a log line. | Add the closing backtick at the end of the name; the error position points at the opening one. |
This '(' is never closedExplained | A function call, IN list or subquery has more opening than closing parentheses. | Count the brackets from the reported position and add the missing ) where the expression ends. |
Frequently asked questions
How do I format SQL in MySQL Workbench?
Workbench has a built-in Edit → Format → Beautify Query command (Ctrl+B) with few settings. For leading commas, tabular alignment or minification, paste the query here, format it with the MySQL dialect and copy the result back with Ctrl/Cmd+Shift+C.
Can it format a MySQL stored procedure?
Yes, once the DELIMITER lines are removed. Each statement inside BEGIN … END is laid out, but the body is not indented, so treat the output as a readable draft rather than a final style.
Does the MySQL formatter support JSON columns and the ->> operator?
Yes. Both -> and ->> are recognised as operators and JSON path strings like ‘$.items[0].sku’ are left exactly as written.
Should I choose MySQL or MariaDB?
Pick the server the query runs on. They share quoting and comment rules, but only the MariaDB setting treats RETURNING as a clause.
Is the query sent to a server?
No. Tokenizing and formatting happen in your browser tab, so production table names and customer data in WHERE clauses never leave your machine.