n8n - 自然语言查询数据库
本文介绍如何使用 n8n 搭建一个基于自然语言的数据库查询系统。通过这个系统，用户可以使用自然语言提问，系统会自动转换为 SQL 查询并返回结果。
1. 系统要求
1.1. 环境要求
- n8n 最新版本
- MySQL 版本 > 5.7.27
- Docker 环境（推荐）
1.2. 配置说明
1.3. Docker 环境配置
- 宿主机地址：
host.docker.internal - 默认端口：3306
1.4. MySQL 连接配置
- 在 n8n 中创建新的 MySQL 凭证
- 配置连接信息：
- 主机：
host.docker.internal - 端口：3306
- 数据库名：你的数据库名
- 用户名：你的用户名
- 密码：你的密码
- 主机：
2. 数据库结构获取
2.1. 查询表结构
以下 SQL 查询可获取数据库中所有表和字段的详细信息：
SELECT
t.TABLE_NAME AS `tablename`,
t.TABLE_COMMENT AS `tablecomment`,
c.COLUMN_NAME AS `columnname`,
c.COLUMN_COMMENT AS `columncomment`,
c.COLUMN_TYPE AS `columntype`
FROM
information_schema.TABLES t
JOIN
information_schema.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_SCHEMA = DATABASE()
ORDER BY
t.TABLE_NAME, c.ORDINAL_POSITION; -- 按表和字段顺序排序
2.2. 数据结构转换
将查询结果转换为便于 AI 理解的文本格式：
const result = {};
items.forEach(item => {
const tableName = item.json.tablename;
// 如果该表名还没添加到 result，则初始化
if (!result[tableName]) {
result[tableName] = {
tablename: tableName,
tablecomment: item.json.tablecomment || "",
fields: [] // 用于存储该表的所有字段
};
}
// 添加字段信息
result[tableName].fields.push({
columnname: item.json.columnname,
columncomment: item.json.columncomment || "",
columntype: item.json.columntype || ""
});
});
// 转换为数组返回
const tempRes= Object.values(result).map(table => ({ json: table }));
return [
{
json: {
fileContent: tempRes.map(item => {
let tableName = `# ${item.json.tablecomment} ${item.json.tablename}`;
let fields = item.json.fields.map(field => `- ${field.columnname} ${field.columntype} ${field.columncomment}`).join("\n");
return `${tableName}\n${fields}\n`;
}).join("\n")
}
}
];
3. AI 查询配置
3.1. Prompt 模板
以下是用于 AI 生成 SQL 的 prompt 模板：
你是一个经验丰富的DBA，精通MySQL数据库。你会根据用户的需求和数据结构，生成一个查询语句。
只需要返回一个可执行的SQL语句，不需要任何解释。
数据结构：
[数据库结构将在这里自动插入]
# 示例
用户：查询职工总人数
回答：SELECT COUNT(*) AS total_workers FROM zm_workers WHERE history IS NULL AND state = 1;
用户：查询在职员工的平均工资
回答：SELECT AVG(salary) AS avg_salary FROM zm_workers WHERE state = 1 AND history IS NULL;
3.2. 工作流说明
本工作流包含两个主要部分：
-
数据结构生成工作流
- 用于在数据库结构发生变化时更新结构文件
- 建议定期执行以保持结构最新
-
自然语言查询工作流
- 接收用户自然语言输入
- 通过 AI 转换为 SQL
- 执行查询并返回结果
- 支持导出 CSV 格式
4. 使用说明
- 首次使用时执行数据结构生成工作流
- 在聊天界面输入自然语言问题
- 系统自动生成并执行 SQL 查询
- 查询结果将自动保存为 CSV 文件
5. 注意事项
- 定期更新数据结构文件以确保 AI 使用最新的表结构
- SQL 查询默认有超时限制，过于复杂的查询可能失败
- 建议在查询中加入适当的限制条件以提高性能
- 敏感数据请通过权限管理进行控制
6. 最终工作流 json
{
"name": "自然语言查询MySQL",
"nodes": [
{
"parameters": {},
"type": "n8n-nodes-base.manualTrigger",
"typeVersion": 1,
"position": [
-1580,
-360
],
"id": "1c2d2fce-eea5-4234-8113-87fb765c1cc8",
"name": "When clicking ‘Test workflow’"
},
{
"parameters": {
"operation": "executeQuery",
"query": "SELECT \n t.TABLE_NAME AS `tablename`,\n t.TABLE_COMMENT AS `tablecomment`,\n c.COLUMN_NAME AS `columnname`,\n c.COLUMN_COMMENT AS `columncomment`,\n c.COLUMN_TYPE AS `columntype`\nFROM \n information_schema.TABLES t\nJOIN \n information_schema.COLUMNS c \n ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME\nWHERE \n t.TABLE_SCHEMA = DATABASE()\nORDER BY \n t.TABLE_NAME, c.ORDINAL_POSITION; -- 按表和字段顺序排序",
"options": {}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.4,
"position": [
-1340,
-360
],
"id": "63ea7157-38a8-4659-a874-9b8802527c15",
"name": "MySQL",
"credentials": {
"mySql": {
"id": "cqr9Jh7LF7M2HEgG",
"name": "MySQL连接凭证"
}
}
},
{
"parameters": {
"jsCode": "const result = {};\nitems.forEach(item => {\n const tableName = item.json.tablename;\n\n // 如果该表名还没添加到 result，则初始化\n if (!result[tableName]) {\n result[tableName] = {\n tablename: tableName,\n tablecomment: item.json.tablecomment || \"\",\n fields: [] // 用于存储该表的所有字段\n };\n }\n\n // 添加字段信息\n result[tableName].fields.push({\n columnname: item.json.columnname,\n columncomment: item.json.columncomment || \"\",\n columntype: item.json.columntype || \"\"\n });\n});\n\n// 转换为数组返回\nconst tempRes= Object.values(result).map(table => ({ json: table }));\nreturn [\n {\n json: {\n fileContent: tempRes.map(item => {\n let tableName = `# ${item.json.tablecomment} ${item.json.tablename}`;\n let fields = item.json.fields.map(field => `- ${field.columnname} ${field.columntype} ${field.columncomment}`).join(\"\\n\");\n return `${tableName}\\n${fields}\\n`;\n }).join(\"\\n\")\n }\n }\n];"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
-1140,
-360
],
"id": "376f9dd2-a779-4865-b53f-914c5e2081e5",
"name": "Code"
},
{
"parameters": {
"operation": "toText",
"sourceProperty": "fileContent",
"options": {}
},
"type": "n8n-nodes-base.convertToFile",
"typeVersion": 1.1,
"position": [
-920,
-360
],
"id": "011fb3b4-ecb3-419c-b473-4f998f21e02d",
"name": "Convert to File"
},
{
"parameters": {
"operation": "write",
"fileName": "/home/node/hrmshcema.txt",
"options": {}
},
"type": "n8n-nodes-base.readWriteFile",
"typeVersion": 1,
"position": [
-700,
-360
],
"id": "a105f1c7-2c2e-4e2d-bb46-36980091f600",
"name": "Read/Write Files from Disk"
},
{
"parameters": {
"content": "# 生成数据结构文件\n** 请在数据库结构发生变化的时候执行这个工作流 **",
"height": 320,
"width": 1180,
"color": 5
},
"type": "n8n-nodes-base.stickyNote",
"typeVersion": 1,
"position": [
-1680,
-500
],
"id": "8d3f0c8f-c8f9-4b78-b9d6-07b2d5029876",
"name": "Sticky Note"
},
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.chatTrigger",
"typeVersion": 1.1,
"position": [
-1620,
20
],
"id": "6fcb11b4-cf03-42b7-94a5-7d64bb505df8",
"name": "When chat message received",
"webhookId": "aa9571f3-5576-4f00-8150-b65c5cabea33"
},
{
"parameters": {
"fileSelector": "/home/node/hrmshcema.txt",
"options": {}
},
"type": "n8n-nodes-base.readWriteFile",
"typeVersion": 1,
"position": [
-1400,
20
],
"id": "f2f88e45-65c1-4897-a441-bbed5592a95f",
"name": "Read/Write Files from Disk1"
},
{
"parameters": {
"operation": "text",
"options": {}
},
"type": "n8n-nodes-base.extractFromFile",
"typeVersion": 1,
"position": [
-1180,
20
],
"id": "613e3c79-87bf-484c-9a2d-d80a37290b9b",
"name": "Extract from File"
},
{
"parameters": {
"promptType": "define",
"text": "={{ $('When chat message received').item.json.chatInput }}",
"options": {
"systemMessage": "=你是一个经验丰富的DBA，精通MySQL数据库。你会根据用户的需求和数据结构，生成一个查询语句。只需要返回一个可执行的SQL语句，不需要任何解释。\n数据结构：{{ $json.data }}\n# 对话示例\n用户：查询职工总人数\n回答：select count(*) as totalworkers from zm_workers where history is null and state=1;"
}
},
"type": "@n8n/n8n-nodes-langchain.agent",
"typeVersion": 1.7,
"position": [
-960,
20
],
"id": "784c1ce8-8d06-4d83-acf6-62adee455857",
"name": "AI Agent"
},
{
"parameters": {
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.lmChatDeepSeek",
"typeVersion": 1,
"position": [
-1020,
220
],
"id": "ece860d1-8368-403e-b43e-761dfef82909",
"name": "DeepSeek Chat Model",
"credentials": {
"deepSeekApi": {
"id": "ZICWuqWxccFkm0kD",
"name": "DeepSeek account"
}
}
},
{
"parameters": {
"operation": "executeQuery",
"query": "{{ $json.output }}",
"options": {}
},
"type": "n8n-nodes-base.mySql",
"typeVersion": 2.4,
"position": [
-600,
20
],
"id": "c3a02b5a-a56c-405d-a657-fff27ffbbcd6",
"name": "MySQL1",
"credentials": {
"mySql": {
"id": "cqr9Jh7LF7M2HEgG",
"name": "MySQL连接凭证"
}
}
},
{
"parameters": {
"options": {}
},
"type": "n8n-nodes-base.convertToFile",
"typeVersion": 1.1,
"position": [
-360,
20
],
"id": "ca7b119d-c69d-4174-8951-77a3486e1f29",
"name": "Convert to File1"
},
{
"parameters": {
"operation": "write",
"fileName": "=/home/node/selectResult.csv",
"options": {}
},
"type": "n8n-nodes-base.readWriteFile",
"typeVersion": 1,
"position": [
-140,
20
],
"id": "12e8607f-e7b1-44dd-a109-fc013c8e5714",
"name": "Read/Write Files from Disk2"
},
{
"parameters": {
"content": "# 自然语言查询MySQL数据库",
"height": 480,
"width": 1860
},
"type": "n8n-nodes-base.stickyNote",
"typeVersion": 1,
"position": [
-1680,
-140
],
"id": "58b555d1-a698-49f4-ba7d-b716a2b0d998",
"name": "Sticky Note1"
}
],
"pinData": {},
"connections": {
"When clicking ‘Test workflow’": {
"main": [
[
{
"node": "MySQL",
"type": "main",
"index": 0
}
]
]
},
"MySQL": {
"main": [
[
{
"node": "Code",
"type": "main",
"index": 0
}
]
]
},
"Code": {
"main": [
[
{
"node": "Convert to File",
"type": "main",
"index": 0
}
]
]
},
"Convert to File": {
"main": [
[
{
"node": "Read/Write Files from Disk",
"type": "main",
"index": 0
}
]
]
},
"When chat message received": {
"main": [
[
{
"node": "Read/Write Files from Disk1",
"type": "main",
"index": 0
}
]
]
},
"Read/Write Files from Disk1": {
"main": [
[
{
"node": "Extract from File",
"type": "main",
"index": 0
}
]
]
},
"Extract from File": {
"main": [
[
{
"node": "AI Agent",
"type": "main",
"index": 0
}
]
]
},
"DeepSeek Chat Model": {
"ai_languageModel": [
[
{
"node": "AI Agent",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"AI Agent": {
"main": [
[
{
"node": "MySQL1",
"type": "main",
"index": 0
}
]
]
},
"MySQL1": {
"main": [
[
{
"node": "Convert to File1",
"type": "main",
"index": 0
}
]
]
},
"Convert to File1": {
"main": [
[
{
"node": "Read/Write Files from Disk2",
"type": "main",
"index": 0
}
]
]
}
},
"active": false,
"settings": {
"executionOrder": "v1"
},
"versionId": "0bef0e02-a98d-4fae-b769-7d37783498ee",
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "d36ca678f8b66bb6f5a018613aa17402c64553960e4ec06e7eb47ef5ce25102f"
},
"id": "uIbPFnIcResZoxPG",
"tags": []
}