mysql中的模糊查询

原文链接：http://www.cnblogs.com/cyttina/p/3894428.html

http://blog.sina.com.cn/s/blog_667bef380101f2da.html

1.  参数中直接加入%%

　　param.setUsername("%CD%");
      param.setPassword("%11%");

	<select  id="selectPersons" resultType="person" parameterType="person">
		select id,sex,age,username,password from person where true 
			<if test="username!=null"> AND username LIKE #{username}</if>
			<if test="password!=null">AND password LIKE #{password}</if>
	
	</select>

2.  bind标签

<select id="selectPersons" resultType="person" parameterType="person">
  <bind name="pattern" value="'%' + _parameter.username + '%'" />
  select id,sex,age,username,password 
  from person
  where username LIKE #{pattern}
</select>

 

 

3. CONCAT

where username LIKE concat(concat('%',#{username}),'%')

模糊查询：

工作中用到，写三种用法吧，第四种为大小写匹配查询

 

1. sql中字符串拼接

   SELECT * FROM tableName WHERE name LIKE CONCAT(CONCAT('%', #{text}), '%');

 

2. 使用 ${...} 代替 #{...}

   SELECT * FROM tableName WHERE name LIKE '%${text}%'; 

 

3. 程序中拼接

   Java

   // or String searchText = "%" + text + "%";

   String searchText = new StringBuilder("%").append(text).append("%").toString();

   parameterMap.put("text", searchText);

 

   SqlMap.xml

   SELECT * FROM tableName WHERE name LIKE #{text};

 

4. 大小写匹配查询

[sql] view plain copy

1. SELECT *  FROM TABLENAME  WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('jz') || '%'  
2.  --或者是  
3. SELECT *   FROM TABLENAME  WHERE LOWER(SUBSYSTEM) LIKE '%' || LOWER('jz') || '%' 
   

最后一个是在自己写的时候，由于关键字为空，查询不出来的情况

<select id="queryPassMember" parameterType="map" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from shop_employee_relation
		<where>
			<if test="shopId != null">
				SHOP_ID= #{shopId}
				and delete_flag=1
			</if>
			<if test="mobile !=null and mobile !=''">
				AND shopkeeper_phone LIKE
				concat(concat('%',#{mobile}),'%')
			</if>
			<if test="memberName !=null and memberName !=''">
				AND member_name LIKE concat(concat('%',#{memberName}),'%')
			</if>
		</where>
		order by ROLE asc
	</select>

                                                           题外话：今天下午状态突然下降，有点饿，坐等下班中。