mysql的内置函数instr(filed,str),作用是返回str子字符串在filed字符串的第一次出现的位置。当instr(filed,str)=0时,表示子符串str不存在于字符串filed中,因此可以用来实现mysql中的模糊查询,与like用法类似。如下:
// 1、instr()函数,#{name}为参数
select id,name from test where instr(name,#{name})>0上述写法等同于下面
// 2、like
select id,name from test where name like concat('%',#{name},'%')
instr(filed,str) > 0 ⇒ file like '%str%'
instr(filed,str) = 1 ⇒ file like 'str%'
instr(filed,str) = 0 ⇒ file not like '%str%'
下面是一段mapper.xml的部分示例代码
<select id="selectUserListByConds" parameterType="java.lang.String" resultMap="BaseResultMap">
select
a.userid,
a.username,
a.account,
a.password,
a.mobile,
a.description,
a.delete_flag,
a.enabled,
a.email,
a.address,
a.is_online,
a.created_time,
a.updated_time,
a.created_user,
a.updated_user,
a.org_code,
s.org_name,
a.limit_ip,
a.expiry_date,
a.last_login_time,
a.last_login_ip,
a.account_type,
a.account_type_name,
a.tenant_id
from s_user a
left join s_department s on s.org_code=a.org_code and s.tenant_id=a.tenant_id
where 1=1
<if test="showdelete==null">
and a.delete_flag!=1
</if>
<if test="account!=null and account!=''">
and instr(a.account,#{account})>0
</if>
<if test ="username!=null and username!=''">
and instr(a.username,#{username})>0
</if>
<if test="roleIds!=null and roleIds!=''">
and exists(select * from s_user_role where user_id=a.userid and role_id = #{roleIds})
</if>
<if test="orgCode!=null and orgCode!=''">
and instr(a.org_code,#{orgCode})>0
</if>
<if test="tenantId!=null and tenantId!=''">
and a.tenant_id=#{tenantId}
</if>
order by a.created_time DESC
</select>