准备语句,将逗号分隔的值传递给存储过程参数[英] prepare statement to pass comma-separated values to stored procedure parameter

本文是小编为大家收集整理的关于准备语句,将逗号分隔的值传递给存储过程参数的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我很难将查询转移到商店过程中.我需要将逗号分隔的字符串传递到两个IN子句的参数yr_model_no.我已经阅读了一些线程,并决定使用准备语句.原始查询可以很好地获取多行,但是在存储过程中,当我使用call load_things('1283943kd9,2e9kk389334','53')调用它时(第一个是模型号列表,第二个是用户ID),它只能从逗号分隔字符串的第一个值.首先,我认为这是yr_model_no参数的长度问题,但是将其更改为varchar(200)会给我未知的列2e9kk389334在" Where子句"错误中.有人可以帮助我找出以下代码中的准备语句或参数有什么问题吗?

这是一个http://sqlfiddle.com/#!!

DELIMITER ;;
CREATE PROCEDURE `load_things` (IN `yr_model_no` varchar(20), IN `yr_app_id` int(5))
BEGIN
    SET @s = 

CONCAT('
SELECT * FROM 
(
   SELECT COUNT( c.app_id ) AS users_no, ROUND( AVG( c.min ) , 1 ) AS avg_min, ROUND( AVG( c.max ) , 1 ) AS avg_max, a.mid, a.likes, a.dislikes, b.model_no
        FROM  `like` a
        RIGHT JOIN  `model` b ON a.mid = b.mid
        LEFT JOIN  `details` c ON c.mid = b.mid
        WHERE b.model_no IN (',yr_model_no,')
        GROUP BY b.model_no
)TAB1
JOIN
(
   SELECT a.app_id,b.model_no,IFNULL(c.isbooked,0) AS isbooked,d.min,d.max,e.like_type
     FROM `users` a
     JOIN `model` b
       ON b.model_no IN (',yr_model_no,')
LEFT JOIN `favorite` c 
       ON c.app_id = a.id
      AND c.mid = b.mid         
LEFT JOIN `details` d 
       ON d.app_id = a.id
      AND d.mid = b.mid 
LEFT JOIN `users_likes` e
       ON e.app_id = a.id
      AND e.mid = b.mid
    WHERE a.id = ',yr_app_id,'
)TAB2
ON TAB1.model_no = TAB2.model_no');

PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END;;
DELIMITER ;

推荐答案

如果您考虑您的语句正在生成的SQL,它看起来像

JOIN `model` b
ON b.model_no IN (1283943kd9,2e9kk389334)

这是无效的SQL(您需要围绕VARCHAR值引号)

您需要确保在将传递给过程的字符串中的每个值添加引号:

load_things('''1283943kd9'',''2e9kk389334''','53')

不确定这是否是逃脱MySQL中弦的正确方法(您可能需要查看文档)

这将导致:

JOIN `model` b
ON b.model_no IN ('1283943kd9','2e9kk389334')

本文地址:https://www.itbaoku.cn/post/597326.html

问题描述

I have trouble moving a query into a store procedure. I need to pass a comma-separated string to the parameter yr_model_no for two IN clauses. I have read a few threads and decided to use prepare statements. The original query works fine fetching multiple rows, but in the stored procedure, when I call it with call load_things('1283943kd9,2e9kk389334','53') (the first is a list of model numbers and the second is an user id), it can only fetch one single row from the first value of the comma-separate string. At first, I thought it was the length problem with the yr_model_no parameter, but changing it to varchar(200) would give me unknown column 2e9kk389334 in 'where clause' error. Can someone help me figure out what is wrong with the prepare statement or parameters in the following code?

Here's a similar fiddle example of my table schema and query.

DELIMITER ;;
CREATE PROCEDURE `load_things` (IN `yr_model_no` varchar(20), IN `yr_app_id` int(5))
BEGIN
    SET @s = 

CONCAT('
SELECT * FROM 
(
   SELECT COUNT( c.app_id ) AS users_no, ROUND( AVG( c.min ) , 1 ) AS avg_min, ROUND( AVG( c.max ) , 1 ) AS avg_max, a.mid, a.likes, a.dislikes, b.model_no
        FROM  `like` a
        RIGHT JOIN  `model` b ON a.mid = b.mid
        LEFT JOIN  `details` c ON c.mid = b.mid
        WHERE b.model_no IN (',yr_model_no,')
        GROUP BY b.model_no
)TAB1
JOIN
(
   SELECT a.app_id,b.model_no,IFNULL(c.isbooked,0) AS isbooked,d.min,d.max,e.like_type
     FROM `users` a
     JOIN `model` b
       ON b.model_no IN (',yr_model_no,')
LEFT JOIN `favorite` c 
       ON c.app_id = a.id
      AND c.mid = b.mid         
LEFT JOIN `details` d 
       ON d.app_id = a.id
      AND d.mid = b.mid 
LEFT JOIN `users_likes` e
       ON e.app_id = a.id
      AND e.mid = b.mid
    WHERE a.id = ',yr_app_id,'
)TAB2
ON TAB1.model_no = TAB2.model_no');

PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END;;
DELIMITER ;

推荐答案

If you think about the SQL that your statement is generating it will look like this

JOIN `model` b
ON b.model_no IN (1283943kd9,2e9kk389334)

This is invalid SQL (you need quotes around the varchar values)

You need to make sure you add quotes around each value in the string that you pass to your procedure:

load_things('''1283943kd9'',''2e9kk389334''','53')

Not sure if this is the correct way to escape strings in MySQL (you might need to look at the docs)

This will result in:

JOIN `model` b
ON b.model_no IN ('1283943kd9','2e9kk389334')