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;

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

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

FIND_IN_SET(str,strlist)

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);

查询结果

mysql 字段判断是否包含某个字符串_数据_02

LOCATE(substr,str)

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);

查询结果

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

非期望情况

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


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

POSITION(substr IN str)

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);

查询结果

mysql 字段判断是否包含某个字符串_数据_05

非期望情况

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


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

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 AND INSTR(support_goods_level,'1');

查询结果

mysql 字段判断是否包含某个字符串_数据_07

非期望情况

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