javascript - 如何在mysql语句中插入动态参数？

Question

我使用 mysql 作为数据库，这是查询

select 
  job_id, 
  area_set, 
  area_subset, 
  worker_type, 
  job_posting_name, 
  job_location, 
  start_date, 
  end_date, 
  description, 
  is_template, 
  template_name, 
  is_draft, 
  no_of_post, 
  is_open, 
  pay_rate, 
  is_completed, 
  create_date, 
  contractor_company_name, 
  jobs.user_id 
from 
  jobs, contractor_company 
where job_id not in (select job_id from job_response where job_response.user_id = 864) 
  and is_template in (0,1) 
  and is_draft in (0,0) 
  and is_open in (1,1) 
  and is_completed in (0,0) 
  and contractor_company.contractor_company_id = (select contractor_company_id from  contractor_survey_question 
    where contractor_survey_question.user_id = jobs.user_id) 
    and jobs.user_id IN ( select user.user_id from user where user.phone_country_code =(select user.phone_country_code from user 
      where user.user_id=864 )) and area_set = 10 and area_subset in(78,79)
order by create_date desc; 

这里 area_subset 可能有不同数量的参数。

我从 js 适配器调用这个语句，比如

select 
  job_id, 
  worker_type, 
  job_posting_name, 
  job_location, 
  start_date, 
  end_date, 
  description, 
  is_template, 
  template_name, 
  is_draft, 
  no_of_post, 
  is_open,
  pay_rate, 
  is_completed, 
  create_date,
  contractor_company_name,
  jobs.user_id 
from
  jobs, contractor_company 
where job_id not in (select job_id from  job_response where job_response.user_id = ?) 
  and is_template in (?,?) 
  and is_draft in (?,?) 
  and is_open in (?,?) 
  and is_completed in (?,?) 
  and contractor_company.contractor_company_id = (select  contractor_company_id from  contractor_survey_question 
    where contractor_survey_question.user_id = jobs.user_id) 
    and jobs.user_id IN ( select user.user_id from user 
      where user.phone_country_code =(select user.phone_country_code from user 
        where user.user_id=? ))  and area_set = ? and area_subset in(?,?) 
order by create_date desc; 

例如 area_subset 中可能有超过 2 个参数，那么我该如何传递给这个查询。

请帮助

标签： javascriptmysql

Answer 1

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by ActivityLogKey asc";
elseif @input = "desc" then
    SET @sort = " order by ActivityLogKey desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END