MySQL模糊查询的三种方式

一、Mybatis常用模糊查询方法

• 直接使用 % 字符串拼接，如 ‘%’#{name}‘%’ 或 “%”#{name}“%”，单引号或双引号都可以。
• 使用’%${name}%',会有SQL注入危险
• 使用函数concat拼接concat(“%”,#{name},“%”)

1.使用concat(“%”,#{name},“%”)

UserMapper.xml文件：

 <select id="selectUsersByCondition" resultType="qin.com.entity.Users" parameterType="Users">
        select * from users
        <where>
            <if test="name != null and name != ''">
                <!--
                and name like concat(concat("%",#{name},"%"))
                ==>  Preparing: select * from users WHERE name like concat(concat("%",?,"%")) order by id desc
                -->
                and name like concat("%",#{name},"%")
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="birthday != null and birthday != ''">
                and birthday = #{birthday}
            </if>
            <if test="createTime != null and createTime != ''">
                and createTime>=#{createTime}
            </if>
            <if test="updateTime != null and updateTime != ''">
                and updateTime &lt;= #{updateTime}
            </if>
        </where>
        order by id desc
    </select>

Test测试代码

@Test
    public void testSelectUsersByCondition() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UsersService usersService =(UsersService) applicationContext.getBean("usersService");
        Users users = new Users();
        users.setName("小");
        List<Users> list = usersService.selectUsersByCondition(users);
        list.forEach(users1 -> System.out.println("根据条件查询用户信息："+users1));
    }

输出结果：
查询语句
==> Preparing: select * from users WHERE name like concat(“%”,?,“%”) order by id desc
==> Parameters: 小(String)

2.使用’%${name}%’

UserMapper.xml文件：

 <if test="name != null and name != ''">
	   <!--and name like '%${name}%'-->
	   and name like '%${name}%'
 </if>

输出结果：
==> Preparing: select * from users WHERE name like ‘%小%’ order by id desc
==> Parameters:

3.使用"%“#{name}”%"

<if test="name != null and name != ''">
   and name like "%"#{name}"%"
</if>

输出语句：
==> Preparing: select * from users WHERE name like “%”?“%” order by id desc
==> Parameters: 小(String)

总结：

推荐使用第一和第三种方式