MySQL Formatter

MySQL queries usually reach you from somewhere hostile to formatting: the slow query log, a mysqldump file where every INSERT is one line thousands of characters long, or an ORM that logs the statement it just executed. With the MySQL dialect selected, this formatter reads backtick-quoted identifiers, # comments and the two-argument LIMIT as first-class syntax instead of choking on them.

That last point is the reason not to just use a generic formatter. Standard SQL has no # comment and no LIMIT offset, count; a generic parser fails on the first one it meets. The same engine also covers MariaDB, which shares MySQL syntax for everything a formatter cares about.

MySQL syntax the formatter treats specially

• Backtick identifiers. `users`.`id` is read as a single quoted identifier, including when the name contains spaces, reserved words or emoji. The backticks are never rewritten to double quotes, because under the default sql_mode a double-quoted string in MySQL is a string literal, not an identifier — changing them would change the query.
• # line comments. Preserved as written and kept on their own line. So are -- comments (which in MySQL require a following space) and /* … */ blocks.
• LIMIT 20, 10. Kept together on the LIMIT line rather than split across lines. The two-argument form means offset first, row count second — LIMIT 20, 10 skips 20 rows and returns 10, the exact opposite order of LIMIT 10 OFFSET 20, which is the same thing written the readable way.
• ON DUPLICATE KEY UPDATE. Promoted to its own clause line, aligned with INSERT INTO and VALUES, so an upsert reads as three blocks rather than one run-on line.
• Table option clauses. ENGINE = InnoDB, DEFAULT CHARSET = utf8mb4 and COLLATE stay on the tail of the CREATE TABLE statement after the column list closes.

The sample query uses the row-alias form of upsert (VALUES (…) AS new … new.views) that MySQL 8.0.19 introduced; the older VALUES(views) reference inside the update clause has since been deprecated. Both parse here.

Reading a slow query log entry

Slow log entries are the most common thing pasted into a MySQL formatter, and they arrive as one long line preceded by # metadata comments — Query_time, Lock_time, Rows_examined. Because the MySQL dialect understands #, you can paste the whole entry, metadata included, and get the header lines preserved above a readable query.

Once the query is broken into clauses, the usual culprits become visible without a plan: a WHERE predicate wrapping an indexed column in a function, an accidental cross join hiding in a comma-separated FROM list, or a LIMIT with a five-figure offset that MySQL will satisfy by reading and discarding every preceding row.

What it will not do

This is a pretty-printer, not a query analyser. It does not run EXPLAIN, does not know your indexes, and does not connect to any server. It will not warn you that utf8 in MySQL means three-byte UTF-8 and you probably wanted utf8mb4, though the sample above quietly does the right thing.

It does check for unbalanced parentheses and an odd number of single quotes, which catches the most common damage from copying a query out of a log that truncated it.