如何在存储过程中传递带有单引号的输入参数

Question

我有一个存储过程r，我正在传递下面的字符串，但它不工作？？

call Prc_SearchKeyword('randomize', "'girl', 'playing'",2,0,31);

下面的语法运行良好。

call Prc_SearchKeyword('randomize', 'girl',1,0,31);



DELIMITER $$

CREATE PROCEDURE `Prc_SearchKeyword`(IN filter VARCHAR(50), IN midquery varchar(200), IN keywordcount int(10), In firstimage int(10), in lastimage int(10) )
BEGIN
IF(filter = 'nbofviews') Then
SELECT sd.imageid, sd.imgcollection, sd.Caption FROM   (SELECT p.imageid  FROM searchinitial_views p join searchkwdmgmt s on p.primarykeyword = s.primary_kwd and s.allkwd in (midquery) GROUP  BY p.imageid HAVING Count(distinct p.primarykeyword) = keywordcount order by views desc  LIMIT  firstimage, lastimage ) q join searchdetails sd ON sd.imageid = q.imageid ;
END IF;
IF(filter = 'shootdate') Then
SELECT sd.imageid, sd.imgcollection, sd.Caption FROM   (SELECT p.imageid  FROM searchinitial_shootdate p join searchkwdmgmt s on p.primarykeyword = s.primary_kwd and s.allkwd in (midquery) GROUP  BY p.imageid HAVING Count(distinct p.primarykeyword) = keywordcount order by shootdate desc  LIMIT  firstimage, lastimage ) q join searchdetails sd ON sd.imageid = q.imageid ;
END IF;
IF(filter = 'randomize') Then
SELECT sd.imageid, sd.imgcollection, sd.Caption FROM   (SELECT p.imageid  FROM searchinitial_random p join searchkwdmgmt s on p.primarykeyword = s.primary_kwd and s.allkwd in (midquery) GROUP  BY p.imageid HAVING Count(distinct p.primarykeyword) = keywordcount order by random desc  LIMIT  firstimage, lastimage ) q join searchdetails sd ON sd.imageid = q.imageid;
END IF;
IF(filter = 'priority') Then
SELECT sd.imageid, sd.imgcollection, sd.Caption FROM   (SELECT p.imageid  FROM searchinitial_priority p join searchkwdmgmt s on p.primarykeyword = s.primary_kwd and s.allkwd in (midquery) GROUP  BY p.imageid HAVING Count(distinct p.primarykeyword) = keywordcount order by priority desc  LIMIT  firstimage, lastimage ) q join searchdetails sd ON sd.imageid = q.imageid ;
END IF;
END

Answer 1

您需要查找如何转义字符，即使用两个单引号而不是一个''girl'',''playing''

Answer 2

您可以使用两个单引号来转义单引号:例如：

... where lastname='O''Malley'

在您的案例中：

''girl'',''playing''

Answer 3

由于MySQL将单引号和双引号都识别为字符串分隔符，因此当您希望在值中包含单引号时，一种选择是使用双引号，反之亦然。

call YourProc("'girl', 'playing'");

另一种方法是使用反斜杠对引号进行转义：

call YourProc('\'girl\', \'playing\'');