设计一个用自然语言查数据库的 Text-to-SQL 系统

详细解析#

第一步：澄清需求#

• 用户：业务、运营人员，不会写 SQL，但对数据口径有基本的认识
• 数据源：业务库的只读副本还是数据仓库？有几十张表还是几百张表
• 权限：不同角色、不同地区的人能看的表、字段、数据行是否不同？有没有薪资、手机号这类敏感字段
• 准确性：错误的数字比没有数字更危险，宁可拒答也不能乱答
• 输出：表格、图表、文字结论；是否支持多轮追问（"按月再拆一下"）

第二步：整体架构#

用户问题（+ 对话历史）
  ▼
① 理解：补全指代、把"上个月"换算成具体日期、识别指标和维度
  ▼
② 检索语义层：相关的表和字段、指标口径、枚举值、示例问答（向量 + 关键词）
  ▼
③ 生成 SQL（结构化输出：SQL + 用到的表 + 简要说明）
  ▼
④ 校验：解析语法树 → 只允许单条 SELECT → 表权限 → 行数上限 ──失败──┐
  ▼                                                                │
⑤ 执行：只读账号、只读副本或数仓、语句超时 ─────────出错─────────────┤
  ▼                                            把 SQL 和错误信息反馈给模型修正（最多 N 次）
⑥ 解释：结果摘要 + 图表推荐 + 展示 SQL 和口径，用户可以纠错

第三步：核心模块#

语义层：原始表结构里，字段名往往看不出含义（如 gmv_amt_d），也没有业务规则。需要人工维护：

• 表和字段的中文说明，枚举值的含义（status = 3 表示已发货）
• 指标口径："活跃用户"怎么算、"GMV"是否扣除退款，最好直接给出计算它的 SQL 片段
• 表之间的关联路径、默认的过滤条件（排除测试账号、已删除的数据）、时区
• 示例问答：真实的问题和审核过的 SQL，作为少样本示例

表多的时候不能全部放进 Prompt，先检索出相关的表和示例。另一个思路是让模型只选择"指标 + 维度 + 过滤条件"，由语义层按定义好的口径生成 SQL，模型的自由度小了，出错的机会也少了。

SQL 校验：用 SQL 解析库得到语法树再检查，比正则可靠（见代码示例）。只检查"是不是 SELECT"不够：SELECT ... INTO OUTFILE 能写文件，FOR UPDATE 会加锁，SLEEP() 能长时间占住连接。所以：

• 只允许单条 SELECT，不允许 INTO 和加锁读，访问的表必须在白名单里
• 没有 LIMIT 的补上，LIMIT 超过上限的拒绝并让模型改写
• 设置超时：MySQL 用 max_execution_time（单位毫秒，只对只读的 SELECT 生效），PostgreSQL 用 statement_timeout
• 执行前可以先 EXPLAIN，预估扫描行数太大的拒绝执行

执行和纠错：执行出错（字段不存在、语法错误）时，把 SQL 和错误信息交给模型修正，最多重试两三次。结果为空也值得怀疑，常见原因是过滤值写错了（"北京"和"北京市"），可以把相关字段的真实取值提供给模型再试一次。

结果解释：只把结果的摘要（行数、前几行、汇总值）交给模型写结论，要求结论里的数字必须来自查询结果，不能自己推算；图表类型按结果的形状用规则推荐（时间序列用折线图，分类对比用柱状图，单个数字用指标卡）。界面上始终展示执行的 SQL 和使用的口径，用户可以标记"结果不对"。

第四步：关键难点#

权限不能靠提示词。用户问"把所有员工的工资列出来"，模型可能照做，所以权限必须在模型之外落实：

1. 数据库层：每个角色一个只读账号，只授予允许访问的视图；敏感字段在视图里脱敏或不暴露；按地区等维度的行级权限，用视图或数据库的行级安全策略实现
2. 应用层：校验 SQL 只访问白名单里的表，越权的 SQL 不执行，并记录下来
3. Prompt 层：只把用户有权访问的表放进上下文，连表结构都不暴露

数据库层是真正的边界，另外两层用来更早发现问题、给出友好的提示。还要注意聚合结果也可能泄露个人信息：一个部门只有一个人时，"部门平均工资"就是他的工资，所以敏感指标要求分组人数不少于某个阈值。

SQL 能执行，结果却是错的。这是最危险的情况，因为用户很难察觉。应对：口径由语义层统一定义；结果明显超出合理范围时提示（比如转化率超过 100%）；展示 SQL 和口径，方便懂行的人核对；用户纠正过的问题和 SQL 审核后加入示例库。

评测：从真实提问中整理出"问题 + 标准 SQL + 标准结果"，覆盖常见指标、多表关联、时间计算、无法回答和无权回答的问题。主要指标是执行结果的准确率，而不是 SQL 字符串是否一致，写法不同、结果相同的 SQL 很多；再加上 SQL 的合法率、拒答的正确率。表结构或语义层变化后都要回归，见评测集的构建。

第五步：扩展与优化#

• 缓存：相同的问题、相同的数据版本，直接复用 SQL 和结果
• 性能保护：只在只读副本或数仓上执行，限制每个用户的并发查询数，避免大查询拖垮业务库
• 交互：结果出来后，允许用户直接在界面上调整时间范围、维度和筛选条件，不必每次都用自然语言重新描述

代码示例#

用 node-sql-parser 做应用层校验，返回的错误信息可以直接反馈给模型：

import sqlParser from 'node-sql-parser' // CommonJS 包，在 Node 的 ESM 里用默认导入

const parser = new sqlParser.Parser()
const opt = { database: 'MySQL' }

export function checkSql(sql: string, allowedTables: Set<string>) {
  let ast
  try {
    ast = parser.astify(sql, opt)
  } catch (e) {
    return { ok: false, error: `SQL 语法错误：${(e as Error).message}` }
  }
  const stmts = Array.isArray(ast) ? ast : [ast]
  if (stmts.length !== 1 || stmts[0].type !== 'select') {
    return { ok: false, error: '只允许单条 SELECT 语句' }
  }
  const stmt = stmts[0] as any
  if (stmt.into?.position || stmt.locking_read) {
    return { ok: false, error: '不允许 SELECT ... INTO 和加锁读' }
  }
  // tableList 返回形如 "select::库名::表名" 的字符串，没写库名时是 "null"。
  // 带库名的引用一律拒绝；CTE 的名字也会出现在列表里，为了简单同样拒绝，Prompt 里要求用子查询代替 WITH
  const denied = parser.tableList(sql, opt).filter((item) => {
    const [, db, table] = item.split('::')
    return db !== 'null' || !allowedTables.has(table)
  })
  if (denied.length) return { ok: false, error: `没有权限访问：${denied.join(', ')}` }
  return { ok: true }
}

不能简单地把 CTE 的名字从列表里排除：CTE 可以和真实的表同名，排除这个名字的同时，也放过了 CTE 内部对那张真实表的访问。应用层的检查难免有遗漏（数据库方言、解析库不支持的语法），所以真正的权限边界是数据库账号：只读、只能访问允许的视图、没有写文件之类的权限。

面试官可能追问#

易错点#

• 只把表结构交给模型，没有字段含义和指标口径，生成的 SQL 能执行，口径却是错的
• 以为"只允许 SELECT"就安全了：SELECT 也能写文件、加锁、长时间占用连接
• 在提示词里写"不要查询薪资表"来做权限控制
• 用 SQL 字符串是否一致来评测，而不是比较执行结果