mysql 过程 - 子句中的 where 条件中的解析参数问题

Question

下面是程序

CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(age) and start_hours > in_hours 
    AND in_hours <= end_hours;
END

这个过程中的问题是我如何传入age varchar(100),参数in,in子句目前我正在使用查询进行解析

CALL `usitterz`.`sitter_price`(4.10,'1,2,3,4', 3, 5);

但这是错误的，因为在查询中读这个像 in('1,2,3,4') 但我想要它像 - in(1,2,3,4) 。

就像

CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(1,2,3,4) and start_hours > in_hours 
    AND in_hours <= end_hours;
END

Answer 1

第 1 步:搞乱以获取 EXECUTE 的正确字符串

DROP PROCEDURE IF EXISTS sitter_price;
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
    SET @theSql=CONCAT('SELECT TRUNCATE(sum(price)*',in_hours,'*',no_of_days,'*',no_of_sitters,',2)');
    SET @theSql=CONCAT(@theSql,' as total_amount from job_prices jp join kids_ages ka on ka.id = jp.kids_age_id');
    SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and start_hours > ',in_hours,' AND ');
    SET @theSql=CONCAT(@theSql,in_hours,'<= end_hours');
    /*
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(1,2,3,4) and start_hours > in_hours 
    AND in_hours <= end_hours;
    */
    select @theSql;
END$
DELIMITER ;

Step2:传入参数，查看字符串是什么样的

call sitter_price(89,'1,2,4,8',11,12);

SELECT TRUNCATE(sum(price)*89*12*11,2) as total_amount 
from job_prices jp 
join kids_ages ka on ka.id = jp.kids_age_id 
where ka.age in(1,2,4,8) and start_hours > 89 
AND 89<= end_hours

第 3 步:使用 PREPARE 和 EXECUTE 完成存储过程。

DROP PROCEDURE IF EXISTS sitter_price;
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
    SET @theSql=CONCAT('SELECT TRUNCATE(sum(price)*',in_hours,'*',no_of_days,'*',no_of_sitters,',2)');
    SET @theSql=CONCAT(@theSql,' as total_amount from job_prices jp join kids_ages ka on ka.id = jp.kids_age_id');
    SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and start_hours > ',in_hours,' AND ');
    SET @theSql=CONCAT(@theSql,in_hours,'<= end_hours');
    PREPARE stmt from @theSql; -- create a prepared stmt from the above concat
    EXECUTE stmt;   -- run it
    DEALLOCATE PREPARE stmt;    -- cleanup
END$
DELIMITER ;

MySqL 手册页 SQL Syntax for Prepared Statements .

请注意，上述 CONCAT() 仅在使用用户变量(带有 @ 符号)时才会成功。不是带有 DECLARE 的局部变量。