Prompt工程化调优实录：基于GPT-4o的SQL生成任务，从42%到89%准确率

1. 问题背景：一个被低估的工程问题

做AI应用最烦的不是模型不行，而是Prompt不稳定。上个月我接到一个需求：为公司内部数据分析平台做一个NL2SQL的Copilot。业务方要求不高——“能用就行”。结果测试下来，GPT-4o直接写SQL，在Spider数据集的难例上准确率只有42.5%。更头疼的是，模型偶尔会胡编列名，或者把LEFT JOIN条件写反。

很多人觉得Prompt随便写两句就行，那是没做过生产级任务。真正的Prompt Engineering是在Token预算、模型能力和业务约束之间找平衡。本文不会讲“如何写一个完美的Prompt”这种玄学，只记录我针对SQL生成这个具体任务做的5轮实验数据。

2. 环境与版本：固定变量才能对比

所有实验均通过openaiPython库调用，版本如下：
- openai库版本：1.30.5
- 模型版本：gpt-4o-2024-05-13（主测）与 gpt-3.5-turbo-0125（对照）
- 温度：temperature=0.2（降低随机性，保证可复现）
- 测试集：从Spider dev集里按难度筛选200条（含GROUP BY、嵌套子查询、多表JOIN）
- 评估指标：执行准确率（Execution Accuracy，直接跑SQLite），不是匹配准确率，那个没意义。

先看基线Prompt长什么样：

# 基线版本 - 零样本直出
def make_prompt_baseline(schema_ddl: str, question: str) -> str:
    return f"""你是一个SQL专家。根据数据库结构和问题，输出SQLite SQL。
数据库结构：
{schema_ddl}

问题：{question}
SQL查询：
"""

我跑了下基线，结果如下表（Token按tiktoken的cl100k_base编码统计）：

4o准确率低我可以忍，但3.5直接没法看。接下来的调优全程只针对4o，3.5最后做对照。

3. 方案设计：从Few-shot到结构化约束

我设计了5个迭代版本，每个版本在前一版基础上增加一个约束维度：

• V1（基线）：零样本，直接拼接DDL和问题。
• V2（Few-shot）：在System消息里塞2个典型难例（含JOIN和子查询）。
• V3（CoT + 中间表示）：要求模型先输出“SQL逻辑分解”，再写SQL。
• V4（Schema感知裁剪）：只把涉及到的表结构拼进Prompt，减少噪声。
• V5（输出格式锁定 + 自校验）：强制JSON输出SQL与解释，并让模型检查列名是否存在。

V4这个点值得展开。Spider数据库大表有21个字段，全量DDL有3KB+，但一条查询只用到其中4个。把无关字段全喂进去，模型容易被误导选错列。所以我用字符串匹配粗筛了涉及的表，再补全外键关系。

4. 核心实现：代码与Prompt细节

先写一个Schema裁剪函数：

import sqlite3, re

def prune_schema(db_path: str, question: str, all_ddl: dict) -> str:
    """
    根据question中的关键词粗筛涉及的table，裁剪DDL。
    all_ddl: {table_name: create_sql_string}
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    # 获取所有表名
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    all_tables = [r[0] for r in cursor.fetchall()]

    # 简单匹配：问题中出现的表名或列名前缀
    matched = []
    for t in all_tables:
        # 表名如果是下划线分隔，拆开匹配
        t_parts = t.split('_')
        if any(part in question.lower() for part in t_parts):
            matched.append(t)

    # 必须包含主表，否则报错
    if not matched:
        matched = all_tables[:2]  # fallback

    # 补全外键引用的表（从原DDL里查）
    final_ddl = []
    for t in matched:
        ddl = all_ddl.get(t, "")
        final_ddl.append(ddl)
        # 用正则找REFERENCES xxx
        refs = re.findall(r'REFERENCES\s+(\w+)', ddl, re.IGNORECASE)
        for r in refs:
            if r not in matched and r in all_ddl:
                final_ddl.append(all_ddl[r])

    return "\n\n".join(final_ddl)

接下来是V5的Prompt模板，这里放弃了纯文本输出，改用JSON结构化要求：

V5_SYSTEM = """你是一个SQL代码生成器。你必须严格遵守以下规则：
1. 只输出一个JSON对象，不要输出任何多余文本。
2. JSON格式为：{"thinking": "你的逻辑分析", "sql": "SQL语句", "columns_used": ["col1", "col2"]}
3. 在生成SQL前，先检查columns_used中的每个列名是否存在于给定的表结构中。
4. 如果列名前缀不明确，必须使用"表名.列名"格式。
5. SQL必须是SQLite方言，不要使用TOP、GETDATE()等方言。

数据库结构如下：
{schema_ddl}

用户问题：{question}

现在开始，直接输出JSON："""

def make_prompt_v5(schema_ddl: str, question: str, example_qa: list) -> str:
    # example_qa 是Few-shot示例，每条是(question, sql, columns)
    ex_text = ""
    for q, s, cols in example_qa:
        ex_text += f"问题：{q}\nSQL：{s}\n涉及列：{', '.join(cols)}\n---\n"

    return V5_SYSTEM.format(schema_ddl=schema_ddl, question=question)

这里有个隐藏坑：GPT-4o的JSON模式不是100%稳定。即使你在Prompt里写了“只输出JSON”，偶尔也会蹦出“```json”或者前面加个“好的”。所以我在调用端加了重试机制——解析失败就重新调用，最多3次。

import json, time

def call_gpt_with_retry(client, prompt, max_retry=3):
    for attempt in range(max_retry):
        try:
            resp = client.chat.completions.create(
                model="gpt-4o-2024-05-13",
                messages=[
                    {"role": "system", "content": V5_SYSTEM},
                    {"role": "user", "content": prompt}
                ],
                temperature=0.2,
                response_format={"type": "json_object"}  # 关键参数
            )
            content = resp.choices[0].message.content
            data = json.loads(content)
            return data["sql"]
        except Exception as e:
            print(f"Attempt {attempt+1} failed: {e}")
            time.sleep(1)
    return None

注意那个response_format={"type": "json_object"}，这是OpenAI提供的强制JSON模式。但实测发现，这个参数会显著增加输入Token（约多130个左右），因为模型内部会加约束。如果预算紧，可以去掉这个参数改用正则提取，但准确率会掉3-5个百分点。

5. 踩坑与优化：Token花在哪了？

调优过程中我踩了几个大坑，可能对你有参考价值：

坑1: Few-shot示例选错比没有更糟。 我V2版本随机从训练集抽了2个例子，结果准确率反而掉到39%。分析发现，示例里有个LEFT JOIN，模型被带偏，遇到INNER JOIN场景也强制仿写。后来我改成故意选和测试问题结构相反的示例（一个简单聚合+一个复杂嵌套），效果才好转。

坑2: CoT的“thinking”会消耗大量输出Token。 V3版本要求模型先写逻辑分析再写SQL，准确率提升到61%，但平均输出Token从310涨到680。这意味着单次成本翻了近一倍。后来我在V5里把thinking限制在50字以内——效果没掉多少，Token却省了30%。

坑3: Schema裁剪需要处理表间依赖。 你裁剪表A后，如果A有外键引用表B，而B不在裁剪结果里，SQL执行时直接报错“no such table”。所以我写了REFERENCES正则补全逻辑，但这个逻辑偶尔会误伤——把一些没用的历史表拉进来。最终方案是：只补全被引用且在主查询中出现的表。

坑4: 3.5-turbo在V5上不稳定。 同样Prompt，4o能稳定输出JSON，3.5经常把SQL字段写成query。折腾了很久，后来发现3.5对response_format的支持不如4o，所以直接放弃3.5跑生产，只在离线测试里做对照。

6. 效果数据：量化收益

最终测试结果如下（200条难例，执行准确率）：

几个关键数据点值得强调：
- V4→V5的提升是最大的（73%→89%），主要贡献来自JSON输出约束和列名校验。这验证了“让模型先确认列存在”比直接生成SQL靠谱得多。
- Token消耗反而比基线降了35%，原因是Schema裁剪减少了输入，而JSON约束减少了模型废话输出（之前模型经常自问自答或解释两句）。
- 错误案例里，剩余11%的失败集中在：日期函数处理（date('now') vs CURRENT_DATE）和隐式类型转换。这已经不是Prompt能解决的了，需要加后处理规则。

7. 总结与工程建议

这次调优给我最大的教训：Prompt Engineering不是写作文，而是做特征工程。对NL2SQL来说，Schema裁剪和输出格式约束比花哨的CoT更有效。

几点实用建议：
1. 先量化基线，别凭感觉说“效果变好了”。用执行准确率，别用ROUGE或BLEU。
2. Token消耗必须和准确率一起看。V3的CoT准确率高了，但成本也高了，生产环境很难接受。
3. 不要迷信Few-shot。示例选不好，负迁移比零样本还严重。
4. 如果任务有结构化输出需求，直接上JSON mode，别让模型自由发挥。
5. 生产环境务必加重试机制，模型偶尔会发疯（比如输出个Base64编码）。

现在这套V5 Prompt已经在内部系统跑了一个月，平均每天调用2,000次，准确率稳定在87-90%之间。后续我计划尝试把Schema裁剪换成基于语义的embedding检索，看看能不能再压一点Token。有任何问题欢迎评论区交流。

注：实验代码和数据已脱敏，完整版在内部仓库。文中价格估算基于OpenAI官方定价，gpt-4o输入$5/1M tokens，输出$15/1M tokens。