Building an AI‑Powered MySQL Query Assistant with MaxKB and Alibaba Cloud Bailei Model

1. Scenario Description

Connect an AI large language model to a MySQL structured database to enable conversational queries, quickly understand data, perform simple aggregation, and achieve Q&A functionality.

2. Resource Preparation

AI Large Language Model : Alibaba Cloud Bailei base model qwen-max

MySQL : MySQL database with sample tables (training certification center shop operation data)

MaxKB : Open‑source knowledge‑base Q&A system based on LLM and RAG

3. Operation Steps

Step 1: Apply for Alibaba Cloud Bailei model API domain and APIKey

Refer to the following link for detailed instructions: https://maxkb.cn/docs/user_manual/model/bailian_model/

Step 2: Install and Deploy MaxKB

Complete the installation and deployment by following the link below. Default account credentials: admin/MaxKB@123..

https://maxkb.cn/docs/installation/online_installtion/

Step 3: Connect Bailei Model in MaxKB

Enter System Settings → Model Settings and configure the Bailei model as shown:

Step 4: Create MySQL Query Function

In Function Library → Built‑in Functions , select the MySQL query function, click Add , then enable the function on the function‑library tab and set the startup parameters (connection info for the target MySQL database). See screenshots below.

The startup parameters contain the connection details of the MySQL database that holds the training certification center operational data (see the following images for the exact configuration).

Step 5: Create the Training Certification Center AI Assistant Application

Create an AI assistant application as illustrated below; the following sections describe each node in detail.

1. Basic Information

Enter the application name, description, opening greeting, etc.; keep other parameters at their defaults.

2. Semantic‑to‑SQL

1) Define the semantic‑to‑SQL AI node (select the Bailei model configured in Step 3)

2) Input system role and prompt

System role prompt: Define the AI node as a SQL expert with text‑to‑SQL capability.

# 角色
你是一位资深的MYSQL数据库SQL专家，具备深厚的专业知识和丰富的实践经验。你能够精准理解用户的文本描述，并生成准确可执行的SQL语句。
### 技能1: 生成SQL语句
1. 仔细分析用户提供的文本描述，明确用户需求。
2. 根据对用户需求的理解，生成符合MYSQL数据库语法的准确可执行的SQL语句。

Prompt: Provide the AI with the table structures (DDL) of the seven data tables, the user question, and answer requirements (output format, date conversion, example SQL, etc.).

# 数据表结构
# 表 1: 流量分布表（edu_distribution）
### 表说明
介绍培训认证中心店铺访问流量在全国各地的分布情况，包含每个地市区域的店铺浏览量（PV）、店铺访客数(UV)、支付人数、支付转化率等信息
### DDL语句
CREATE TABLE `edu_distribution` (
  `Date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '日期',
  `Current_Ranking` int DEFAULT NULL COMMENT '当前排名',
  `Region` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地市区域',
  `Store_Page_Views (PV)` int DEFAULT NULL COMMENT '店铺浏览量(PV)',
  `Store_Unique_Visitors (UV)` int DEFAULT NULL COMMENT '店铺访客数(UV)',
  `Paying _Users` int DEFAULT NULL COMMENT '支付人数',
  `Payment_Conversion_Rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付转化率'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='流量分布：介绍培训认证中心店铺访问流量在全国各地的分布情况';
# 表 2: 商品课程的交易访问情况表（edu_learn）
### 表说明
这是一张培训认证中心课程商品的交易访问效果数据，其中包含了每一天每个商品的访客数、浏览数、订阅量、售前访客数、售前浏览量、支付人数、支付金额、支付转化率等信息。
### DDL语句
CREATE TABLE `edu_learn` (
  `Data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '日期',
  `Product_ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品ID',
  `Produc_ Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
  `Product_Type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品类型',
  `Number_of_Visitors` int DEFAULT NULL COMMENT '访客数',
  `Number_of_Views` int DEFAULT NULL COMMENT '浏览量',
  `Number_of_Subscriptions` int DEFAULT NULL COMMENT '订阅量',
  `Number_of_Pre-sales_Visitors` int DEFAULT NULL COMMENT '售前访客数',
  `presalesPageViewssales_Page_Views` int DEFAULT NULL COMMENT '售前浏览量',
  `Payers` int DEFAULT NULL COMMENT '支付人数',
  `Payment_Amount` double DEFAULT NULL COMMENT '支付金额',
  `Payment_Conversion_Rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付转化率'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 表 3: 商品课程订单详情表（edu_orders）
### 表说明
这是一张培训认证中心的订单详情表，记录了每条订单的内部订单号、订单时间、订单状态、渠道来源、商品ID、商品名称、商品类型、商品数量、总价、订单优惠折扣合计、订单实收金额、订单已成功退款金额（出）。
### DDL语句
CREATE TABLE `edu_orders` (
  `ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '内部订单号',
  `Date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付时间',
  `Status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '订单状态',
  `Channel_Source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '渠道来源',
  `Produc_ Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
  `Product_Type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品类型',
  `Produc_ No` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品数量',
  `Total_Price` double DEFAULT NULL COMMENT '总价',
  `Total_Order_Discount` double DEFAULT NULL COMMENT '订单优惠折扣合计',
  `Actual_Payment_Received` double DEFAULT NULL COMMENT '订单实收金额',
  `Total_Refunded_Amount` int DEFAULT NULL COMMENT '订单已成功退款金额（出）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 表 4: 商品课程学员学习情况表（edu_study）
### 表说明
这是一张记录培训认证中心学员学习情况的表，其中包含每日的学习人数、总学习时长(小时)、人均学习时长(小时)、课程学习次数、已学习的课程数、课程完课率。
### DDL语句
CREATE TABLE `edu_study` (
  `Data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '日期',
  `Number_of_Learners` int DEFAULT NULL COMMENT '学习人数',
  `Total_Learning_Duration` double DEFAULT NULL COMMENT '总学习时长(小时)',
  `Average_Learning_hours` double DEFAULT NULL COMMENT '人均学习时长(小时)',
  `Number_of_Course_Sessions` int DEFAULT NULL COMMENT '课程学习次数',
  `Number_of_Courses_Completed` int DEFAULT NULL COMMENT '已学习的课程数',
  `Course_Completion_Rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '课程完课率'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 表 5: 商品课程下单交易情况表（edu_transaction）
### 表说明
交易情况表，记录了2024年到2025年的每日交易情况，包含交易日期、店铺访客数、下单用户数、下单金额（元）、支付人数、支付金额（元）、客单价（元）、访客‑下单转化率、下单‑支付转化率、访客‑支付转化率。
### DDL语句
CREATE TABLE `edu_transaction` (
  `Date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '日期',
  `StoreVisitors` int DEFAULT NULL COMMENT '店铺访客数',
  `OrderUsers` int DEFAULT NULL COMMENT '下单用户数',
  `OrderAmount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '下单金额(单位:元)',
  `Payers` int DEFAULT NULL COMMENT '支付人数',
  `PaymentAmount` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '支付金额(单位:元)',
  `AvgOrderValue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '客单价(单位:元)',
  `VTOR_CR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '访客‑下单转化率',
  `OTP_CR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '下单‑支付转化率',
  `VTP_CR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '访客‑支付转化率'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 表 6: 店铺商品课程流量访问趋势情况表（edu_trend）
### 表说明
记录每天的流量趋势信息，包括店铺浏览量(PV)、店铺访客数(UV)、商品浏览量、商品访客数。
### DDL语句
CREATE TABLE `edu_trend` (
  `Date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '日期',
  `StorePV` int DEFAULT NULL COMMENT '店铺浏览量(PV)',
  `StoreUV` int DEFAULT NULL COMMENT '店铺访客数(UV)',
  `ProductPV` int DEFAULT NULL COMMENT '商品浏览量',
  `ProductUV` int DEFAULT NULL COMMENT '商品访客数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 表 7: 店铺用户情况表（edu_users）
### 表说明
记录每日新增用户、店铺访客数、累计用户、支付人数、累计支付人数。
### DDL语句
CREATE TABLE `edu_users` (
  `Date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '日期',
  `NewUsers` int DEFAULT NULL COMMENT '新增用户',
  `StoreVisitors` int DEFAULT NULL COMMENT '店铺访客数',
  `TotalUsers` int DEFAULT NULL COMMENT '累计用户',
  `Payers` int DEFAULT NULL COMMENT '支付人数',
  `TotalPayers` int DEFAULT NULL COMMENT '累计支付人数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='...';
# 用户问题：
{{开始.question}}
# 回答要求：
- 生成的SQL语句必须符合MYSQL数据库的语法规范。
- 不要使用 Markdown 或 SQL 代码块，禁止添加额外说明。
- 直接输出符合 MySQL 标准的 SQL 语句，纯文本展示。
- 所有时间字段为 varchar(255)，需使用 STR_TO_DATE 将其转换为 Date，例如：STR_TO_DATE(`Date`, '%Y/%m/%d') >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
- 示例 1：自然语言描述：“近一个月用户主要来自哪些区域？”
SELECT `Region`, SUM(`Paying _Users`) AS Total_Paying_Users FROM `edu_distribution` WHERE STR_TO_DATE(`Date`, '%Y/%m/%d') >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) GROUP BY `Region` ORDER BY Total_Paying_Users DESC LIMIT 1;
- 示例 2：自然语言描述：“2022‑2025 年每年的实际收入是多少元？”
SELECT YEAR(STR_TO_DATE(Date, '%Y/%m/%d')) AS Year, SUM(Actual_Payment_Received) AS Actual_Income FROM edu_orders WHERE STR_TO_DATE(Date, '%Y/%m/%d') BETWEEN '2022-01-01' AND '2025-12-31' GROUP BY Year ORDER BY Year;
3. Add SQL Extraction AI Node
Same configuration as the semantic‑to‑SQL node, used to confirm that the generated content is pure SQL.
4. Add MySQL Query Function Node
Click
Add Component → Function
(the function created in Step 4), link it with the SQL extraction node, and set the input parameter to:
SQL Extraction > AI Answer Content
. Other settings remain default.
5. Add AI Answer Node
1) Model Definition: Alibaba Cloud Bailei
2) System Role: Set as an operations management expert that can translate SQL results into plain language.
#系统角色
你是一个培训认证中心运营管理专家，可以将sql查询结果用通俗的语言描述出来
3) Prompt: Same as the semantic‑to‑SQL prompt, plus instructions for the AI to express the SQL results in natural language.
4. Debug and Verify
Enter common questions to test the assistant and click
Execute Details
to view the process.
Verification 1: Which regions contributed the most paying users in the past year?
Verification 2: Show the top 10 orders with the highest amount in the order details table.
Verification 3: What is the actual revenue for each year from 2022 to 2025?