mysql 字段包含某个字符的函数
- 工作小总结
- 时间：2019-01-22 09:26
- 4362人已阅读
简介
通过sql查询语句，查询某个字段中包含特定字符串：例子：查询e_book表的types字段包含字符串"3"，有下面4种方式select * from e_book where types like "%3%";select * from e_boo
🔔🔔🔔好消息!好消息!🔔🔔🔔
有需要的朋友👉:联系凯哥
通过sql查询语句，查询某个字段中包含特定字符串：
例子：查询e_book表的types字段包含字符串"3"，有下面4种方式
select * from e_book where types like "%3%";
select * from e_book where find_in_set('3', types);
select * from e_book where locate('3', types);
select * from e_book where INSTR(types,'3');第2、3中方式相对速度较快
如果使用find_in_set不行的话，可以换成locate.
使用案例：
select * from (
select
cb.id, cb.code, cb.contract_type contractType, cb.target,b.real_name targetName,
cb.start_date startDate, cb.end_date endDate, cb.real_end_date realEndDate, cb.finance_group_id financeGroupId,
cb.dept_name jobGroupName,
cb.job_group_id jobGroupId,
cb.dept_name_en deptNameEn ,
cb.create_date createDate, cb.creater, cb.status,
cb.dept_code,
cb.dept_name_en,
cb.dept_name,
cb.product_line_code,
cb.product_line_name,
cb.subject_code,
cb.subject_name,
temp.name as tname,
case when locate('劳务', temp.name) then '1'
when locate('服务', temp.name) then '2'
end as newType
from cb_contract cb
left join base b on cb.target = b.id
left join contract_template temp on cb.contract_template = temp.id
where cb.is_delete = 1
and dept_code in
(
'DEP0009'
)
) cb
where 1=1
and (
(newType = '2'
and product_line_code in
(
'PL0092'
)
)
or (newType = '2' and ( product_line_code is null or product_line_code = ''))
or (newType = '1' )
)
order by cb.id desc
limit 0, 10