如何讓 AI 生成較正確的 SQL：提供 schema、SQL 方言和 EXPLAIN

AI tools are genuinely good at writing SQL. Ask ChatGPT, Claude, Cursor or Claude Code for "monthly revenue per plan" and you'll have a query in seconds, and it will probably run.

That's the problem. Wrong SQL rarely fails loudly. It returns a number, and the number looks plausible. Here are the mistakes I see most often in AI-written SQL, why they happen, and the three inputs that fix most of them: the schema, the dialect, and the plan.

Why the model gets it wrong

The model isn't careless. It's missing information:

It hasn't seen your schema, so it writes the column names that are usually there (users.created_at, orders.customer_id).
It doesn't know your engine, so it mixes PostgreSQL, MySQL and SQLite syntax, or uses syntax that runs on both with different results.
It doesn't know what "last month", "active" or "revenue" mean in your business, so it picks something and doesn't tell you.
It can't see your data, so it doesn't know which columns contain NULLs or which joins are one-to-many.

Each of those turns into a specific, recognisable bug.

Bug 1: fan-out (totals that are too high)

-- "Revenue per plan from subscription invoices"
SELECT i.plan_id, SUM(i.amount_cents) AS revenue_cents
FROM invoices i
JOIN invoice_lines l ON l.invoice_id = i.id
WHERE l.kind = 'subscription'
GROUP BY i.plan_id;

If an invoice has three subscription lines, its total is summed three times. The query runs, the number is wrong, and nothing warns you. The fix is to stop joining at the wrong grain:

SELECT i.plan_id, SUM(i.amount_cents) AS revenue_cents
FROM invoices i
WHERE EXISTS (
  SELECT 1 FROM invoice_lines l
  WHERE l.invoice_id = i.id AND l.kind = 'subscription'
)
GROUP BY i.plan_id;

A model can only spot this if it knows invoices → invoice_lines is one-to-many, which means it needs the schema with its keys.

Bug 2: NULLs that silently remove rows

-- "Customers who have never ordered"
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If any orders.customer_id is NULL, NOT IN is never true and this returns zero rows. NOT EXISTS doesn't have that problem:

SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Related traps: status <> 'cancelled' also drops rows where status is NULL; COUNT(col) skips NULLs while COUNT(*) doesn't; SUM over no rows returns NULL, not 0. The model needs to know which columns are nullable, so give it the DDL, not a summary.

Bug 3: the wrong dialect

Some differences fail loudly (date_trunc doesn't exist in MySQL; MySQL has no RETURNING). The dangerous ones run fine and return different results:

7 / 2 is 3 in PostgreSQL and SQLite, 3.5000 in MySQL.
LIKE is case-sensitive in PostgreSQL, case-insensitive for ASCII by default in SQLite, and collation-dependent in MySQL.
NULLs sort last in ascending order in PostgreSQL and first in MySQL and SQLite.
Upserts: ON CONFLICT ... DO UPDATE in PostgreSQL and SQLite (3.24+), ON DUPLICATE KEY UPDATE in MySQL.

Tell the model the engine and version. "MySQL" isn't enough: window functions and CTEs arrived in MySQL 8.0, EXCEPT in 8.0.31.

Fix 1: give it the schema (the real one)

Build a context block once and reuse it. Get the DDL straight from the database:

PostgreSQL: pg_dump --schema-only -t orders -t invoices ...
MySQL: SHOW CREATE TABLE orders;
SQLite: .schema orders in the sqlite3 shell

Keep nullability, foreign keys, unique constraints and composite index column order. Add rough row counts and your conventions (soft-delete column, tenant column, time zone of timestamps). In Cursor, Claude Code or Codex, save it as a file in the repo (docs/db-context.md) and reference it from your rules file so it's loaded every time.

Fix 2: make it state the spec before the SQL

Most wrong reports are wrong because of an unstated definition, not bad syntax. Here's the prompt I start with:

You are a senior SQL developer. Write ONE query for the exact engine and version below.

Engine: [ENGINE AND VERSION]
Schema (use only these tables and columns; if something is missing, say so):
[DDL]

Question: [QUESTION]
Definitions: [e.g. "month = calendar month in UTC", "exclude deleted_at IS NOT NULL"]

Before writing SQL:
1. State the grain (one output row per what), filters, and the time window as
   >= start AND < end.
2. List your assumptions. If one would change the answer materially, ask me.
3. Name each join and its cardinality (1:1, 1:N, N:M) and how you avoid
   double counting in aggregates.
Then write the query, and give me 2 sanity-check queries (a total that should
reconcile, and a spot check for one entity).

Rules: bind parameters for user values, SELECT only, syntax valid for this engine.

The "join cardinality" step catches fan-out. The "assumptions" step catches the definitions. The sanity checks give you something to run instead of something to trust.

Fix 3: ground performance advice in EXPLAIN

"Add an index on every column in the WHERE clause" is what you get when the model has no plan to look at. Give it one:

PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) <query>; (from PostgreSQL 18, buffer counts are included with ANALYZE automatically).
MySQL 8: EXPLAIN FORMAT=TREE <query>; for the plan, EXPLAIN ANALYZE <query>; (8.0.18+) for actual timings.
SQLite: EXPLAIN QUERY PLAN <query>; (look for SCAN vs SEARCH ... USING INDEX).

One warning: EXPLAIN ANALYZE executes the statement in PostgreSQL and MySQL. For an UPDATE or DELETE, wrap it in a transaction and roll back, and do it on a copy of the data when you can.

Then ask the model to walk the plan node by node and to cite the node behind every claim. In PostgreSQL plans, tell it that actual time and rows are per loop, so a cheap-looking inner node with loops=50000 may be where the time goes. Ask it to flag estimated-vs-actual row mismatches (often a statistics problem, not an index problem) and sorts that spill to disk. And get the plan from production-like data: the planner picks different plans for a thousand rows than for ten million.

Before you run any of it

AI-written SQL that only reads is low risk. AI-written DDL and DML deserves a rule: nobody runs it on production without reading every statement. In practice:

Run it on a local or staging copy first.
Run the WHERE of every UPDATE/DELETE as a SELECT COUNT(*) before running the write.
Wrap writes in a transaction and check the row count before COMMIT. (PostgreSQL and SQLite DDL is transactional; MySQL DDL commits implicitly and can't be rolled back.)
Use a read-only role for exploration, and set a statement timeout.
Don't give coding agents production database credentials.

The short version

Schema in, dialect stated, definitions written down, plan attached, verification out. The model is the same; the inputs are what change the answer.

I packaged the prompts I use for this (32 of them: natural language → SQL with schema context, query review for NULLs, fan-out, N+1 and index use, EXPLAIN reading for PostgreSQL, MySQL and SQLite, schema and index reviews, zero-downtime migrations and rollbacks, ORM and injection checks, seed data and DB tests, window-function reports) plus a verification checklist and a PDF guide as the AI SQL & Database Prompt Pack: https://payhip.com/b/VhqW7. Everything above works without it.

— Pluto · @Mars_Peakul