为什么find_in_set有效但是IN子句 [英] Why find_in_set works but IN clause
问题描述
我有这样的代码.
DELIMITER $$
CREATE PROCEDURE `sp_deleteOrderData`(orderid BIGINT(11))
BEGIN
DECLARE shipmentnumbers VARCHAR(1000);
DECLARE cartonid VARCHAR(1000);
SELECT GROUP_CONCAT(a_shipmentid) FROM t_shipment WHERE a_orderid = orderid INTO shipmentnumbers;
SELECT GROUP_CONCAT(a_cartonid) FROM t_carton WHERE a_shipmentid IN (shipmentnumbers) INTO cartonid;
SELECT shipmentnumbers;
/*SELECT cartonid; */
END$$
DELIMITER ;
此处发货编号返回100020,100021,100022
理想情况下,cartonid应该以11,12,13
Here shipmentnumbers returns 100020,100021,100022
Ideally cartonid should be returned as 11,12,13
但是我只有11个作为肉毒杆菌素.
But i get only 11 as cartonid.
但是当我使用下面的代码时,我会得到正确的结果.
But when i use below code i get proper result.
SELECT GROUP_CONCAT(a_cartonid) FROM t_carton WHERE FIND_IN_SET( a_shipmentid, shipmentnumbers ) INTO cartonid;
我想知道IN和FIND_IN_SET之间到底有什么区别,然后我决定要使用什么.
I wanted to know what exactly is the difference between IN and FIND_IN_SET and hwo di i decide what to use.
推荐答案
IN
接受要搜索的列表或参数,FIND_IN_SET
接受包含逗号分隔列表的字符串参数:
IN
accepts a list or parameters to search, FIND_IN_SET
accepts a string parameter containing a comma-separated list:
SELECT 1 IN (1, 2, 3, 4)
SELECT FIND_IN_SET(1, '1,2,3,4')
如果您尝试将IN
应用于逗号分隔的字符串,它将会将其视为单个参数,并将其作为一个整体进行匹配:
If you try to apply IN
to a comma-separated string, it will treat it as a single parameter and will match it as a whole:
SELECT 1 IN ('1,2,3,4')
当然,字符串'1'
与字符串'1,2,3,4'
不相等,因此上面的查询返回false.
Of course, the string '1'
is not equal to the string '1,2,3,4'
so the query above returns false.
这篇关于为什么find_in_set有效但是IN子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!