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

旧城等待， 2022-12-20 12:40 333阅读 0赞

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center][]

## 查询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);

#### 查询结果 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 1][]

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

#### 查询结果 ####

![20201112141246124.png_pic_center][]

#### 非期望情况 ####

![20201112141350719.png_pic_center][]  
故，如果数据中有类似数据会查询不正确。

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

#### 查询结果 ####

![20201112141526689.png_pic_center][]

#### 非期望情况 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 2][]  
故，如果数据中有类似数据会查询不正确。

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

#### 查询结果 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 3][]

#### 非期望情况 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/3e6ac3f16d8b43009e6745685ea4d9b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/d666f45f3fe34233b23f14d22ad45c68.png
[20201112141246124.png_pic_center]: /images/20221120/66499473a17841d3887b75ae64a2971a.png
[20201112141350719.png_pic_center]: /images/20221120/36d8c95d52a64f35a5ea02bec88a24c5.png
[20201112141526689.png_pic_center]: /images/20221120/8b72954b8b814e16a85ecf17e5c7cc16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201112141604705.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201112141721558.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201112141748254.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA=,size_16,color_FFFFFF,t_70#pic_center