MySQL Formatter

Why a MySQL formatter is not just an SQL formatter

A formatter has to tokenise a query before it can lay it out, and MySQL tokenises differently from every other engine. Backticks quote identifiers where standard SQL uses double quotes. A # starts a comment, which no other major engine accepts. LIMIT 20, 10 puts the offset before the row count. @total is a user variable, ON DUPLICATE KEY UPDATE is a clause of its own, and GROUP_CONCAT(… SEPARATOR ', ') smuggles a keyword into a function call. Feed any of that to a tokeniser built for another dialect and you get odd line breaks or a parse error on a query that runs fine in production.

This page pins the tokeniser to MySQL. Our generic SQL formatter reads the dialect off the query, which works well when markers like backticks are present, but a plain SELECT id, name FROM users carries no markers at all. Here there is nothing to detect and nothing to get wrong. Every query is read with MySQL rules, every time.

The formatting itself is measured, not assumed. ON DUPLICATE KEY UPDATE comes out as its own clause with the assignments indented under it, LIMIT 20, 10 stays in comma form instead of being rewritten, # comments keep their place next to the line they annotate, and @rank := @rank + 1 passes through untouched. A long GROUP_CONCAT gets broken over lines with its inner ORDER BY indented inside the call and the SEPARATOR kept on the value it belongs to. A script with several statements is split with a blank line between them, so a SET followed by the SELECT that uses the variable stays readable as a pair.

The pin is strict about reading, not about rejecting. If a pasted query turns out not to be MySQL at all, say it carries a PostgreSQL :: cast, the MySQL parse fails and the formatter retries the other dialects instead of stopping at an error, then prints a line naming the dialect it ended up using. You get formatted output either way, plus the information that the query was not what the page name promised.

How to use this formatter

Paste a query into the left pane, or drop a .sql file on it, and the formatted version appears on the right while you type. Nothing runs against a database and nothing is uploaded, the formatter is JavaScript in this tab.

Two options change the output. --uppercase uppercases keywords and leaves identifiers and function names alone, so select becomes SELECT while group_concat and your column names keep their case. --tabular right-aligns keywords in a fixed column, the river layout some teams use for queries in documents. --gap puts two blank lines between statements instead of one, which helps in a long migration script. Indentation is two spaces, four spaces or tabs.

The queries this page sees most are the ones that arrive unformatted by nature. A slow-query-log entry on one line, the SQL an ORM logged, a query pasted into a chat. For the reverse situation, where you have a spreadsheet export and need statements instead of a query, the CSV to SQL INSERT converter builds the INSERTs for you.

Backticks, double quotes and the ANSI_QUOTES trap

MySQL is the only major engine that quotes identifiers with backticks by default. That single character is behind most cross-engine copy-paste failures in both directions. A query written for PostgreSQL arrives with "customer id" in double quotes, and default MySQL reads that as the string customer id, not the column. The query often still parses, it just compares a column against a constant now, which is worse than an error because it returns rows.

The escape hatch is the ANSI_QUOTES sql_mode. With it enabled, double quotes become identifier quotes the way the standard intends, backticks keep working, and double-quoted strings stop being strings. Turning it on makes foreign queries run and can break existing ones that used "…" for text, which is why the mode is off on most installations and why the same query can behave differently on two servers of the same version.

This formatter does not rewrite quoting, deliberately. Backticks stay backticks and double quotes stay double quotes, because swapping them changes what the query means depending on a server setting the formatter cannot see.

Whether Users and users are the same table

Depends on the operating system under the server. MySQL stores each table in files named after it, so table and database name comparison follows the file system. The MySQL Reference Manual documents the controlling variable, lower_case_table_names, and its defaults: 0 on Linux, where Users and users are two different tables, 1 on Windows, where names are lowercased on storage and lookup, and 2 on macOS, where names keep their case on disk but compare case-insensitively.

The bug this produces is reliable enough to schedule. A query developed against MySQL on macOS or Windows runs fine with any casing, gets deployed against a Linux server, and dies with a table-not-found error on a table that plainly exists. Since MySQL 8.0 the variable can only be set when the server is initialised, so the fix is not a config change on the production box. The fix is consistent casing in the queries, and the usual convention is all-lowercase names with underscores.

Column names sit outside all of this, they are never case-sensitive on any platform.

Does this work for MariaDB queries?

Mostly yes, with one measured caveat. MariaDB kept MySQL's tokenising rules, so backticks, # comments, LIMIT 20, 10 and user variables all format correctly here. The underlying library does ship a separate MariaDB grammar, and this page pins the MySQL one, so syntax that only MariaDB has is where the seams show. We measured the most common divergence. INSERT … RETURNING does not error, but RETURNING id ends up appended to the VALUES line in lowercase instead of standing as its own clause. The query survives unchanged, the layout around the MariaDB-only part is just less pretty.

If your daily driver is MariaDB and that bothers you, tell us via the feedback box and a pinned MariaDB page moves up the list.

The MySQL errors a formatter helps you read

Formatting does not fix a broken query, it shows you where the break is. These are the messages this page gets pasted next to, in the exact wording the server prints.

Online tool vs. MySQL Workbench, phpMyAdmin and DBeaver

If the query is already open in a MySQL client, format it there. Workbench has Beautify Query on Ctrl+B in the SQL editor, phpMyAdmin has a Format button above the query box in the SQL tab, and DBeaver ships a formatter too. All three know your schema, which a browser tool never will.

This page covers the query that is not in any of those. The one an ORM wrote into a log, the one a colleague pasted into Slack, the one from a five-year-old ticket, the one on a locked-down machine where Workbench is not installed. Production queries carry table names, schema structure and sometimes literal customer values, so we would rather format them in a tab that provably sends nothing than in a random formatter site with an open network panel. When a query has to leave the machine anyway, for a forum post or an AI prompt, the SQL anonymizer replaces the names with placeholders first and can reverse the mapping later, the price being that the restore only works in a browser that still holds the key.

What this tool does not do

It has no schema, no server and no execution, so a typo in a column name formats perfectly. It cannot handle DELIMITER blocks, which are a client feature rather than SQL, so stored procedure bodies with custom delimiters come out mangled. PARSED means tokenised, not correct.

MySQL questions people actually search