n8n - 自然语言查询数据库

1- n8n 自然语言查询数据库

本文介绍如何使用 n8n 搭建一个基于自然语言的数据库查询系统。通过这个系统，用户可以使用自然语言提问，系统会自动转换为 SQL 查询并返回结果。

1.1- 系统要求

1.1.1- 环境要求

n8n 最新版本
MySQL 版本 > 5.7.27
Docker 环境（推荐）

1.1.2- 配置说明

1.1.2.1- Docker 环境配置

宿主机地址: host.docker.internal
默认端口: 3306

1.1.2.2- MySQL 连接配置

在 n8n 中创建新的 MySQL 凭证
配置连接信息：
- 主机: host.docker.internal
- 端口: 3306
- 数据库名: 你的数据库名
- 用户名: 你的用户名
- 密码: 你的密码

1.2- 数据库结构获取

1.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; -- 按表和字段顺序排序

1.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")
    }
  }
];

1.3- AI 查询配置

1.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;

1.3.2- 工作流说明

本工作流包含两个主要部分：

数据结构生成工作流
- 用于在数据库结构发生变化时更新结构文件
- 建议定期执行以保持结构最新
自然语言查询工作流
- 接收用户自然语言输入
- 通过 AI 转换为 SQL
- 执行查询并返回结果
- 支持导出 CSV 格式

1.4- 使用说明

首次使用时执行数据结构生成工作流
在聊天界面输入自然语言问题
系统自动生成并执行 SQL 查询
查询结果将自动保存为 CSV 文件

1.5- 注意事项

定期更新数据结构文件以确保 AI 使用最新的表结构
SQL 查询默认有超时限制，过于复杂的查询可能失败
建议在查询中加入适当的限制条件以提高性能
敏感数据请通过权限管理进行控制

1.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": []
}

#自动化 #数据库

#n8n #AI #Docker #MySQL #自然语言处理

AI 一键生成 Obsidian 高颜值白板脑图！上一篇

如何获取 Telegram Chat ID - 完整指南下一篇