998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
400 lines
7.8 KiB
Markdown
400 lines
7.8 KiB
Markdown
# Dify工作流 - 知识点管理SQL脚本
|
||
|
||
## 📋 工作流场景
|
||
|
||
**流程**：清空旧知识点 → Dify AI分析 → 生成新知识点
|
||
|
||
---
|
||
|
||
## 🔧 SQL脚本
|
||
|
||
### 1. 删除单个资料的所有知识点
|
||
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
```
|
||
|
||
**说明**：
|
||
- 物理删除，不可恢复
|
||
- 会删除资料ID为34的所有知识点
|
||
- 自动提交事务
|
||
|
||
---
|
||
|
||
### 2. 使用变量的方式（推荐）
|
||
|
||
```sql
|
||
SET @material_id = 34;
|
||
DELETE FROM knowledge_points WHERE material_id = @material_id;
|
||
SELECT CONCAT('已删除资料 ', @material_id, ' 的所有知识点，共 ', ROW_COUNT(), ' 条') as result;
|
||
```
|
||
|
||
**优点**：
|
||
- 变量可以动态传入
|
||
- 便于日志记录
|
||
- 返回删除数量
|
||
|
||
---
|
||
|
||
### 3. 带验证的删除（完整版）
|
||
|
||
```sql
|
||
-- 步骤1：查看删除前的数量
|
||
SELECT COUNT(*) as count_before
|
||
FROM knowledge_points
|
||
WHERE material_id = 34;
|
||
|
||
-- 步骤2：执行删除
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
|
||
-- 步骤3：验证删除结果（应该为0）
|
||
SELECT COUNT(*) as count_after
|
||
FROM knowledge_points
|
||
WHERE material_id = 34;
|
||
```
|
||
|
||
---
|
||
|
||
### 4. 批量删除多个资料
|
||
|
||
```sql
|
||
DELETE FROM knowledge_points
|
||
WHERE material_id IN (34, 35, 36);
|
||
|
||
SELECT ROW_COUNT() as total_deleted;
|
||
```
|
||
|
||
---
|
||
|
||
### 5. 条件删除（仅删除AI生成的）
|
||
|
||
```sql
|
||
-- 只删除AI生成的知识点，保留手动添加的
|
||
DELETE FROM knowledge_points
|
||
WHERE material_id = 34
|
||
AND source = 1; -- source=1 表示AI生成
|
||
|
||
SELECT ROW_COUNT() as deleted_ai_generated;
|
||
```
|
||
|
||
---
|
||
|
||
## 🎯 Dify工作流集成
|
||
|
||
### 方案A：使用SQL执行节点
|
||
|
||
**节点配置**：
|
||
- 节点类型：`HTTP Request` 或 `Code`
|
||
- 数据库连接：直连MySQL
|
||
|
||
**SQL命令**：
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = {{material_id}};
|
||
```
|
||
|
||
**变量映射**：
|
||
- `{{material_id}}`：从上游节点传入的资料ID
|
||
|
||
---
|
||
|
||
### 方案B：使用HTTP请求调用后端API
|
||
|
||
**API端点**：
|
||
```
|
||
DELETE /api/v1/courses/materials/{material_id}/knowledge-points/batch
|
||
```
|
||
|
||
**请求示例**：
|
||
```bash
|
||
curl -X DELETE \
|
||
"http://kaopeilian-backend-dev:8000/api/v1/courses/materials/34/knowledge-points/batch" \
|
||
-H "Authorization: Bearer {{api_token}}" \
|
||
-H "Content-Type: application/json"
|
||
```
|
||
|
||
---
|
||
|
||
### 方案C：Python代码节点
|
||
|
||
```python
|
||
import pymysql
|
||
|
||
def delete_knowledge_points(material_id):
|
||
"""删除指定资料的所有知识点"""
|
||
|
||
# 数据库连接
|
||
conn = pymysql.connect(
|
||
host='120.79.247.16',
|
||
port=3307,
|
||
user='root',
|
||
password='nj861021',
|
||
database='kaopeilian',
|
||
charset='utf8mb4'
|
||
)
|
||
|
||
try:
|
||
cursor = conn.cursor()
|
||
|
||
# 执行删除
|
||
sql = "DELETE FROM knowledge_points WHERE material_id = %s"
|
||
cursor.execute(sql, (material_id,))
|
||
|
||
# 获取删除数量
|
||
deleted_count = cursor.rowcount
|
||
|
||
# 提交事务
|
||
conn.commit()
|
||
|
||
return {
|
||
"success": True,
|
||
"deleted_count": deleted_count,
|
||
"message": f"已删除资料 {material_id} 的 {deleted_count} 个知识点"
|
||
}
|
||
|
||
except Exception as e:
|
||
conn.rollback()
|
||
return {
|
||
"success": False,
|
||
"error": str(e),
|
||
"message": f"删除失败: {str(e)}"
|
||
}
|
||
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
# Dify中调用
|
||
result = delete_knowledge_points(material_id={{material_id}})
|
||
return result
|
||
```
|
||
|
||
---
|
||
|
||
## 🔍 验证删除结果
|
||
|
||
### 查询语句
|
||
|
||
```sql
|
||
-- 检查是否还有剩余的知识点
|
||
SELECT COUNT(*) as remaining_count
|
||
FROM knowledge_points
|
||
WHERE material_id = 34;
|
||
|
||
-- 应该返回：remaining_count = 0
|
||
```
|
||
|
||
---
|
||
|
||
## ⚠️ 注意事项
|
||
|
||
### 1. 外键约束影响
|
||
|
||
**exam_mistakes表的影响**：
|
||
- `exam_mistakes.knowledge_point_id` 外键约束：`ON DELETE SET NULL`
|
||
- 删除知识点时，相关错题记录的 `knowledge_point_id` 会被设置为 `NULL`
|
||
- 不会阻止删除操作
|
||
|
||
**验证**：
|
||
```sql
|
||
-- 检查是否有错题记录引用
|
||
SELECT COUNT(*) as dependent_count
|
||
FROM exam_mistakes
|
||
WHERE knowledge_point_id IN (
|
||
SELECT id FROM knowledge_points WHERE material_id = 34
|
||
);
|
||
```
|
||
|
||
### 2. 数据恢复
|
||
|
||
**⚠️ 警告**：物理删除后无法恢复！
|
||
|
||
**建议**：删除前备份
|
||
```sql
|
||
-- 备份到临时表
|
||
CREATE TABLE knowledge_points_backup_20251017 AS
|
||
SELECT * FROM knowledge_points WHERE material_id = 34;
|
||
|
||
-- 删除
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
|
||
-- 如需恢复
|
||
-- INSERT INTO knowledge_points SELECT * FROM knowledge_points_backup_20251017;
|
||
```
|
||
|
||
### 3. 并发问题
|
||
|
||
如果Dify工作流并发执行，可能导致问题：
|
||
|
||
**解决方案：使用行锁**
|
||
```sql
|
||
-- 开始事务
|
||
START TRANSACTION;
|
||
|
||
-- 锁定资料记录
|
||
SELECT * FROM course_materials WHERE id = 34 FOR UPDATE;
|
||
|
||
-- 删除知识点
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
|
||
-- 提交
|
||
COMMIT;
|
||
```
|
||
|
||
---
|
||
|
||
## 📊 监控和日志
|
||
|
||
### 删除操作日志
|
||
|
||
```sql
|
||
-- 记录删除操作（可选）
|
||
INSERT INTO system_logs (
|
||
level, type, message,
|
||
user_id, created_at
|
||
) VALUES (
|
||
'INFO',
|
||
'database',
|
||
CONCAT('删除资料 ', 34, ' 的所有知识点，共 ', ROW_COUNT(), ' 条'),
|
||
1,
|
||
NOW()
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## 🎬 完整工作流示例
|
||
|
||
### Dify工作流配置
|
||
|
||
```yaml
|
||
workflow:
|
||
name: "重新生成资料知识点"
|
||
|
||
nodes:
|
||
- id: "1"
|
||
name: "获取资料信息"
|
||
type: "http_request"
|
||
config:
|
||
url: "/api/v1/courses/materials/{{material_id}}"
|
||
method: "GET"
|
||
|
||
- id: "2"
|
||
name: "清空旧知识点"
|
||
type: "code"
|
||
config:
|
||
code: |
|
||
import pymysql
|
||
conn = pymysql.connect(
|
||
host='120.79.247.16', port=3307,
|
||
user='root', password='nj861021',
|
||
database='kaopeilian'
|
||
)
|
||
cursor = conn.cursor()
|
||
cursor.execute(
|
||
"DELETE FROM knowledge_points WHERE material_id = %s",
|
||
(material_id,)
|
||
)
|
||
conn.commit()
|
||
deleted = cursor.rowcount
|
||
conn.close()
|
||
return {"deleted_count": deleted}
|
||
|
||
- id: "3"
|
||
name: "AI分析生成知识点"
|
||
type: "llm"
|
||
config:
|
||
prompt: "分析文档并提取知识点..."
|
||
|
||
- id: "4"
|
||
name: "保存新知识点"
|
||
type: "http_request"
|
||
config:
|
||
url: "/api/v1/courses/{{course_id}}/knowledge-points/batch"
|
||
method: "POST"
|
||
```
|
||
|
||
---
|
||
|
||
## 🔗 数据库连接信息
|
||
|
||
```
|
||
主机: 120.79.247.16
|
||
端口: 3307
|
||
数据库: kaopeilian
|
||
用户: root
|
||
密码: nj861021
|
||
```
|
||
|
||
---
|
||
|
||
## 📝 常见问题
|
||
|
||
### Q1: 删除后前端还显示知识点？
|
||
|
||
**原因**：前端缓存
|
||
|
||
**解决**：
|
||
```javascript
|
||
// 前端刷新数据
|
||
await loadMaterialKnowledgePoints(materialId, true) // 强制刷新
|
||
```
|
||
|
||
### Q2: 删除失败，提示外键约束？
|
||
|
||
**原因**：有其他表引用这些知识点
|
||
|
||
**解决**：
|
||
```sql
|
||
-- 检查依赖
|
||
SELECT * FROM exam_mistakes
|
||
WHERE knowledge_point_id IN (
|
||
SELECT id FROM knowledge_points WHERE material_id = 34
|
||
);
|
||
|
||
-- 如果有依赖，先清空外键或删除依赖记录
|
||
```
|
||
|
||
### Q3: 需要批量删除多个资料的知识点？
|
||
|
||
```sql
|
||
-- 方法1：IN子句
|
||
DELETE FROM knowledge_points
|
||
WHERE material_id IN (34, 35, 36);
|
||
|
||
-- 方法2：子查询（根据课程ID）
|
||
DELETE FROM knowledge_points
|
||
WHERE material_id IN (
|
||
SELECT id FROM course_materials
|
||
WHERE course_id = 1 AND is_deleted = 0
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
## ✅ 快速命令
|
||
|
||
### 仅删除（最简单）
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
```
|
||
|
||
### 删除+验证
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
SELECT COUNT(*) FROM knowledge_points WHERE material_id = 34;
|
||
```
|
||
|
||
### 删除+返回数量
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = 34;
|
||
SELECT ROW_COUNT() as deleted;
|
||
```
|
||
|
||
---
|
||
|
||
**推荐用法**：在Dify中使用最简单的单行命令
|
||
```sql
|
||
DELETE FROM knowledge_points WHERE material_id = {{material_id}};
|
||
```
|
||
|