NestJS中Prisma原生查询转换报错（原Express+Knex查询正常）
2026-7-19
解决NestJS+Prisma $queryRaw执行SQL时的P2010语法错误问题
问题描述
将Express+Knex环境下正常运行的SQL查询迁移到NestJS中，使用Prisma的$queryRaw执行时，抛出PrismaClientKnownRequestError错误（错误码P2010，MySQL错误码1064），提示SQL语法错误，位置靠近'?'字符。需要获取与原Express应用相同的活动详情数据。
原代码
async fetchCompanyActivities( body: GetCompanyDetailDto, member_id: number, facility_id: number, ) { const { latitude, longitude, start_date, end_date, keyword, activity_type_ids, zone_ids, } = body; // const numberOfDaysToAdd = process.env.company_activity_no_of_days; const numberOfDaysToAdd = 15; const unit = 6371; const currentDate = new Date(); const curTime = currentDate.getHours() + ':' + currentDate.getMinutes() + ':' + currentDate.getSeconds(); const memberQuery1 = member_id ? ` AND m0_.member_id = '${member_id}'` : ''; const memberQuery2 = member_id ? ` LEFT JOIN (SELECT schedule_detail_id ,IF(member_id = '${member_id}' , 1,0) self_booked FROM member_schedule_activity WHERE status IN ('${CompanyInfoType.STATUS_BOOKED}','${CompanyInfoType.STATUS_RESERVED}') OR (STATUS = '${CompanyInfoType.STATUS_EXPIRED}' AND checkin = 1) GROUP BY schedule_detail_id,member_id) m4_ ON m4_.schedule_detail_id = s3_.id ` : ''; const memberQuery3 = member_id ? ` OR IFNULL(m4_.self_booked,1)=1` : ''; const memberQuery4 = member_id ? ` AND member_id = '${member_id}'` : ''; const distanceQuery = latitude && longitude ? `,(\n ${unit} *\n acos(cos(radians(${latitude})) *\n cos(radians(IF( a2_.is_location = 0 , f1_.latitude, a2_.latitude))) *\n cos(radians(IF( a2_.is_location = 0 , f1_.longitude, a2_.longitude)) -\n radians(${longitude})) +\n sin(radians(${latitude})) *\n sin(radians(IF( a2_.is_location = 0 , f1_.latitude, a2_.latitude))))\n ) AS distance` : ''; const distanceOrderByQuery = latitude && longitude ? `ORDER BY distance DESC` : ''; const dateFilterQuery = start_date && end_date ? `AND s3_.schedule_date >= '${start_date} ${curTime}' AND s3_.schedule_date <= '${end_date}'` : `AND DATE_ADD(s3_.schedule_date, INTERVAL s3_.duration MINUTE ) > \n (SELECT _sd.schedule_date FROM schedule_detail _sd\n INNER JOIN activity_schedule _as ON _as.id=_sd.schedule_id\n WHERE _as.facility_id = '${facility_id}' AND _sd.schedule_date >= NOW() ORDER BY _sd.schedule_date ASC LIMIT 1) \n AND s3_.schedule_date <= DATE_ADD(DATE_FORMAT((SELECT _sd.schedule_date FROM schedule_detail _sd\n INNER JOIN activity_schedule _as ON _as.id=_sd.schedule_id\n WHERE _as.facility_id = '${facility_id}' AND _sd.schedule_date >= NOW() ORDER BY _sd.schedule_date ASC LIMIT 1),'%Y-%m-%d'), INTERVAL ${numberOfDaysToAdd} DAY)`; const keywordFilterQuery = keyword ? `AND (f1_.company_name LIKE '%${keyword}%' OR f1_.address LIKE '%${keyword}%' OR a2_.pinaddress LIKE '%${keyword}%' OR at_.name LIKE '%${keyword}%')` : ''; const activityTypeFilterQuery = activity_type_ids && activity_type_ids.length ? `AND at_.id IN (${activity_type_ids.join(',')})` : ''; const zoneFilterQuery = zone_ids && zone_ids.length ? `AND a2_.facilityZone_id IN (${zone_ids.join(',')})` : ''; const query = `SELECT DISTINCT\n s3_.id AS schedule_detail_id,\n fz.title zone,\n IFNULL(m3_.booked_slots,0) booked_slots,\n s3_.slots AS slots,\n s3_.schedule_id AS classId,\n a4_.id AS activityId,\n a4_.NAME AS name,\n at_.NAME AS actt_name,\n a4_.CODE AS code,\n a4_.description AS description,\n a4_.imageName AS activityImage,\n at_.imageName AS activityTypeImage,\n a2_.is_free AS isFree,\n a2_.offer_online AS offer_online,\n a2_.allow_corepass AS isCorePass,\n a2_.recurrence AS recurrence,\n s3_.duration AS duration,\n DATE_FORMAT(s3_.schedule_date,'%h:%i %p') AS startTime,\n DATE_FORMAT(DATE_ADD(s3_.schedule_date, INTERVAL s3_.duration MINUTE ), '%h:%i %p') endTime,\n DATE_ADD(s3_.schedule_date, INTERVAL s3_.duration MINUTE ) class_end_date_time,\n instrutor_.title AS instructor_name,\n a2_.duration AS slot_duration,\n a2_.facility_id AS facilityId,\n f1_.company_name AS facility,\n IF( a2_.is_location = 0 , f1_.latitude, a2_.latitude) AS classLatitude,\n IF( a2_.is_location = 0 , f1_.longitude, a2_.longitude) AS classLongitude,\n IF( a2_.is_location = 0 , f1_.latitude, a2_.latitude) AS facilityLatitude,\n IF( a2_.is_location = 0 , f1_.longitude, a2_.longitude) AS facilityLongitude,\n IF( a2_.is_location = 1 AND LENGTH (a2_.pinaddress ) > 0 , a2_.pinaddress, f1_.address) AS address,\n IF( a2_.is_location = 1 AND LENGTH (a2_.pinaddress ) > 0 , a2_.pinaddress, f1_.address) AS activity_schedule_address,\n f1_.company_logo AS facilityImage,\n a2_.startDate AS a_startDate,\n DATE_FORMAT(s3_.schedule_date,'%a,%M %D %Y') AS startDate,\n a2_.endDate AS endDate,\n a2_.updated_date AS lastUpdated,\n s3_.schedule_date AS classDate,\n m0_.is_favourite AS is_favourite_0,\n m0_.checkin AS checkin_1,\n m0_.STATUS AS status,\n m0_.id AS msa_id,\n IF( a2_.is_location = 0 , f1_.latitude, a2_.latitude) AS latitude,\n IF( a2_.is_location = 0 , f1_.longitude, a2_.longitude) AS longitude,\n a2_.id AS id_5,\n a2_.is_recommended AS is_recommended ${distanceQuery}\n FROM\n activity_schedule a2_\n INNER JOIN schedule_detail s3_ ON a2_.id = s3_.schedule_id\n INNER JOIN activity a4_ ON a2_.activity_id = a4_.id\n INNER JOIN facility_zones fz ON fz.id = a2_.facilityZone_id\n INNER JOIN activity_type at_ ON a4_.activity_type_id = at_.id \n INNER JOIN activity_schedule_package a6_ ON\n a2_.id = a6_.activityschedule_id\n INNER JOIN package p5_ ON\n p5_.id = a6_.package_id\n LEFT JOIN instructor instrutor_ ON\n s3_.instructor_id = instrutor_.id AND(instrutor_.is_deleted = 0)\n INNER JOIN fos_user_user f1_ ON a2_.facility_id = f1_.id\n LEFT JOIN member_schedule_activity m0_ ON m0_.schedule_detail_id = s3_.id AND m0_.STATUS != '${CompanyInfoType.STATUS_CANCELLED}' ${memberQuery1}\n LEFT JOIN (\n SELECT\n COUNT(id) booked_slots, schedule_detail_id\n FROM\n member_schedule_activity \n WHERE\n STATUS IN ('${CompanyInfoType.STATUS_BOOKED}','${CompanyInfoType.STATUS_RESERVED}')\n OR (STATUS = '${CompanyInfoType.STATUS_EXPIRED}' AND checkin = 1)\n group by schedule_detail_id\n ) m3_ ON m3_.schedule_detail_id = s3_.id \n \n ${memberQuery2}\n \n WHERE\n a2_.is_deleted = 0 \n ${dateFilterQuery} \n ${keywordFilterQuery}\n ${activityTypeFilterQuery}\n ${zoneFilterQuery}\n AND ( f1_.enabled = 1 )\n AND a2_.facility_id IN (${facility_id})\n AND s3_.is_deleted = 0 \n AND s3_.is_cancel = 0 \n AND (a2_.is_free = 1 OR (p5_.is_deleted = 0 AND ( p5_.expires_on > curdate() OR p5_.repeat_monthly = 1 ) ))\n GROUP BY s3_.id \n ORDER BY\n s3_.schedule_date\n LIMIT 100`; const activities = await this.prismaService.$queryRaw(Prisma.sql`${query}`); return activities; }
错误信息
err: { "type": "PrismaClientKnownRequestError", "message": "\nInvalid`prisma.$queryRaw()`invocation:\n\n\nRaw query failed. Code:`1064`. Message:`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?\n FROM\n activit' at line 45`", "stack": "Error: Invalid `prisma.$queryRaw()` invocation:\n\n Raw query failed. Code: `1064`. Message: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?\n FROM\n activit' at line 45`\n at Pn.handleRequestError (.../node_modules/@prisma/client/runtime/library.js:171:6929)\n at Pn.handleAndLogRequestError(.../node_modules/@prisma/client/runtime/library.js:171:6358)\n at Pn.request (.../node_modules/@prisma/client/runtime/library.js:171:6237)\n at UserRepository.fetchCompanyActivities (.../dist/apps/user/main.js:9706:28)\n at UserService.getCompanyDetail (.../dist/apps/user/main.js:10521:19)", "code": "P2010", "clientVersion": "4.14.1", "meta": { "type": "Object", "message": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?\n FROM\n activit' at line 45", "code": "1064" } }
问题原因
- 原代码直接拼接字符串生成SQL，而Prisma的
Prisma.sql模板会自动对字符串中的特殊字符转义，或尝试将变量解析为参数占位符（?），导致生成的SQL语法不符合MySQL要求。 - 直接拼接SQL存在严重的SQL注入风险，这也是Prisma不推荐该写法的核心原因。
解决方案
改用Prisma的参数化查询方式，所有动态变量通过Prisma.value()传递，IN子句用Prisma.join()处理数组，避免直接字符串拼接。
重构后代码
async fetchCompanyActivities( body: GetCompanyDetailDto, member_id: number, facility_id: number, ) { const { latitude, longitude, start_date, end_date, keyword, activity_type_ids, zone_ids, } = body; const numberOfDaysToAdd = 15; const unit = 6371; const currentDate = new Date(); const curTime = `${currentDate.getHours()}:${currentDate.getMinutes()}:${currentDate.getSeconds()}`; // 构建动态查询片段，使用参数化 const memberConditions = []; let memberJoinFragment = Prisma.sql``; if (member_id) { memberConditions.push(Prisma.sql`AND m0_.member_id = ${Prisma.value(member_id)}`); memberJoinFragment = Prisma.sql` LEFT JOIN ( SELECT schedule_detail_id, IF(member_id = ${Prisma.value(member_id)}, 1, 0) self_booked FROM member_schedule_activity WHERE status IN (${Prisma.value(CompanyInfoType.STATUS_BOOKED)}, ${Prisma.value(CompanyInfoType.STATUS_RESERVED)}) OR (STATUS = ${Prisma.value(CompanyInfoType.STATUS_EXPIRED)} AND checkin = 1) GROUP BY schedule_detail_id, member_id ) m4_ ON m4_.schedule_detail_id = s3_.id `; } const distanceFragment = latitude && longitude ? Prisma.sql` ,( ${Prisma.value(unit)} * acos(cos(radians(${Prisma.value(latitude)})) * cos(radians(IF(a2_.is_location = 0, f1_.latitude, a2_.latitude))) * cos(radians(IF(a2_.is_location = 0, f1_.longitude, a2_.longitude)) - radians(${Prisma.value(longitude)})) + sin(radians(${Prisma.value(latitude)})) * sin(radians(IF(a2_.is_location = 0, f1_.latitude, a2_.latitude))) ) AS distance` : Prisma.sql``; let dateFilterFragment = Prisma.sql``; if (start_date && end_date) { const startDateTime = `${start_date} ${curTime}`; dateFilterFragment = Prisma.sql` AND s3_.schedule_date >= ${Prisma.value(startDateTime)} AND s3_.schedule_date <= ${Prisma.value(end_date)}`; } else { dateFilterFragment = Prisma.sql` AND DATE_ADD(s3_.schedule_date, INTERVAL s3_.duration MINUTE) > ( SELECT _sd.schedule_date FROM schedule_detail _sd INNER JOIN activity_schedule _as ON _as.id = _sd.schedule_id WHERE _as.facility_id = ${Prisma.value(facility_id)} AND _sd.schedule_date >= NOW() ORDER BY _sd.schedule_date ASC LIMIT 1 ) AND s3_.schedule_date <= DATE_ADD( DATE_FORMAT( (SELECT _sd.schedule_date FROM schedule_detail _sd INNER JOIN activity_schedule _as ON _as.id = _sd.schedule_id WHERE _as.facility_id = ${Prisma.value(facility_id)} AND _sd.schedule_date >= NOW() ORDER BY _sd.schedule_date ASC LIMIT 1), '%Y-%m-%d' ), INTERVAL ${Prisma.value(numberOfDaysToAdd)} DAY )`; } const keywordFilterFragment = keyword ? Prisma.sql` AND ( f1_.company_name LIKE ${Prisma.value(`%${keyword}%`)} OR f1_.address LIKE ${Prisma.value(`%${keyword}%`)} OR a2_.pinaddress LIKE ${Prisma.value(`%${keyword}%`)} OR at_.name LIKE ${Prisma.value(`%${keyword}%`)} )` : Prisma.sql``; const activityTypeFilterFragment = activity_type_ids?.length ? Prisma.sql`AND at_.id IN (${Prisma.join(activity_type_ids.map(id => Prisma.value(id)))})` : Prisma.sql``; const zoneFilterFragment = zone_ids?.length ? Prisma.sql`AND a2_.facilityZone_id IN (${Prisma.join(zone_ids.map(id => Prisma.value(id)))})` : Prisma.sql``; // 最终拼接完整SQL const query = Prisma.sql` SELECT DISTINCT s3_.id AS schedule_detail_id, fz.title zone, IFNULL(m3_.booked_slots, 0) booked_slots, s3_.slots AS slots, s3_.schedule_id AS classId, a4_.
相关产品推荐
相关产品推荐
