Text-to-SQL 的完整方法论

从 prompt 工程、schema 检索、few-shot 选样到护栏与评测——把"自然语言查询数据库"做对

让 LLM 把"上个月销售额按地区降序前 5"变成正确的 SQL，是几乎所有数据应用都要做的事。这一篇把端到端的真实工程讲清楚——不是"塞 schema 进 prompt 让它写"那种 demo。

通用错误：以为"塞整个 schema 进 prompt"就够了

LLM 在大型 schema（>50 张表）下会：

选错表（同名字段歧义）
把字段类型搞错（把 cents INT 当 dollars）
编造不存在的列
JOIN 顺序错误导致全表扫描

正确的 pipeline：

用户问题
   ↓
[1] 意图归类（query / aggregation / mutation）
   ↓
[2] Schema 检索（找最相关的 5-10 张表）
   ↓
[3] Few-shot 选样（选与本问题最像的 3 个示例）
   ↓
[4] LLM 生成 SQL
   ↓
[5] 静态护栏（语法 / 危险语句 / 行数）
   ↓
[6] EXPLAIN 校验（不真跑也能拦阻全表扫描）
   ↓
[7] 执行 + 结果裁剪
   ↓
[8] 自然语言重述（可选）

Step 1：意图归类

const intentPrompt = `
Classify the user's question into one of:
- query: just SELECT
- aggregation: GROUP BY / window
- mutation: INSERT / UPDATE / DELETE
- meta: asking about schema itself

Question: "${q}"
Return JSON: { "intent": "...", "confidence": 0-1 }
`;

意图分类用一个小模型（GPT-4o-mini / Claude Haiku）就够，便宜快。如果 confidence < 0.6，直接 fallback 到"我不确定你想问什么"，不进入下一步。

Step 2：Schema 检索

把每张表的"自描述"（表名、注释、列名 + 注释）做 embedding，存到 MariaDB VECTOR 列里：

CREATE TABLE schema_embeddings (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  table_name  VARCHAR(64),
  description TEXT,
  embed       VECTOR(1536),
  VECTOR INDEX (embed)
);

查询时：

const qEmbed = await embed(question);
const [tables] = await db.query(
  `SELECT table_name, description,
          VEC_DISTANCE_EUCLIDEAN(embed, VEC_FromText(?)) AS dist
   FROM schema_embeddings
   ORDER BY dist LIMIT 8`,
  [JSON.stringify(qEmbed)],
);

只把 top-8 的表喂给生成模型。Schema token 数量直接砍 80%+，准确率反而上去。

Step 3：Few-shot 选样

类似地，把历史成功的 (question, sql) 对做 embedding 索引：

CREATE TABLE sql_examples (
  id        BIGINT AUTO_INCREMENT PRIMARY KEY,
  question  TEXT,
  sql_text  TEXT,
  embed     VECTOR(1536),
  VECTOR INDEX (embed)
);

每次取最近的 3 个示例放进 prompt。空冷启动时手写 30 个高质量示例铺底。

Step 4：生成 prompt 模板

You are an expert SQL writer for MariaDB 11.4.

Database schema (only the relevant tables):
{retrieved_schema_with_comments}

Examples:
{few_shot_examples}

Rules:
- Output ONLY a single SQL statement, no markdown fences.
- Always include a LIMIT for SELECT (max 500).
- Use MariaDB syntax (NOT MySQL 8 syntax — JSON_TABLE differs).
- Money columns end with `_cents` and are integers.
- All timestamps are server-local time zone.

User question: {question}
SQL:

不要让模型同时输出 SQL + 解释。会导致 SQL 被截断或加了 markdown fence。让它只输出 SQL，解释是另一次调用。

Step 5：静态护栏

复用 MCP server 实战里的 guard()：

import { guard } from './guard';
const check = guard(sql, { allowWrites: false, maxRowsAffected: 500 });
if (!check.ok) throw new Error(`blocked: ${check.reason}`);

Step 6：EXPLAIN 校验

不执行也能判断：

const [plan] = await db.query(`EXPLAIN FORMAT=JSON ${sql}`);
const rows = JSON.parse(plan[0]['EXPLAIN']);
function findBad(node: any): string | null {
  if (node.rows_examined_per_scan > 100_000) return 'full table scan';
  if (node.access_type === 'ALL' && node.rows > 10_000) return 'no index used';
  for (const k in node) if (typeof node[k] === 'object') {
    const bad = findBad(node[k]);
    if (bad) return bad;
  }
  return null;
}
if (findBad(rows.query_block)) {
  // 让模型重写一次，附上 EXPLAIN 反馈
}

这一步能把"看似对、实际打挂数据库"的 SQL 拦掉。

Step 7：执行 + 裁剪

const [rows] = await db.query(sql);
// 大结果集裁剪
const tooLarge = JSON.stringify(rows).length > 50_000;
const final = tooLarge ? rows.slice(0, 50) : rows;

Step 8：自然语言重述（可选）

如果是给非技术用户看，再调一次模型把表格变成"上个月销售额第一的地区是华东，共 1.2 亿元"。

评测：怎么知道你的 pipeline 在变好？

建一个评测集（300 对 (question, expected_sql)），每次 pipeline 改了之后跑：

let pass = 0;
for (const { question, expected } of testset) {
  const generated = await pipeline(question);
  // 三种判定（递进）
  // 1) SQL 文本完全相同
  // 2) AST 等价（用 node-sql-parser）
  // 3) 执行结果相同（在 staging 库跑两遍对比）
  if (await resultsEqual(generated, expected)) pass++;
}
console.log(`accuracy: ${pass / testset.length}`);

业界经验：单纯 prompt + schema 准确率 ~55%，加上检索 + few-shot 能到 ~80%，加 EXPLAIN 重写到 ~88%。再往上要 fine-tune。

三个模型横评（2026 Q1，作者实测）

测了 300 题 evaluation set，目标 MariaDB 11.4：

模型	一次成功率	重写后成功率	平均 token	平均延迟
Claude Opus 4.7	89%	93%	3.2K	2.8s
GPT-5	86%	91%	3.5K	2.1s
Gemini 2.5 Pro	83%	89%	4.1K	1.9s
Claude Haiku 4.5	76%	84%	3.0K	0.9s
Llama 3.3 70B (Groq)	71%	80%	4.8K	0.6s

结论：生产推荐 Claude Opus 或 GPT-5 当主、Haiku/Llama 当 fallback；如果延迟敏感，Haiku 性价比最高。

常见坑

不要把所有表都丢进 prompt——准确率掉、token 爆
不要让 LLM 直接 EXECUTE 任意 SQL——加 guard
utf8mb4 默认 collation 在 MariaDB 11.5+ 是 uca1400，从 MySQL 学到的 SQL 在这里排序结果可能不同
不要假设 LIMIT N 没序就稳定——MariaDB 不保证无序结果稳定
JOIN 顺序敏感——给模型加 hint："prefer star schema joins on fact table first"