为什么find

本文介绍了为什么find_in_set有效但是IN子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的代码.

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.