Mysql的instr()函数用法及说明
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">Mysql的instr()函数用法</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">mysql的内置函数instr(filed,str)</a></li><li><a href="#_lab2_0_1">当instr(filed,str)=0时</a></li><li><a href="#_lab2_0_2">下面是一段mapper.xml的部分示例代码 </a></li></ul><li><a href="#_label1">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>Mysql的instr()函数用法</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>mysql的内置函数instr(filed,str)</h3>
<p>作用是返回str子字符串在filed字符串的第一次出现的位置。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>当instr(filed,str)=0时</h3>
<p>表示子符串str不存在于字符串filed中，因此可以用来实现mysql中的模糊查询，与like用法类似。</p>
<p>如下：</p>
<div class="jb51code"><pre class="brush:sql;"> // 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%'</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>下面是一段mapper.xml的部分示例代码 </h3>
<div class="jb51code"><pre class="brush: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></pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]