📋 目录

  1. 概述
  2. 核心概念
  3. 实际案例：工单列表多表联查
  4. 最佳实践
  5. 性能优化
  6. 常见问题

概述

Prisma 虽然提供了优雅的 ORM 操作，但在复杂的多表联查场景下，原生 SQL 往往更高效、更灵活。

为什么使用原生 SQL？

场景 Prisma ORM 原生 SQL
简单单表查询 ✅ 推荐 ⚠️ 过度
多表联查 ⚠️ 复杂 ✅ 推荐
动态 WHERE 条件 ⚠️ 困难 ✅ 优秀
N+1 查询风险 ⚠️ 易出现 ✅ 规避
复杂聚合计算 ⚠️ 需后处理 ✅ 数据库处理
性能要求高 ⚠️ 中等 ✅ 最优

核心概念

Prisma 执行原生 SQL 的方法

1️⃣ $queryRaw - 类型安全（推荐用于简单查询）
// 参数化查询，防止 SQL 注入
const result = await prisma.$queryRaw`
  SELECT * FROM users WHERE id = ${userId}
`;

优点：

  • 类型检查安全
  • 自动参数绑定
  • 模板字符串形式清晰

缺点：

  • 不支持动态 SQL 拼接
  • 不灵活
2️⃣ $queryRawUnsafe - 灵活但需自己处理参数绑定（推荐用于复杂查询）
// 手动参数绑定，支持动态 SQL
const sql = "SELECT * FROM users WHERE id = ? AND status = ?";
const result = await prisma.$queryRawUnsafe(sql, userId, status);

优点：

  • 完全灵活的 SQL 拼接
  • 支持动态 WHERE 条件
  • 性能最优

缺点：

  • 需要手动管理参数
  • 需要开发者防止 SQL 注入

参数绑定的两种方式

方式 A：使用 ? 占位符（推荐）
const params = ['value1', 'value2'];
const sql = "SELECT * FROM table WHERE col1 = ? AND col2 = ?";
const result = await prisma.$queryRawUnsafe(sql, ...params);
方式 B：使用命名参数
const result = await prisma.$queryRaw`
  SELECT * FROM table WHERE col1 = ${value1} AND col2 = ${value2}
`;

实际案例：工单列表多表联查

📌 业务需求

查询工单列表，支持：

  • 工单号模糊搜索
  • 事件号模糊搜索
  • 客户名称模糊搜索（需要 JOIN customers 表）
  • 工单状态精确查询
  • 创建时间范围查询
  • 关联用户名称（质检人员、维修人员、创建人）

📊 数据表结构

work_order (工单表)
├── id (BigInt)
├── order_no (工单号)
├── event_no (事件号)
├── order_status (工单状态)
├── customer_id (客户 ID - 外键)
├── current_handler_id (当前处理人 ID - 外键)
├── inspection_person_id (质检人员 ID - 外键)
├── repair_person_id (维修人员 ID - 外键)
├── create_by (创建人 ID - 外键)
└── create_time (创建时间)

customers (客户表)
├── id
└── name (客户名称)

users (用户表)
├── id
└── name (用户名称)

🔧 完整实现代码

步骤 1：定义查询参数 DTO
// src/work-orders/dto/query-work-order.dto.ts
import { IsOptional, IsString, IsInt, IsDateString, Min, Max, IsIn } from 'class-validator';
import { Transform } from 'class-transformer';

export class QueryWorkOrderDto {
  // 分页参数
  @IsOptional()
  @Transform(({ value }) => parseInt(value))
  @IsInt()
  @Min(1)
  page?: number = 1;

  @IsOptional()
  @Transform(({ value }) => parseInt(value))
  @IsInt()
  @Min(1)
  @Max(100)
  pageSize?: number = 10;

  // 搜索参数
  @IsOptional()
  @IsString()
  workOrderNo?: string;  // 工单号

  @IsOptional()
  @IsString()
  eventNo?: string;  // 事件号

  @IsOptional()
  @IsString()
  customerName?: string;  // 客户名称

  @IsOptional()
  @IsString()
  @IsIn(["0", "1", "2", "3", "4", "5", "6", "7", "8"])
  status?: string;  // 工单状态

  // 时间范围参数
  @IsOptional()
  @IsDateString()
  scheduledAtStart?: string;

  @IsOptional()
  @IsDateString()
  scheduledAtEnd?: string;
}
步骤 2：实现 Service 方法
// src/work-orders/work-orders.service.ts
import { Injectable, BadRequestException, Logger } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { QueryWorkOrderDto } from './dto/query-work-order.dto';

@Injectable()
export class WorkOrdersService {
  private readonly logger = new Logger(WorkOrdersService.name);

  constructor(private readonly prisma: PrismaService) {}

  async findAll(queryDto: QueryWorkOrderDto) {
    const {
      page = 1,
      pageSize = 10,
      workOrderNo,
      eventNo,
      customerName,
      status,
      scheduledAtStart,
      scheduledAtEnd
    } = queryDto;

    try {
      // ===== 第一步：构建动态 WHERE 条件 =====
      // 这是原生 SQL 的核心优势：灵活的动态条件拼接
      let whereConditions: string[] = ["wo.delete_flag = '0'"];
      const params: any[] = [];

      // 工单号模糊查询
      if (workOrderNo) {
        whereConditions.push("wo.order_no LIKE ?");
        params.push(`%${workOrderNo}%`);
      }

      // 事件号模糊查询
      if (eventNo) {
        whereConditions.push("wo.event_no LIKE ?");
        params.push(`%${eventNo}%`);
      }

      // 工单状态精确查询
      if (status) {
        whereConditions.push("wo.order_status = ?");
        params.push(status);
      }

      // 客户名称模糊查询（跨表）
      if (customerName) {
        whereConditions.push("c.name LIKE ?");
        params.push(`%${customerName}%`);
      }

      // 创建时间范围查询
      if (scheduledAtStart || scheduledAtEnd) {
        if (scheduledAtStart && scheduledAtEnd) {
          whereConditions.push("wo.create_time BETWEEN ? AND ?");
          params.push(new Date(scheduledAtStart), new Date(scheduledAtEnd));
        } else if (scheduledAtStart) {
          whereConditions.push("wo.create_time >= ?");
          params.push(new Date(scheduledAtStart));
        } else if (scheduledAtEnd) {
          whereConditions.push("wo.create_time <= ?");
          params.push(new Date(scheduledAtEnd));
        }
      }

      // 将所有条件用 AND 连接
      const whereSQL = whereConditions.length > 0 
        ? "WHERE " + whereConditions.join(" AND ") 
        : "";

      // ===== 第二步：构建 COUNT SQL（获取总数）=====
      const countSQL = `
        SELECT COUNT(*) as count
        FROM work_order wo
        LEFT JOIN customers c ON wo.customer_id = c.id
        ${whereSQL}
      `;

      // 打印日志便于调试
      this.logger.debug(`[COUNT SQL] ${countSQL}`);
      this.logger.debug(`[COUNT Params] ${JSON.stringify(params)}`);

      // 执行 COUNT 查询
      const countStartTime = Date.now();
      const countResult = await this.prisma.$queryRawUnsafe(
        countSQL,
        ...params
      ) as any[];
      const countDuration = Date.now() - countStartTime;

      // 处理 BigInt 类型转换
      let total = 0;
      if (countResult[0]) {
        const countValue = countResult[0].count;
        total = typeof countValue === 'bigint' ? Number(countValue) : countValue;
      }
      this.logger.debug(`[COUNT Result] count=${total}, duration=${countDuration}ms`);

      // ===== 第三步：构建 LIST SQL（获取列表）=====
      const offset = (page - 1) * pageSize;
      const listSQL = `
        SELECT
          wo.id,
          wo.order_no,
          wo.event_no,
          wo.order_status,
          wo.is_remote_guide,
          CAST(wo.current_handler_id AS UNSIGNED) as current_handler_id,
          CAST(wo.inspection_person_id AS UNSIGNED) as inspection_person_id,
          CAST(wo.repair_person_id AS UNSIGNED) as repair_person_id,
          CAST(wo.customer_id AS UNSIGNED) as customer_id,
          c.name as customer_name,
          CAST(wo.create_by AS UNSIGNED) as create_by,
          wo.create_time,
          u_handler.name as current_handler_name,
          u_inspection.name as inspection_person_name,
          u_repair.name as repair_person_name,
          u_creator.name as create_person_name
        FROM work_order wo
        LEFT JOIN customers c ON wo.customer_id = c.id
        LEFT JOIN users u_handler ON wo.current_handler_id = u_handler.id
        LEFT JOIN users u_inspection ON wo.inspection_person_id = u_inspection.id
        LEFT JOIN users u_repair ON wo.repair_person_id = u_repair.id
        LEFT JOIN users u_creator ON wo.create_by = u_creator.id
        ${whereSQL}
        ORDER BY wo.create_time DESC
        LIMIT ? OFFSET ?
      `;

      // 复制参数数组（因为会被修改）
      const listParams = [...params, pageSize, offset];

      // 打印日志
      this.logger.debug(`[LIST SQL] ${listSQL}`);
      this.logger.debug(`[LIST Params] pageSize=${pageSize}, offset=${offset}, filters=${JSON.stringify(params)}`);

      // 执行 LIST 查询
      const listStartTime = Date.now();
      const rows = await this.prisma.$queryRawUnsafe(
        listSQL,
        ...listParams
      ) as any[];
      const listDuration = Date.now() - listStartTime;
      this.logger.debug(`[LIST Result] rows=${rows.length}, duration=${listDuration}ms`);

      // ===== 第四步：格式化响应数据 =====
      const formattedRows = rows.map(row => ({
        id: row.id,
        eventNo: row.event_no,
        orderNo: row.order_no,
        orderStatus: row.order_status,
        orderStatusName: this.getStatusName(row.order_status),
        isRemoteGuide: row.is_remote_guide,
        inspectionPersonId: row.inspection_person_id,
        inspectionPersonName: row.inspection_person_name || '-',
        repairPersonId: row.repair_person_id,
        repairPersonName: row.repair_person_name || '-',
        currentHandlerId: row.current_handler_id,
        currentHandlerName: row.current_handler_name || '-',
        customerId: row.customer_id,
        customerName: row.customer_name || '-',
        createBy: row.create_by,
        createByName: row.create_person_name || '-',
        createTime: row.create_time,
      }));

      // ===== 第五步：返回分页结果 =====
      return {
        rows: formattedRows,
        total,
        page,
        pageSize,
        totalPages: Math.ceil(total / pageSize),
      };
    } catch (error) {
      // 详细的错误日志
      this.logger.error(`[查询工单列表] 异常发生`);
      this.logger.error(`[错误信息] ${error.message}`);
      this.logger.error(`[错误堆栈] ${error.stack}`);

      throw new BadRequestException(`查询工单列表失败: ${error.message}`);
    }
  }

  private getStatusName(status: string): string {
    const statusMap: { [key: string]: string } = {
      '1': '待初筛',
      '2': '初筛中',
      '3': '待维修',
      '4': '维修中',
      '5': '待确认',
      '6': '确认中',
      '7': '待验收',
      '8': '已完成',
      '0': '已关闭',
    };
    return statusMap[status] || '未知状态';
  }
}

最佳实践

✅ DO（应该做）

1. 始终使用参数绑定防止 SQL 注入
// ✅ 正确：使用 ? 占位符
const sql = "SELECT * FROM users WHERE name = ?";
const result = await prisma.$queryRawUnsafe(sql, userInput);

// ❌ 错误：字符串拼接（容易被注入）
const sql = `SELECT * FROM users WHERE name = '${userInput}'`;
const result = await prisma.$queryRawUnsafe(sql);
2. 给字段设置别名便于映射
// ✅ 正确：使用 as 别名便于 JavaScript 处理
SELECT 
  wo.order_no,
  wo.customer_id,
  c.name as customer_name,  // 别名
  u.name as operator_name   // 别名

// 在 JavaScript 中直接使用
const { orderNo, customerName, operatorName } = row;
3. 分离 COUNT 和 LIST 查询
// ✅ 正确：两个分离的查询
const total = await prisma.$queryRawUnsafe(countSQL, ...params);
const rows = await prisma.$queryRawUnsafe(listSQL, ...listParams);

// 这样做的原因：
// 1. 获取总数不需要 LIMIT，更高效
// 2. 分页逻辑清晰
// 3. 分别优化两个查询的性能
4. 处理 BigInt 类型转换
// ✅ 正确：检测并转换 BigInt
const countValue = countResult[0].count;
const total = typeof countValue === 'bigint' ? Number(countValue) : countValue;

// ❌ 错误：直接使用可能导致类型混合
const total = countResult[0].count;  // 可能是 BigInt，后续运算会出错
5. 添加详细的日志
// ✅ 正确：记录 SQL、参数和执行时间
this.logger.debug(`[SQL] ${sql}`);
this.logger.debug(`[Params] ${JSON.stringify(params)}`);
const startTime = Date.now();
const result = await prisma.$queryRawUnsafe(sql, ...params);
const duration = Date.now() - startTime;
this.logger.debug(`[Duration] ${duration}ms`);
6. 在 WHERE 条件中使用 LEFT JOIN
// ✅ 正确：LEFT JOIN 不过滤主表记录
SELECT * FROM work_order wo
LEFT JOIN customers c ON wo.customer_id = c.id
WHERE c.name LIKE '%keyword%'  // 可以过滤关联表字段

// ❌ 错误：INNER JOIN 会过滤掉没有关联的记录
SELECT * FROM work_order wo
INNER JOIN customers c ON wo.customer_id = c.id

❌ DON’T（不应该做）

1. 不要直接拼接用户输入
// ❌ 危险：SQL 注入漏洞
const userInput = "'; DROP TABLE users; --";
const sql = `SELECT * FROM users WHERE name = '${userInput}'`;
2. 不要在 JavaScript 中进行大量数据处理
// ❌ 低效：返回所有数据再过滤
const allRows = await prisma.$queryRawUnsafe("SELECT * FROM work_order");
const filtered = allRows.filter(row => row.status === '1');

// ✅ 高效：在 SQL 中过滤
const rows = await prisma.$queryRawUnsafe(
  "SELECT * FROM work_order WHERE order_status = ?",
  '1'
);
3. 不要使用 SELECT * 获取大量不需要的字段
// ❌ 低效：获取所有字段再丢弃
SELECT * FROM work_order wo
LEFT JOIN customers c ...
LEFT JOIN users u ...

// ✅ 高效：只获取需要的字段
SELECT wo.id, wo.order_no, c.name, u.name FROM work_order wo ...
4. 不要在 COUNT 查询中使用 LIMIT
// ❌ 错误：LIMIT 会影响 COUNT 结果
SELECT COUNT(*) as count FROM work_order LIMIT 10

// ✅ 正确：COUNT 获取总数，LIST 使用 LIMIT
SELECT COUNT(*) as count FROM work_order  -- COUNT 查询
SELECT * FROM work_order LIMIT 10 OFFSET 0  -- LIST 查询

性能优化

📊 性能对比

数据量: 100,000 条工单记录

方案 1: Prisma include/select
├─ 初始查询: 50ms
├─ N+1 查询 (查询 100 条): 1000ms
└─ 总耗时: ~1000ms

方案 2: 原生 SQL (当前实现)
├─ COUNT 查询: 5ms
├─ LIST 查询: 10ms
└─ 总耗时: ~15ms  ← 快 60+ 倍！

🚀 优化建议

1. 为频繁查询的字段添加索引
-- 添加单列索引
CREATE INDEX idx_work_order_order_no ON work_order(order_no);
CREATE INDEX idx_work_order_event_no ON work_order(event_no);
CREATE INDEX idx_work_order_customer_id ON work_order(customer_id);
CREATE INDEX idx_work_order_create_time ON work_order(create_time);
CREATE INDEX idx_customer_name ON customers(name);

-- 添加复合索引（用于多条件查询）
CREATE INDEX idx_wo_status_create_time ON work_order(order_status, create_time);
2. 使用数据库连接池
// .env.development
DATABASE_URL="mysql://user:password@localhost:3306/db?connection_limit=20"
3. 启用查询缓存（可选）
// 对于不频繁变化的数据，可以使用 Redis 缓存
const cacheKey = `workorders:${JSON.stringify(filters)}`;
let result = await redis.get(cacheKey);
if (!result) {
  result = await prisma.$queryRawUnsafe(sql, ...params);
  await redis.setex(cacheKey, 300, JSON.stringify(result));  // 缓存 5 分钟
}
4. 避免 N+1 查询
// ❌ 错误：N+1 查询
const workOrders = await prisma.workOrder.findMany();
for (const wo of workOrders) {
  const customer = await prisma.customer.findUnique({
    where: { id: wo.customerId }
  });  // 会循环执行 N 次查询
}

// ✅ 正确：单个 SQL 查询
const workOrders = await prisma.$queryRawUnsafe(`
  SELECT wo.*, c.name
  FROM work_order wo
  LEFT JOIN customers c ON wo.customer_id = c.id
`);  // 只执行 1 次查询

常见问题

Q1: 如何处理 NULL 值？

// ❌ 问题：NULL 值映射后为 null
const row = { inspectionPersonId: null };

// ✅ 解决：使用默认值或条件映射
const formattedRow = {
  inspectionPersonName: row.inspection_person_name || '-',  // 默认值
  inspectionPersonId: row.inspection_person_id ?? 0,  // 空值合并
};

Q2: 如何处理日期格式？

// Prisma 会自动将 MySQL DATETIME 转换为 JavaScript Date
const row = { create_time: 2025-11-12T10:30:00Z };

// 格式化日期
import dayjs from 'dayjs';
const formattedDate = dayjs(row.create_time).format('YYYY-MM-DD HH:mm:ss');

Q3: 如何处理 BigInt 类型？

// 问题：MySQL BigInt 在 JavaScript 中可能丢失精度
const sql = "SELECT id FROM users";  // id 是 BigInt
const result = await prisma.$queryRawUnsafe(sql);
// result[0].id 可能是 BigInt 或 number，需要手动处理

// 解决方案 1：在 SQL 中转换
SELECT CAST(id AS UNSIGNED) as id FROM users

// 解决方案 2：在 JavaScript 中检测并转换
const id = typeof result[0].id === 'bigint' ? Number(result[0].id) : result[0].id;

Q4: 如何调试 SQL 查询？

// 打印完整的 SQL 和参数
this.logger.debug(`SQL: ${sql}`);
this.logger.debug(`Params: ${JSON.stringify(params)}`);

// 或者复制 SQL 到数据库工具直接执行
// 1. 复制 SQL 语句
// 2. 将 ? 替换为实际参数值
// 3. 在 MySQL Workbench 或 DBeaver 中执行

Q5: 如何处理事务？

// 使用 Prisma 事务
await prisma.$transaction(async (tx) => {
  // 事务内的所有操作要么全部成功，要么全部回滚
  await tx.$queryRawUnsafe("UPDATE work_order SET status = ? WHERE id = ?", '2', 1);
  await tx.$queryRawUnsafe("INSERT INTO order_status_record ...");
});

总结

特性 Prisma ORM 原生 SQL
简单单表 ⭐⭐⭐⭐⭐ ⭐⭐⭐
多表 JOIN ⭐⭐⭐ ⭐⭐⭐⭐⭐
动态条件 ⭐⭐ ⭐⭐⭐⭐⭐
性能 ⭐⭐⭐ ⭐⭐⭐⭐⭐
安全性 ⭐⭐⭐⭐⭐ ⭐⭐⭐ (需小心)
可读性 ⭐⭐⭐⭐⭐ ⭐⭐⭐
学习成本 低 中等

建议：

  • ✅ 简单查询用 Prisma ORM
  • ✅ 复杂查询用原生 SQL + $queryRawUnsafe
  • ✅ 始终使用参数绑定防止 SQL 注入
  • ✅ 添加详细日志便于调试和性能分析

欢迎关注公众号FishTech Notes，收藏点赞关注哦

# sql
Logo
火山引擎 ADG 社区

火山引擎开发者社区是火山引擎打造的AI技术生态平台，聚焦Agent与大模型开发，提供豆包系列模型（图像/视频/视觉）、智能分析与会话工具，并配套评测集、动手实验室及行业案例库。社区通过技术沙龙、挑战赛等活动促进开发者成长，新用户可领50万Tokens权益，助力构建智能应用。

更多推荐

Unicorn Binance Websocket API 使用教程

Unicorn Binance Websocket API 是一个开源项目，由 LUCIT Systems and Development 开发。该项目旨在提供一个简单易用的 Python 库，用于通过 Binance 平台的 WebSocket API 接收实时数据。它支持流式数据订阅，包括市场深度、价格变动、交易历史等，并允许用户轻松处理这些数据。## 2. 项目快速启动### 环境准

avatar 火山引擎 ADG 社区

开源项目使用教程：Driving-with-LLMs

`Driving-with-LLMs` 项目是一个用于自动驾驶的可解释性研究项目，其目录结构如下：- `assets/`：存储项目的相关资源文件。- `data/`：包含训练和测试数据集。- `models/`：存放训练好的模型权重文件。- `results/`：保存模型运行后的结果文件。- `scripts/`：包含项目运行所需的脚本文件。- `utils/`：包含一些工具函数和类

avatar 火山引擎 ADG 社区

Chatbot UI终极指南：如何快速构建个性化AI聊天界面

Chatbot UI是一个功能强大的开源AI模型聊天界面，能够轻松与多种AI服务提供商集成，包括OpenAI、Anthropic、Google、Azure等主流平台。这个项目为开发者和用户提供了一个直观、美观的聊天机器人界面，让您能够快速创建个性化的AI对话体验。无论您是想要构建企业级聊天机器人，还是希望拥有个人AI助手，Chatbot UI都能为您提供完美的解决方案。## 🚀 快速开始：5

avatar 火山引擎 ADG 社区