1. 项目定位与整体拆解1.1 这个系统到底在解决什么问题系列案例做到8.6我把目标锁定在一个很实在的场景上自然语言转SQL并自动执行查询的智能问答系统。简单说业务同事不再需要找人提数在对话框里问一句“上个月华东区销量前三的商品”系统自动生成SQL、连接数据库查询再把结果用大白话返回来。这篇文章不是概念科普是用真实踩坑经历换来的落地总结。一句话概括这个项目Schema管理、SQL生成、安全校验、执行、结果转述这一整条链路全部打通最终包装成一个内部可用的数据问答工具。它适合三类人看正在做Text-to-SQL相关功能的产品或研发、想给团队搭自服务提数工具的数据工程师、以及准备做数据分析类Demo但不知道从哪下手的开发者。如果你没用过这类系统可以先想象一个场景运营同学想查“最近7天每个渠道的注册转化率”以前得提工单给数据组排期少则半天多则两天。有了这套系统他直接在界面里输入同样一句话几秒后拿到表格和摘要结论。这个价值不是把“取数”变快了而是把数据组从重复SQL劳动里解放出来去做真正需要判断力的分析工作。但千万别把这件事想得太简单。我一开始也以为“把问题丢给大模型让它输出SQL”就完事了真正动手后才发现模型生成SQL只是整条链路里最“性感”也最不可控的一环。后面还有数据库权限、SQL合法性校验、结果行数控制、上下文裁剪、成本管控等一系列工程问题。这些部分做不好系统就只能停留在Demo阶段根本不敢接生产库。1.2 能力边界与设计红线动手之前先划边界这是整个项目最重要的一步。我给自己定了几条硬性红线只做查询不做写操作。系统不生成INSERT、UPDATE、DELETE更不允许DDL。以单库为主支持多表JOIN、聚合、时间过滤、排序和分页。面向企业内部自服务查询不面向公网开放。每次执行必须留痕。谁在什么时间问了什么问题、生成了什么SQL、返回了多少行全部进审计日志。结果返回采用“表格文字摘要”双通道让用户既看到原始数据也看到模型总结的结论。这些红线不是拍脑袋定的。尤其“不做写操作”这一条直接决定了后面的账号权限设计和SQL校验逻辑。一个自动执行查询的系统如果哪天模型被诱导生成了一条危险SQL后果就是数据事故级别不是查询结果不准那么简单。所以从架构上就要把风险锁死而不是寄希望于模型听话。产品定位也因此清晰它是“只读版”的数据分析师负责把自然语言问题映射成数据查询再把结果解释给提问者。它不负责改数据、不负责建模、不负责跨数据源整合只做查询问答这一件小事但把它做到稳定、可控、好用。1.3 为什么不用现成的ChatBI很多人第一反应是市面上一堆ChatBI产品直接买不行吗我的判断是现成方案解决的是通用场景但企业内部数据问答真正难的是“适配”不是“能用”。首先是私有化与安全合规问题。数据不出内网是很多企业的硬要求SaaS方案即便效果再好也过不了这个门槛。其次是业务口径问题。“成交额”到底含不含退款、“有效订单”究竟怎么定义每家公司都不一样。这类知识必须能进系统、可配置而不是让模型瞎猜。最后是集成成本。已有的权限体系、报表平台、工单流程都需要对接自建一套反而更容易控制节奏。自建当然不便宜模型API费用、开发时间、后续迭代都是成本。但它换来的是可控性和灵活性。对想快速验证场景的小团队来说用现成方案是个合理选择但要做成企业内部长期使用的工具我更推荐自建。至少你能保证每一层逻辑都是自己看得到的出了问题知道从哪里下手。2. 系统架构与模块划分2.1 核心链路四段式处理管道整套系统的处理流程我拆成四个阶段来设计第一个阶段是“理解与改写”。用户问题往往带有自然语言特有的模糊性比如“最近一个月”“上季度”“华东区”这类说法。这个阶段要做三件事把时间词转成数据库可计算的日期区间把业务别名映射到标准字段名同时判断这个问题是不是一个可查询的问题。很多人会忽略这一步直接把原文丢给模型结果模型在处理时间边界时经常翻车。第二个阶段是“Schema感知与SQL生成”。模型必须基于真实表结构来写SQL而不是靠训练记忆里的“常识”脑补。系统要把候选表的DDL、字段注释、外键关系、业务规则打包成上下文再让模型输出SQL。这个阶段的难点是上下文长度有限表多了塞不下表少了又容易选错。后面我会专门讲怎么处理。第三个阶段是“安全与合法性校验”。模型生成的SQL不能直接执行先过一道由代码控制的网关语法解析、AST白名单、表名检查、强制Limit、执行超时。这一步是纯规则逻辑不依赖任何模型判断。我始终认为SQL对不对可以靠模型但SQL能不能执行必须靠规则。第四个阶段是“执行与结果解释”。用只读连接把SQL跑完拿到结果集后调用一次模型让模型根据结果表格生成中文摘要。这个摘要不是简单重复表格而是提炼结论比如“华东区订单量环比下降12%主要原因是B渠道流量下滑”。这四个阶段连起来就是一条清晰管道每一层只做一件事。好处是出问题时定位非常快SQL错了查生成层执行报错查校验层摘要不对查转述层不会出现一团乱麻的情况。2.2 模块职责与关键设计基于四段式链路系统拆成六个模块各自职责和关键设计如下模块主要职责关键设计问题预处理意图识别、时间词归一化、业务词替换决定是否需要走查询不成句的问题直接返回引导语Schema管理器读取库表结构建立元数据缓存按问题关键词做表候选筛选避免全量DDL塞进提示词生成服务调用大模型API返回SQL文本温度参数调为0要求模型只输出被代码块包裹的SQLSQL校验器语法解析、表名白名单、危险语句拦截基于AST做规则校验出现问题返回错误信息给模型重写执行器建立只读连接执行查询并返回结果强制LIMIT、执行超时、连接池隔离日志服务记录用户问题、SQL、结果、耗时审计和调优的第一手素材也是模型评测的标注数据来源这个模块划分的核心思想是“把模型当成一个翻译器而不是决策器”。翻译器负责把自然语言翻译成SQL决策器必须是人肉写好的规则。模型的输出不可控但规则可控两者结合才能做到稳定。2.3 技术选型与取舍选型上我没有追求花哨原则是每一层都可解释、可替换、可降级。大模型服务选择支持函数调用或JSON输出模式的产品API接入或私有化部署都可以关键是输出格式稳定。数据库方面Demo用SQLite最方便生产环境建议接MySQL或PostgreSQL的只读副本。SQL校验用sqlglot这个库它能解析多种方言的SQL把语句转成AST再在AST上做规则检查比写正则靠谱得多。结果摘要不单独训练模型直接用同一套大模型服务输入表格数据让它总结。缓存层用Redis对标准化问题做结果缓存降低重复查询的成本。选型有一个原则值得反复强调不要在任何一层引入不可解释的复杂度。SQL校验器宁可自己写500行规则也不要让模型自己判断SQL对不对因为你根本无法预期模型会给出什么答案。3. 提示词工程与上下文构建系统成败关键3.1 让模型看懂数据库三类上下文信息我能百分之百确定Text-to-SQL最大的坑就是模型不了解你的库。如果上下文里没有表结构模型就只能凭训练记忆里的“通用订单表”来猜测生成出来的SQL几乎不可能在你的库上跑通。我整理了三类必须进上下文的“养料”第一类是Schema信息也就是DDL。这里要特别注意不是把所有表都塞进去而是基于用户问题先做表候选筛选。比如问题里提到“订单”“用户”就只把订单表、用户表、订单明细表的DDL拿出来其他无关表一概不给。拿到了候选表之后还要压缩只保留字段名、类型、注释、主外键标记把默认值、索引、存储引擎这类对SQL生成无用的修饰词全部删掉。第二类是业务口径注释。数据表字段有时候叫status业务里却叫“订单状态”有时候叫is_valid业务里却是“是否有效”。这些映射关系必须写清楚。我在提示词里单独放一个“业务规则”段落每次生成SQL前更新这里面的内容比如“有效订单status为1且deleted_at为空”。第三类是值分布示例。枚举字段模型特别容易猜错。比如status字段的真实值可能是0/1/2模型却写成了paid/pending这就是典型的上下文缺失。把所有枚举字段的真实取值样例放进提示词能让WHERE条件准确率大幅提升。这三类信息怎么组织建议用一个模板结构固定下来不要每次随手写数据库表结构DDL - 订单表orders字段id, user_id, pay_time, status, amount - 用户表users字段id, user_name, region, created_at 业务规则 - 有效订单 status completed - 订单金额使用amount字段单位元 要求 - 只输出可执行SQL - 不使用事务3.2 一套可以直接用的Prompt模板系统提示词我长期用下面这个版本不是最优但足够稳你可以直接抄走你是一个企业数据分析助手。根据用户的问题和提供的表结构生成一条SELECT查询。 约束 1. 只输出一条SQL用sql ... 包裹不要有任何解释。 2. 只能使用给定的表和字段不能臆造。 3. WHERE条件中的字符串值必须使用表中真实存在的值。 4. 默认返回20行如果用户要求“全部”也不得超过200行。 5. 禁止使用事务、插入、更新、删除、DDL语句。用户侧提示词把“裁剪后的DDL 业务规则 用户问题”拼接起来。模型调用时把温度设成0让它输出最保守的结果。生成的返回内容不能直接当SQL用。很多模型会在代码块外写“以下是您需要的SQL”这种废话有些甚至会输出JSON包装。所以代码里要做一个提取函数把markdown代码块的内容剥出来。我见过太多次解析失败最后发现是代码块语言标记写得不统一比如写了sql而不是sql提取时都要兼容。我在工程里还会加一个“自检重写”环节。第一次生成的SQL如果校验不过就把错误信息返回给模型让它根据报错修正SQL最多重试两轮。实测下来这个机制能把整体成功率从大概70%提到90%以上。关键是重试时要把原始错误原样传给模型不要自己缩写否则模型会缺少判断信息。3.3 few-shot设计三张高频场景样例Few-shot对Text-to-SQL的帮助非常明显尤其适合处理聚合、JOIN和时间过滤这几类容易出错的结构。我长期只放三个示例原因很简单示例越多模型反而容易学到示例的风格噪声三个足够覆盖最常见的结构。场景一单表条件筛选。问题是“查询所有已完成订单的总金额”示例SQL为SELECT SUM(amount) FROM orders WHERE status completed。场景二两表关联聚合。问题是“查询每个用户的订单数量”示例SQL为SELECT u.user_name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.user_name。场景三时间过滤加聚合排序。问题是“查询上周各品类的销量排名”示例SQL为SELECT p.category, SUM(oi.quantity) AS total_qty FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON p.id oi.product_id WHERE o.pay_time 2024-01-01 AND o.pay_time 2024-01-08 GROUP BY p.category ORDER BY total_qty DESC LIMIT 20。这里有一个极其重要的细节示例里的表名、字段名必须和真实库完全一致。如果示例用了假表名模型会照抄生成一个看似结构正确但根本跑不起来的SQL。所以few-shot不要从网上随便抄一定要基于自己的schema来写。4. 自动执行查询的安全底线4.1 数据库连接必须是只读的自动执行SQL最可怕的场景是模型被诱导生成一条DELETE FROM orders或者带上了DROP TABLE。提示词里明确禁止这些操作有用吗有一点用但提示词不是防火墙权限才是。我在系统里专门建了一个只读账号只给目标库的SELECT权限应用层永远用这个账号连数据库CREATE USER query_user% IDENTIFIED BY your_password; GRANT SELECT ON analytics_db.* TO query_user%;如果是SQLite这类本地数据库连接时用URI模式打开然后执行PRAGMA query_onlyON从数据库引擎层面禁止任何写操作。生产环境我会再进一步把查询账号指向只读副本而不是主库。原因是慢查询跟模型输出有关即使SQL合法也可能写了一个没走索引的全表扫描。如果直连在线业务库一个坏SQL就可能拖垮核心服务。只读副本即使被打爆也不影响线上业务。4.2 执行前的三道校验闸门SQL生成后要过三道闸门每一道都是纯规则校验不依赖任何模型判断。第一道闸门是语法解析。用sqlglot把SQL解析成AST如果解析失败直接拒绝。解析成功后再判断根节点类型必须是SELECT语句INSERT、UPDATE、DELETE、ALTER等一律拦下。第二道闸门是表名白名单。从AST里提取所有被引用的表名和系统允许访问的表集合做比对。只要出现一个不在白名单里的表直接拒绝执行。这里要小心JION里的表别名AST遍历时要取真实表名。第三道闸门是结果行数强制限制。无论用户怎么问系统都会在原始SQL外面加一层LIMIT 200。明细查询最多返回200行聚合查询则影响不大。这个限制能防止一次查询拉回几百万行数据把内存打满。核心校验代码长这样import sqlglot def validate_sql(sql: str, allowed_tables: set) - str: expr sqlglot.parse_one(sql) if not isinstance(expr, sqlglot.exp.Select): raise ValueError(只允许SELECT查询) for table in expr.find_all(sqlglot.exp.Table): if table.name not in allowed_tables: raise ValueError(f表 {table.name} 不在白名单中) return expr.sql()这三道闸门不是万无一失比如字段拼接绕过、注释内注入等奇技淫巧但配合“只读账号强制Limit执行超时”这四层已经足够把风险压到可接受范围。真正的安全不能靠某一层单独完成必须层层叠加。还有一个不能忽略的细节执行超时。我给每条查询设置了5秒上限超过就杀掉连接。宁可查询失败也不能让一条坏SQL一直占用数据库资源。这个时间可以根据业务调整但一定要有超时机制。4.3 执行留痕与并发控制自动执行不能是黑盒必须全链路留痕。每次用户提问、生成的SQL、校验结果、执行耗时、返回行数、最终摘要全部写进日志表。这些日志首先是审计需要我见过不止一个项目在出事之后拿不出“到底执行了什么SQL”的证据非常被动。日志更是调优素材。我很多Prompt优化都是看日志里的失败case改出来的。比如发现某张表频繁被模型选错就去看日志里的上下文发现字段注释含糊补充注释后错误率立刻下降。没有日志这些优化就全靠拍脑袋。并发控制方面我给系统加了一个信号量同时最多只有10个查询在执行超出部分排队。前端展示也做了节流防止用户快速连点按钮把数据库打挂。数据库连接池单独隔离不让查询链路跟业务链路混用避免互相影响。5. 实操流程从0到1搭建一个最小闭环5.1 准备一个迷你业务库和元数据为了讲清楚我搭了一个三张表的迷你业务库用户表、订单表、订单明细表。DDL大概是这样CREATE TABLE users ( id INTEGER PRIMARY KEY, user_name TEXT, region TEXT, created_at DATETIME ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER, pay_time DATETIME, status TEXT, amount DECIMAL(10, 2) ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER, product_name TEXT, category TEXT, quantity INTEGER, price DECIMAL(10, 2) );表建好以后还要准备一份schema.json把建表语句、字段注释、外键关系、业务规则全放进去作为提示词生成的元数据来源。这里我建议不要每次实时去查information_schema而是把整理好的元数据缓存起来既能控制上下文质量又能减少数据库压力。元数据JSON的结构类似{ tables: { orders: { ddl: CREATE TABLE orders (...), comment: 订单主表pay_time为支付时间, columns: { status: 订单状态completed已完成pending待支付, amount: 订单金额单位元含运费 } } }, business_rules: [ 有效订单 status completed, 统计时段默认取最近30天 ] }这一步看着不起眼实际上是决定整个系统上限的环节。模型能不能生成正确SQL很大程度取决于你对数据库的描述是否精确是不是像“人话”。5.2 核心代码骨架我用Python写了一个最小可运行骨架不绑定任何特定厂商的SDK统一走HTTP接口调大模型import sqlite3 import requests import sqlglot LLM_ENDPOINT YOUR_API_ENDPOINT LLM_API_KEY YOUR_API_KEY MODEL_NAME your_model_name SYSTEM_PROMPT 你是一个企业数据分析助手。根据用户的问题和提供的表结构生成一条SELECT查询。 约束 1. 只输出一条SQL用sql ... 包裹不要有任何解释。 2. 只能使用给定的表和字段不能臆造。 3. WHERE条件中的字符串值必须使用表中真实存在的值。 4. 默认返回20行最多不超过200行。 5. 禁止使用事务、插入、更新、删除、DDL语句。 def llm_chat(system_prompt, user_prompt): resp requests.post( LLM_ENDPOINT, headers{Authorization: fBearer {LLM_API_KEY}}, json{ model: MODEL_NAME, messages: [ {role: system, content: system_prompt}, {role: user, content: user_prompt} ], temperature: 0 }, timeout20 ) resp.raise_for_status() return resp.json()[choices][0][message][content] def extract_sql(raw: str) - str: text raw.strip() if in text: start text.find(sql) if start -1: start text.find() start 3 (4 if raw.find(sql) ! -1 else 0)这里提取逻辑写起来容易乱我建议直接用正则更好理解import re def extract_sql(raw: str) - str: pattern r(?:sql)?\s*(.*?) matches re.findall(pattern, raw, re.DOTALL | re.IGNORECASE) if matches: return matches[0].strip() return raw.strip()执行部分用SQLite的只读模式def safe_execute(sql: str, db_path: str): validate_sql(sql, allowed_tables{users, orders, order_items}) conn sqlite3.connect(ffile:{db_path}?modero, uriTrue) conn.execute(PRAGMA query_onlyON) conn.execute(PRAGMA busy_timeout3000) cur conn.cursor() cur.execute(sql) col_names [desc[0] for desc in cur.description] rows cur.fetchmany(200) conn.close() return col_names, rows这个骨架可以直接跑通一个完整Demo。生产环境建议把SQL校验单独抽成服务模型调用和数据库执行都走异步任务队列但核心逻辑就是上面这些没有更复杂的东西。5.3 一条查询的完整流转演示假设用户问“查询华东地区每个用户的订单数量按数量降序。”系统第一步做预处理识别出“华东地区”和“订单数量”。第二步从元数据里选出用户表和订单表拼接DDL和业务规则给模型。模型的输出可能是SELECT u.user_name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.region 华东 GROUP BY u.user_name ORDER BY order_cnt DESC LIMIT 20校验层检查后确认是SELECT、表名都在白名单、LIMIT已存在。执行层拿到结果集返回给用户一张表格。与此同时结果摘要模型会生成一段文字“华东地区共5位用户有订单其中李明的订单数量最多共12单。”这个流程走完用户感受到的只是一个“问答”背后却经历了完整管道。我最想提醒的一点是结果摘要依赖模型重新解释表格所以SQL里尽量用AS给列起一个可读别名别用原始缩写列名否则摘要生成阶段模型看不懂列名说的话就会很奇怪。6. 常见问题、翻车现场与调优记录6.1 生成的SQL里表名或字段名是错的这类问题出现频率最高现象是模型用了不存在的字段或者把orders写成order又或者把时间字段拼到了错误的表上。排查顺序我建议固定下来先看审计日志里的提示词确认表结构是否正确拼接。再看表名大小写是否和schema一致很多数据库大小写敏感模型输出一个小写就废了。接着看是否有字段重名比如两张表都有created_at模型就会困惑这时需要在schema里显式标注“订单时间字段为orders.pay_time”。最后看枚举值是否写全把真实取值放进提示词能有效减少WHERE条件乱写的问题。还有一个容易被忽略的坑模型会根据训练记忆自动补一个“常识性架构”比如它“觉得”订单表里应该有order_no字段但你的库里根本没有。这种情况下最有效的办法不是改提示词而是把字段清单写得足够完整让模型没有发挥空间。6.2 业务词翻译不准用户说“当前有效订单”库里没有“有效”这个状态只有statuscompleted加上deleted_at IS NULL两个条件。模型不知道这个业务口径自然会生成错误SQL。我的做法是建一个“业务规则库”。规则库本质是一个映射表每个业务词对应一组合法条件表达式。在问题预处理阶段程序先扫描用户问题把“有效订单”替换成标准描述再进模型。比如同一时期规则是有效订单 - 订单状态已完成且未删除那么这个替换后的文本就会进提示词的业务规则段模型生成SQL时就有了依据。这类词会随着使用越来越多所以规则库要做在外部配置里不要写死在代码中。运营、数据分析师都能方便地新增规则而不是每次都要改Prompt重新提测。6.3 上下文太长导致成本飙升DDL塞多了一次请求可能几千token调一次模型成本不低如果还带自检重写和结果摘要链路会调用多次模型成本问题不得不考虑。我的省钱方法有五条一是Schema裁剪用关键词匹配问题里的实体词只选择相关表二是字段裁剪只保留有注释、类型和必要约束的字段把无关列删掉三是温度参数用0避免无意义的随机多样性四是结果摘要用轻量模型不跟主SQL生成共用高规格模型五是高频问题和结果做Redis缓存完全相同的问题直接返回缓存结果不走模型。成本控制不是上线后才考虑的事我在模块设计时就预留了缓存键和裁剪逻辑。否则等账单出来才优化就失去了很多优化空间。6.4 执行慢、数据库被打爆慢查询的根源一般是索引缺失或全表扫描这类问题没法靠提示词解决。系统层的兜底能力是限流和超时这个前面讲过。这里再补一个我在实践中学到的经验如果业务允许把查询系统接到数仓或分析库的只读副本上别直连在线业务库。分析型查询再怎么样也比OLTP重隔离是保护线上服务的关键。此外针对“模型生成了两个大表全量JOIN”这类问题我加了一条规则最大返回行数200的同时最大JOIN表数限制在4张以内。超过4张表JOIN的SQL大概率是模型发生了幻觉。这条规则直接放在校验器里在所有SQL执行前统一判断。7. 做完这个项目后的几点体会以上是方法层面最后聊几句我做完这套系统后的真实体会。Text-to-SQL的真正瓶颈不是模型而是数据资产的规范化程度。模型能不能生成正确SQL很大程度取决于表结构、字段注释、业务口径是不是清晰。很多库的表结构乱得一塌糊涂字段名全是拼音缩写注释一个都没有这种环境下再强的模型也白搭。所以做这类系统之前先花时间整理元数据这个投入比调Prompt值钱得多。自动执行这件事一定要从第一天就当成风险功能来设计。权限、限流、日志、审计这些看起来不性感的模块才是系统能长期活下去的理由。我见过不止一个项目止步在Demo阶段不是因为生成效果不好而是因为没人敢让它接生产数据库。第一版宁可只跑“简单查询”只要把权限和审计做扎实后面才有迭代的资格。最后一点不要追求一步到位。先跑通小闭环让一小部分人用起来积累真实问题再逐步放开范围。系统最怕的不是能力不足而是没有真实反馈。你收集到的每一个失败Case都是下一版模型和Prompt优化的源头活水。如果正在做类似项目我的建议很简单先保证每一条SQL都能被追踪再把体验做顺其他的慢慢来。