FIND_IN_SET(str,strlist)
strlist是有由英文逗号(,)分隔的字符串。如果str存在strlist的列表中,则返回str在strlist中的位置;
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); +----------------------------+ | FIND_IN_SET('b','a,b,c,d') | +----------------------------+ | 2 | +----------------------------+ 1 row in set
如果str是一个常量字符串,strlist是一个SET类型的字符串,则FIND_IN_SET函数将使用位运算(bit arithmetic)进行优化;
如果str不存在strlist中,或者strlist为空字符串,则返回0;
mysql> SELECT FIND_IN_SET('e','a,b,c,d'); +----------------------------+ | FIND_IN_SET('e','a,b,c,d') | +----------------------------+ | 0 | +----------------------------+ 1 row in set mysql> SELECT FIND_IN_SET('b',''); +---------------------+ | FIND_IN_SET('b','') | +---------------------+ | 0 | +---------------------+ 1 row in set
str和strlist任何一个为NULL值,则返回NULL值;
mysql> SELECT FIND_IN_SET(NULL,'a,b,c,d'); +-----------------------------+ | FIND_IN_SET(NULL,'a,b,c,d') | +-----------------------------+ | NULL | +-----------------------------+ 1 row in set mysql> SELECT FIND_IN_SET('b',NULL); +-----------------------+ | FIND_IN_SET('b',NULL) | +-----------------------+ | NULL | +-----------------------+ 1 row in set
如果第一个参数str包含英文逗号(,),则函数工作不正常;
mysql> SELECT FIND_IN_SET('b,','a,b,c,d'); +-----------------------------+ | FIND_IN_SET('b,','a,b,c,d') | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set
函数不会使用索引,如果查询字段有索引,并且可以使用IN的时候,请使用IN;
expr IN (value,…)
如果expr等于IN列表中的任意值,则返回1;否则返回0;
如果所有的值都是常量,则根据expr的类型进行计算和分类。
如果IN列表全部由常量组成,则IN的速度非常快;
类型转换规则将遵循Type Conversion in Expression Evaluation;
mysql> SELECT 2 IN (0,3,5,7); +----------------+ | 2 IN (0,3,5,7) | +----------------+ | 0 | +----------------+ 1 row in set mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); +----------------------------------+ | 'wefwf' IN ('wee','wefwf','weg') | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set
IN可以用于compare row constructors;
mysql> SELECT (3,4) IN ((1,2), (3,4)); +-------------------------+ | (3,4) IN ((1,2), (3,4)) | +-------------------------+ | 1 | +-------------------------+ 1 row in set mysql> SELECT (3,4) IN ((1,2), (3,5)); +-------------------------+ | (3,4) IN ((1,2), (3,5)) | +-------------------------+ | 0 | +-------------------------+ 1 row in set
永远不要再IN的列表中混合加引号和不加引号的值,因为加引号和不加引号值的比较规则是不一样的。混合类型将返回不一致的结果;
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a'); #需要使用下面的 SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
IN列表的具体个数由 max_allowed_packet 配置决定;
为了同SQL标准相一致,如果表达式左侧为NULL,或者找不到匹配项,或者列表中有值为NULL,则返回NULL;
mysql> SELECT NULL IN ('wee','wefwf','weg'); +-------------------------------+ | NULL IN ('wee','wefwf','weg') | +-------------------------------+ | NULL | +-------------------------------+ 1 row in set
IN可以用在子查询中,Subqueries with ANY, IN, or SOME;