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.重点阐明：
- 查问字段strlist是否蕴含str，如果包含返回str所在下标(从1开始)，否则返回0.
- strlist是一个字符串汇合通过,宰割
- 当参数str或者strlist任何一个为null的时候后果返回null
- 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...