mysql 字段判断是否包含某个字符串

• • 整体数据
  • 查询support_goods_level中包含1或者2的数据

• • • FIND_IN_SET(str,strlist)

• • • • 查询结果

• • • LOCATE(substr,str)

• • • • 非期望情况

• • • POSITION(substr IN str)

• • • INSTR(str,substr)

SELECT id,learn_plan_id,sequence,is_valid,support_goods_level FROM qs_study_subject_plan_configuration WHERE is_valid=1 AND learn_plan_id=3;      

SELECT id,learn_plan_id,sequence,is_valid,support_goods_level FROM qs_study_subject_plan_configuration WHERE is_valid=1 AND learn_plan_id=3 AND FIND_IN_SET('1', support_goods_level);      

SELECT id,learn_plan_id,sequence,is_valid,support_goods_level FROM qs_study_subject_plan_configuration WHERE is_valid=1 AND learn_plan_id=3 AND LOCATE('1',support_goods_level);      

故，如果数据中有类似数据会查询不正确。

SELECT id,learn_plan_id,sequence,is_valid,support_goods_level FROM qs_study_subject_plan_configuration WHERE is_valid=1 AND learn_plan_id=3 AND POSITION('1' IN support_goods_level);      

SELECT id,learn_plan_id,sequence,is_valid,support_goods_level FROM qs_study_subject_plan_configuration WHERE is_valid=1 AND learn_plan_id=3 AND INSTR(support_goods_level,'1');