mysql - FIND_IN_SET()

FIND_IN_SET(str,strlist)

官方说明

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

A string list is a string composed of substrings separated by , characters. 

If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. 

Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.

重点说明：

1. 查询字段strlist是否包含str，如果包括返回str所在下标(从1开始)，否则返回0.
2. strlist是一个字符串集合通过,分割
3. 当参数str或者strlist任何一个为null的时候结果返回null
4. str参数不应该包含,

看几个示例

// 第二个数匹配
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

// 按照,分割 没有匹配的
SELECT FIND_IN_SET('b','ab,c,d');
        -> 0

// 分割后abc和ab不匹配
SELECT FIND_IN_SET('ab','abc,ab,ss');
        -> 2

// 多个匹配按照第一个匹配算
SELECT FIND_IN_SET('ab','abc,ab,ss,ab');
        -> 2

一些项目中的实际应用

// 导入测试数据  用户擅长的编程语言
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_language
-- ----------------------------
DROP TABLE IF EXISTS `user_language`;
CREATE TABLE `user_language` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `language` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user_language
-- ----------------------------
BEGIN;
INSERT INTO `user_language` VALUES (1, 'aa', 'PHP,GO,JAVA');
INSERT INTO `user_language` VALUES (2, 'bb', 'C,GO');
INSERT INTO `user_language` VALUES (3, 'cc', 'RUST,PHP');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

// 查询擅长php语言的用户
SELECT * from user_language where FIND_IN_SET('PHP',language)

---

参考

• https://dev.mysql.com/doc/ref...