FIND_IN_SET 与 LIKE 函数：使用场景及性能对比

FIND_IN_SET 与 LIKE 函数使用场景及性能对比示例下面通过具体的 SQL 示例和性能分析来说明两者的区别。1. 示例数据表-- 创建一个员工培训记录表CREATETABLEtraining(idINTPRIMARYKEY,staff_nameVARCHAR(50),coursesVARCHAR(100)-- 存储逗号分隔的课程如 course1,course2,course3);INSERTINTOtrainingVALUES(1,Angus,course1,course2),(2,Cathy,course2),(3,Aldis,course1,course3),(4,Lawson,course1,course2,course3),(5,Carl,course3),(6,Ben,NULL),(7,Rose,course1,course2);2. FIND_IN_SET 使用场景示例场景查询培训课程中包含完整且独立的course3的员工。SELECTstaff_name,coursesFROMtrainingWHEREFIND_IN_SET(course3,courses)0;结果staff_name | courses Aldis | course1,course3 Lawson | course1,course2,course3 Carl | course3特点精确匹配列表中的元素不会误匹配course33或mycourse3。只能处理逗号分隔的字符串。3. LIKE 使用场景示例场景 A查询培训课程中包含course3子串的员工模糊匹配SELECTstaff_name,coursesFROMtrainingWHEREcoursesLIKE%course3%;结果与上面相同但若数据中有course33也会被匹配staff_name | courses Aldis | course1,course3 Lawson | course1,course2,course3 Carl | course3场景 B查询课程以course1开头的员工SELECTstaff_name,coursesFROMtrainingWHEREcoursesLIKEcourse1%;结果staff_name | courses Angus | course1,course2 Aldis | course1,course3 Lawson | course1,course2,course3 Rose | course1,course2特点LIKE支持前缀、后缀、任意位置匹配更灵活但可能产生误匹配。4. 性能对比MySQL 环境4.1 索引利用测试在courses列上创建索引CREATEINDEXidx_coursesONtraining(courses);查询方式SQL 示例是否使用索引原因FIND_IN_SETWHERE FIND_IN_SET(course3, courses) 0❌ 否函数作用于列索引失效LIKE %value%WHERE courses LIKE %course3%❌ 否通配符在开头无法使用 B-Tree 索引LIKE value%WHERE courses LIKE course3%✅ 是前缀匹配可以利用索引LIKE %valueWHERE courses LIKE %course3❌ 否通配符在开头索引失效4.2 执行计划对比使用 EXPLAINEXPLAINSELECT*FROMtrainingWHEREFIND_IN_SET(course3,courses)0;输出关键信息typeALL全表扫描possible_keysNULL。EXPLAINSELECT*FROMtrainingWHEREcoursesLIKEcourse3%;输出typerangekeyidx_courses使用了索引。4.3 大数据量性能测试模拟 100 万行FIND_IN_SET全表扫描耗时约 2.5 秒。LIKE %course3%全表扫描耗时约 1.8 秒略快无需解析逗号。LIKE course3%索引范围扫描耗时 0.01 秒。5. 综合示例同一需求的不同实现需求查询培训了course3的员工。方法SQL优点缺点FIND_IN_SETFIND_IN_SET(course3, courses) 0语义准确不会误匹配无法使用索引全表扫描LIKE安全模式CONCAT(,, courses, ,) LIKE %,course3,%也能准确匹配独立元素无法使用索引且字符串拼接有开销LIKE简单模式courses LIKE %course3%代码简洁可能误匹配如course33规范化设计最佳将 courses 拆分为子表enrollment(staff_id, course)然后WHERE course course3可使用索引性能最优需要改表结构6. 实际开发建议小数据量 1 万行两者性能差异可忽略优先选择FIND_IN_SET保证语义正确。大数据量且不能改表结构如果只做精确匹配用FIND_IN_SET如果允许少量误匹配且追求性能用LIKE %value%但仍是全扫描。追求极致性能必须将逗号分隔字段规范化为关联表然后使用等值查询WHERE course course3利用索引加速。跨数据库兼容FIND_IN_SET是 MySQL 和 Hive 特有函数其他数据库PostgreSQL、SQL Server、Oracle需用LIKE模拟或内置函数如STRING_SPLIT。