把「上个月华东区退款金额最高的 10 个商家」直接变成一条可执行的查询语句，这就是 Text2SQL 要解决的问题。但真正接到生产库时，模型写出的 SQL 往往语法全对、语义全错，甚至一条漏了分区条件的全表扫描就能把库拖垮。本文讲透 Text2SQL 的完整落地链路：Schema 提示怎么喂、几百张表怎么召回、生成的 SQL 如何三道闸校验、怎么做只读安全执行，以及准确率到底该怎么评。
一、难点不在写 SQL，而在懂业务
很多团队第一次做 Text2SQL 都有同样的体验：demo 阶段效果惊人。单表、字段名规范、问题直白，准确率轻松八成以上，PPT 上非常好看。一接真实业务库立刻崩盘——几百张表，字段叫 f_amt_1，同一个「金额」在三张表里含义不同，还混着五年前遗留的脏字段和早就废弃的状态码。
问题的本质要说清楚：模型缺的不是 SQL 语法能力，而是业务语义。SQL 语法它比大部分工程师都熟，窗口函数、CTE、递归查询都能写得漂亮。但它不可能凭空知道你们公司 order_status = 7 表示「已退款待结算」，也不知道财务口径的「实收金额」要减掉平台补贴。所有落地工作，本质上都是在把业务语义可靠地注入上下文，并在生成之后加上校验兜底。
1.1 三类典型失败，第二类最危险
幻觉字段：模型编造出 refund_amount 这种「看起来应该存在」的列名，实际库里叫 f_refund_amt。这类错误最好处理，因为执行直接报错，用户立刻知道失败了。
语义错配：用户问「退款金额」，模型对 order_amount 做了 SUM。语法完全合法，执行完美成功，返回一个漂亮的数字——但这个数字是错的，而且没有任何报错。这是 Text2SQL 最危险的失败模式：静默给出错误答案。如果这个数字被贴进经营周报，损失比查询失败大得多。
性能灾难：生成 SELECT * 后 JOIN 三张亿级表，或者漏掉分区裁剪条件，一条查询就把磁盘 IO 打满。这类问题不只是 AI 的问题，本质是 SQL 执行计划问题，排查思路可以直接参考 MySQL 深度调优：核心参数与执行计划解读 与 MySQL 索引底层原理。区别在于，人写的慢 SQL 上线前会过评审，而 AI 生成的 SQL 是即时执行的，没有人工闸门，所以必须在链路里自动拦。
二、Schema 提示是整条链路的地基
一个反直觉但被反复验证的结论：Text2SQL 的效果，七成取决于喂给模型的 Schema 质量，而不是模型参数量。换掉更强的模型能提升几个点，把 Schema 描述写清楚能提升几十个点。所以精力应该优先花在这里。
2.1 只喂「决策需要」的信息
最常见的错误做法是把 SHOW CREATE TABLE 的原始输出整段塞进提示词。这里面有大量噪声：字符集、存储引擎、自增值、索引定义、无关的历史字段。它们不帮模型做任何决策，只是在挤占上下文、稀释注意力。关于长上下文里信息密度的取舍，可以延伸读 大模型上下文工程实战：长上下文提示设计。
| 信息项 | 是否要喂 | 原因 |
|---|---|---|
| 表名 + 一句话业务含义 | 必须 | 决定选哪张表，权重最高 |
| 列名 + 类型 + 中文注释 | 必须 | 防幻觉字段的唯一手段 |
| 枚举列的取值含义 | 必须 | 状态码语义无法靠猜 |
| 主键 / 外键关联关系 | 必须 | 决定 JOIN 条件是否正确 |
| 分区键 / 时间字段 | 强烈建议 | 不喂就一定会全表扫 |
| 2 到 3 行样例数据 | 建议 | 让模型看清日期格式、金额单位 |
| 字符集 / 引擎 / 自增值 | 不要 | 纯噪声，挤占上下文 |
| 索引定义明细 | 不要 | 交给 EXPLAIN 校验，不靠模型自觉 |
推荐的做法是给每张核心表维护一份「语义化 Schema 卡片」，人工写一次、长期复用。它不是 DDL 的复制，而是给模型看的业务说明书：
-- 表：t_order（订单主表，一行一笔订单，日增约 300 万）
-- 分区键：dt（按天分区，查询必须带 dt 范围，否则全表扫）
-- pay_amount DECIMAL(12,2) 用户实付金额（单位：元，已含运费）
-- refund_amount DECIMAL(12,2) 累计退款金额（单位：元，部分退款会累加）
-- order_status TINYINT 1=待支付 2=已支付 5=已完成 7=已退款 9=已关闭
-- region_code VARCHAR(8) 大区编码：EC=华东 NC=华北 SC=华南
-- merchant_id BIGINT 商家 ID，关联 t_merchant.id
-- 口径提醒：统计"退款金额"用 refund_amount，不要用 pay_amount最后一行那句「口径提醒」看着土，但极其有效。它把过去踩过的语义错配坑，直接固化成了提示词的一部分。每修一个线上错误答案，就往对应的卡片里补一句，这份文档会越用越准。
2.2 提示词模板与少样本
提示词要把角色、方言、硬约束、输出格式一次说清。输出务必要求结构化，方便后续程序解析——不要让模型在 SQL 前后写解释文字，否则你得靠正则去猜哪段是 SQL。结构化输出的通用做法见 大模型结构化输出实战：JSON Schema 与提示工程。
SYSTEM = """你是资深数据分析师，只输出 MySQL 8.0 方言的 SQL。
硬性规则：
1. 只能生成 SELECT 语句，禁止 INSERT/UPDATE/DELETE/DDL。
2. 查询 t_order 必须带 dt 分区范围条件。
3. 禁止 SELECT *，必须显式列出需要的字段。
4. 必须带 LIMIT，未指定时默认 LIMIT 100。
5. 只能使用下方 Schema 中出现的表名和列名，不得臆造。
6. 若 Schema 信息不足以回答，返回 {"sql": null, "reason": "缺少xxx"}。
输出严格为 JSON：{"sql": "...", "tables": ["..."], "reason": "..."}"""
USER_TPL = """### 可用 Schema
{schema_cards}
### 参考示例
问：上周华东区已完成订单数
SQL：SELECT COUNT(*) AS cnt FROM t_order
WHERE dt BETWEEN '2026-08-24' AND '2026-08-30'
AND region_code = 'EC' AND order_status = 5 LIMIT 100
### 当前问题
{question}"""少样本示例不用多，3 到 5 条覆盖典型模式即可：一条聚合、一条多表 JOIN、一条时间范围过滤、一条 TOP N 排序。重点是示例里的写法要和你希望模型输出的风格完全一致，包括分区条件和 LIMIT，模型的模仿能力比遵循指令的能力更强。
三、几百张表：先召回，再生成
真实数仓动辄几百上千张表，全塞进上下文既超长又昂贵，还会显著降低准确率——无关表越多，模型选错表的概率越高。正确做法是加一层Schema 召回：把每张表的语义卡片做成向量索引，用户提问时先检索最相关的 Top-K 张表，只把这几张的卡片拼进提示词。
import chromadb
from sentence_transformers import SentenceTransformer
model = SentenceTransformer("BAAI/bge-large-zh-v1.5")
client = chromadb.PersistentClient(path="./schema_db")
col = client.get_or_create_collection("schema_cards")
# 1) 离线建索引：把语义卡片向量化（表变更时增量更新）
def index_tables(cards: dict):
ids, docs, embs = [], [], []
for table, card in cards.items():
ids.append(table)
docs.append(card)
embs.append(model.encode(card, normalize_embeddings=True).tolist())
col.upsert(ids=ids, documents=docs, embeddings=embs)
# 2) 在线召回：只取最相关的 K 张表
def recall_schema(question: str, k: int = 5) -> str:
q = model.encode(question, normalize_embeddings=True).tolist()
res = col.query(query_embeddings=[q], n_results=k)
return "\n\n".join(res["documents"][0])这本质就是一套 RAG，只不过检索的语料是 Schema 而非文档。向量库选型可参考 Chroma 向量数据库实战，Embedding 模型选型见 Embedding 模型选型与文本向量化实战，混合检索与重排序的调优手法见 大模型 RAG 进阶：混合检索与重排序优化实战。
一个实践细节：纯向量召回对「专有名词」不敏感。用户问「查一下 GMV」，语义检索可能找不到叫 t_trade_stat 的表。解决办法是向量召回叠加关键词倒排，并额外维护一份业务术语表（GMV 到 成交总额 到 t_trade_stat.total_amount 的映射），把术语命中的表强制加入候选集。另外把高频核心表（订单、用户、商家）设为常驻，不参与召回竞争，避免它们被冷门表挤掉。
四、生成不等于可执行：三道校验闸
这是自建 Text2SQL 和直接调模型的核心差距。模型输出的 SQL 在执行前，必须无条件过三道闸，任何一道不通过就拒绝执行并给出可读的失败原因。
4.1 第一闸：语法解析与元数据比对
用 sqlglot 把 SQL 解析成抽象语法树，做三件事：确认能解析（语法合法）、确认根节点是 SELECT（拦住一切写操作）、提取所有引用的表名列名，逐个与真实元数据比对——这一步能把幻觉字段拦死在执行之前。
import sqlglot
from sqlglot import exp
ALLOWED = {"t_order": {"dt", "pay_amount", "refund_amount",
"order_status", "region_code", "merchant_id"},
"t_merchant": {"id", "name", "region_code"}}
def gate_static(sql: str):
try:
tree = sqlglot.parse_one(sql, dialect="mysql")
except Exception as e:
raise ValueError(f"SQL 语法无法解析：{e}")
# 只允许 SELECT，DDL/DML 一律拒绝
if not isinstance(tree, exp.Select):
raise ValueError("仅允许 SELECT 查询")
# 表名校验
for t in tree.find_all(exp.Table):
if t.name not in ALLOWED:
raise ValueError(f"引用了不存在的表：{t.name}")
# 列名校验（防幻觉字段）
known = set().union(*ALLOWED.values())
for c in tree.find_all(exp.Column):
if c.name != "*" and c.name not in known:
raise ValueError(f"引用了不存在的列：{c.name}")
# 强制 LIMIT，缺失则自动补
if not tree.args.get("limit"):
tree = tree.limit(100)
return tree.sql(dialect="mysql")注意最后一段：缺 LIMIT 不是报错，而是自动补齐。这类能安全修复的问题就别打扰用户，只有真正无法自动纠正的（幻觉列名、写操作）才拒绝并回传给模型重试一次。
4.2 第二闸：EXPLAIN 预演，拦全表扫描
语法对了不代表能跑。真正把库拖垮的是执行计划。先跑 EXPLAIN（不产生实际扫描成本），检查扫描行数预估和访问类型，超阈值直接拒绝，让用户补充过滤条件。
MAX_ROWS = 5_000_000 # 预估扫描行数上限
BAD_TYPES = {"ALL", "index"} # 全表扫 / 全索引扫
def gate_explain(conn, sql: str):
with conn.cursor(dictionary=True) as cur:
cur.execute("EXPLAIN " + sql)
plan = cur.fetchall()
total = sum(int(r.get("rows") or 0) for r in plan)
if total > MAX_ROWS:
raise ValueError(f"预估扫描 {total} 行，超过上限；请缩小时间范围")
for r in plan:
if r.get("type") in BAD_TYPES and (int(r.get("rows") or 0) > 100_000):
raise ValueError(f"表 {r.get('table')} 走了全表扫描，请补充分区或索引条件")
return plan阈值要按库的实际能力设，别照抄。执行计划里 type、rows、Extra 各字段怎么读，PostgreSQL 用户可以对照 PostgreSQL 慢查询优化：执行计划解读与索引调优实战，MySQL 侧看前面提过的调优文。另一个实用技巧：把 EXPLAIN 结果里的关键信息回传给模型，让它自己改写成走索引的版本，比直接报错给用户体验好得多。
4.3 第三闸：执行期硬隔离
前两闸是逻辑防御，第三闸是物理防御——假设前面全被绕过，也要保证打不死库。核心手段就一句话：用一个权限被削到最小的只读账号，连只读从库，并且带超时。
-- 专用只读账号：只给 SELECT，且限定到白名单库
CREATE USER 'text2sql_ro'@'10.%' IDENTIFIED BY '***';
GRANT SELECT ON analytics.* TO 'text2sql_ro'@'10.%';
-- 连接与资源限制，防止被单个查询打满
ALTER USER 'text2sql_ro'@'10.%'
WITH MAX_USER_CONNECTIONS 20 MAX_QUERIES_PER_HOUR 2000;
-- 会话级超时（MySQL 8.0，单位毫秒），超时自动 kill
SET SESSION MAX_EXECUTION_TIME = 8000;| 风险 | 兜底手段 | 失效后果 |
|---|---|---|
| 模型生成写操作 | 只读账号 + AST 类型校验 | 数据被误删改，最严重 |
| 全表扫描打满 IO | EXPLAIN 预演 + 扫描行数上限 | 主库抖动，全站受影响 |
| 查询长时间不返回 | MAX_EXECUTION_TIME 超时 | 连接池耗尽 |
| 返回百万行撑爆内存 | 强制 LIMIT + 流式游标 | 应用 OOM |
| 越权查到敏感表 | 库表白名单 + 行级权限过滤 | 数据泄露，合规事故 |
| 并发请求压垮从库 | 连接数限制 + 请求排队 | 分析库不可用 |
还有一条容易被忽略的：行级权限。华东区运营不该看到华南区的数据。这个不能靠模型自觉，要在执行前由程序强制往 WHERE 里注入当前用户的数据域条件，属于后端职责，绝不交给提示词。
五、准确率怎么评：比结果，不比字符串
很多团队用「生成的 SQL 和标准答案字符串是否一致」做指标，这是错的。同一个问题可以有十几种写法都正确：JOIN 顺序不同、用 CTE 还是子查询、字段别名不同。字符串比对会把大量正确答案判为错误，指标完全失真。
正确做法是执行准确率（Execution Accuracy）：把生成的 SQL 和标准 SQL 都在同一份测试数据上执行，比对结果集是否等价。这也是学术基准 Spider、BIRD 采用的核心指标。评测体系的通用搭建思路可参考 AI Agent 评测：如何量化智能体真实能力。
def result_equal(rows_a, rows_b, order_sensitive=False):
"""比对两个结果集是否等价：默认忽略行序与列名，只看值"""
norm = lambda rows: [tuple(round(v, 4) if isinstance(v, float) else v
for v in row) for row in rows]
a, b = norm(rows_a), norm(rows_b)
if len(a) != len(b):
return False
# 问题里带"排名/最高/前N"时行序有意义，必须严格比
return a == b if order_sensitive else sorted(a) == sorted(b)
def evaluate(cases, generate, run):
hit = 0
for c in cases:
try:
sql = generate(c["question"])
got, want = run(sql), run(c["gold_sql"])
ok = result_equal(got, want, c.get("order_sensitive", False))
except Exception:
ok = False
hit += ok
if not ok:
print(f"[FAIL] {c['question']}")
print(f"执行准确率 = {hit}/{len(cases)} = {hit/len(cases):.1%}")| 指标 | 怎么算 | 用途 |
|---|---|---|
| 执行准确率 | 结果集与标准答案等价的占比 | 核心指标，唯一可信 |
| 可执行率 | 能成功跑通不报错的占比 | 衡量幻觉字段严重程度 |
| 拦截率 | 被三道闸拒绝的占比 | 太高说明提示词或召回有问题 |
| 静默错误率 | 跑通但结果错的占比 | 最该压低，直接影响信任 |
| 平均延迟 | 召回加生成加执行总耗时 | 体验红线，建议 5 秒内 |
测试集要自己攒，别只用公开基准。从真实用户提问日志里挑 100 到 200 条，人工写好标准 SQL，按难度分成三档（单表过滤、多表 JOIN、嵌套聚合），每次改提示词或换模型都全量跑一遍。静默错误率是最该盯的数——它每高一个点，用户对整个系统的信任就少一分，而信任一旦崩了，工具就没人用了。
六、生产落地的五个坑
一、Schema 漂移无人同步。DBA 加了字段、改了状态码含义，语义卡片还是三个月前的，模型持续输出错误口径。必须把卡片更新挂进上线流程，或写定时任务比对线上元数据与卡片差异并告警。
二、时间表达处理不当。「上个月」「本季度」「最近 7 天」是提问里的绝对高频词，交给模型算日期极易出错，尤其跨年、跨季度边界。正确做法是在提示词里注入当前日期，并预先给出几个标准时间表达式的写法示例，甚至直接由程序解析时间词、替换成确定的日期区间再交给模型。
三、把结果直接当结论展示。一定要把生成的 SQL 原文展示给用户，并提供「这个结果不对」的一键反馈入口。让用户能核对逻辑，是建立信任的关键，也是最廉价的高质量标注来源——反馈样本直接进测试集和少样本池。
四、忽略成本。每次提问都拼几千 token 的 Schema，量一上来 token 费用相当可观。优化手段：召回结果加缓存、相同问题做语义缓存、简单问题（单表单条件）用小模型或规则模板直出，只把复杂问题送给大模型。
五、期望它替代 BI。Text2SQL 最适合的场景是临时性、探索性查询——那些「问一下就好、不值得专门排个需求」的问题。固定报表、核心经营指标，仍然应该用经过评审的固化 SQL 和物化视图。把两者定位分清楚，这个工具才能长期活下去。
七、小结
Text2SQL 落地的完整链路是：语义化 Schema 卡片 → 向量召回相关表 → 带硬约束的提示词生成 → AST 静态校验 → EXPLAIN 预演 → 只读账号限时执行 → 结果与 SQL 一起展示 → 用户反馈回流测试集。真正决定成败的不是模型，而是 Schema 描述质量和那三道校验闸。
建议的推进节奏：先挑一个业务域的 5 到 10 张核心表做试点，把语义卡片和测试集攒起来，把执行准确率跑到 80% 以上再扩表。一上来就想覆盖全数仓，只会得到一个「看着很酷、没人敢用」的 demo。相关工具链可以继续参考 大模型工具调用：Function Calling 实战——把校验和执行封装成工具交给模型自主调用，是这条链路的自然演进方向。
