Node.js查询MySQL出现ER_PARSE_ERROR语法错误的解决方法

错误根源 #

问题出在参数数组与SQL占位符数量不匹配：当searchString为空时，SQL语句中没有WHERE子句对应的?占位符，但你仍然把空字符串的searchParam加入了参数数组，导致数据库执行时解析出错——多余的空字符串被错误识别为无效语法，触发ER_PARSE_ERROR。

解决方案 #

动态构建参数列表，确保占位符数量和参数数量完全对应：

• 初始化空的参数数组，仅当存在有效搜索条件时，才将搜索参数加入数组
• 分页参数始终添加到参数数组末尾

修改后的完整代码 #

export async function fetchAllPatients({
  searchString = "",
  pageNumber = 1,
  pageSize = 20,
  sortBy = "desc"
}: {
  searchString?: string;
  pageNumber?: number;
  pageSize?: number;
  sortBy?: SortOrder;
}): Promise<{
  patients: any[];
  isNext: boolean;
}> {
  let db: Connection | null = null;

  try {
    db = connectToDatabase();

    return new Promise((resolve, reject) => {
      const skipAmount = (pageNumber - 1) * pageSize;
      let sql = 'SELECT id, firstName, lastName, sex, age, residence, enrollmentDate FROM patients';
      const params: any[] = [];

      // 处理搜索条件
      if (searchString.trim() !== '') {
        sql += ` WHERE CONCAT(firstName, ' ', lastName) LIKE ?`;
        params.push(`%${searchString.trim()}%`);
      }

      // 添加排序与分页
      sql += ` ORDER BY enrollmentDate ${sortBy.toUpperCase()} LIMIT ?, ?`;
      params.push(skipAmount, pageSize);

      // 执行查询
      db?.query(sql, params, (error, results) => {
        if (error) {
          console.error(`Error fetching patients: ${error.message}`);
          reject(`Failed to fetch patients: ${error.message}`);
        } else {
          const patients = results.map((patient: any) => ({
            id: patient.id,
            fullName: `${patient.firstName} ${patient.lastName}`,
            sex: patient.sex,
            age: patient.age,
            residence: patient.residence,
            enrollmentDate: formatDate(patient.enrollmentDate),
          }));
          // 解析是否存在下一页
          resolve({
            patients,
            isNext: results.length === pageSize
          });
        }
      });
    });
  } catch (error) {
    console.error(`Database connection error: ${(error as Error).message}`);
    throw new Error(`Failed to connect to database: ${(error as Error).message}`);
  } finally {
    // 关闭数据库连接，避免资源泄漏
    if (db) {
      db.end();
    }
  }
}

额外注意事项 #

• 确保formatDate函数已正确实现，避免后续日期格式化出错
• 外层try/catch处理数据库连接失败的场景，finally块中关闭连接防止资源泄漏
• 判断isNext的逻辑可根据需求优化，比如额外查询总记录数来精确计算分页状态

内容的提问来源于stack exchange，提问作者Baker