解决mysql的in条件中参数是带引号的字符串的时候查询失效

作者: a_simple_coder | 来源:发表于2019-02-25 11:19 被阅读0次

解决mysql的in条件中参数是带引号的字符串的时候查询失效
记mysql in条件中参数是带引号的字符串查询失效
数据库篇
MySQL 面试系列： `order by` 语法详解
解决mysql查询，in条件参数为带逗号的字符串，查询结果错误
MySQL语句实例
mysql的json查询、coalesce函数、case whe
mysql文章汇集
Linux（四）-文件管理
oracle匹配字段中包含单引号

在开发中有时会使用到 IN 查询，并且里面的参数又是数据库中的一个字段，比如如下错误的例子

    <resultMap type="MisProjectFirstTrial" id="MisProjectFirstTrialResult">
        <result property="projectId" column="PROJECT_ID"/> 
        <result property="projectName" column="PROJECT_NAME"/> 
        <result property="coordinateUser" column="COORDINATE_USER"/> 
         <collection property="coordinateUserList" column="COORDINATE_USER" ofType="User" javaType="list" select="findUserById">
            <result property="id" column="id" />
            <result property="code" column="code" />
            <result property="name" column="name" />
         </collection>
    </resultMap>
    <!-- 根据userId查询user信息 -->
    <select id="findUserById" parameterType="String" resultType="net.northking.entity.User">
        SELECT * FROM mis_user WHERE id in (#{COORDINATE_USER})
    </select>
    
    
    <!-- 获取所有的项目列表 -->
    <select id="findAllFirstTrialProjectList" resultMap="MisProjectFirstTrialResult">
        SELECT * FROM mis_project_first_trial
    </select>

上面语句查询的表数据如下:

image.png

上面根据用户id查询用户信息，但是这里传进去的是COORDINATE_USER，其实这个字段传进去实际上在数据库是这样查询的（多了个引号）：

SELECT * FROM mis_user WHERE id in ("102,103,104");

而不是

SELECT * FROM mis_user WHERE id in (102,103,104);

或者：

SELECT * FROM mis_user WHERE id in ("102","103","104");

在mysql中in里面如果是字符串的话，会自动转化成int类型的，内部使用了如下方法：

CAST("102,103,104" AS INT)

这个问题其实可以使用另外一个mysql的函数来解决：

FIND_IN_SET('查询的值（如:1）', '使用逗号隔开的字符串集合，如:（'1,2,3'）')

正确的sql语句如下：

SELECT * FROM mis_user WHERE and FIND_IN_SET(id,"102,103,104");

结果为：

image.png

所以上面的xml查询user信息的语句可变为：

    <!-- 根据userId查询user信息 -->
    <select id="findUserByIds" parameterType="String" resultType="net.northking.entity.User">
        SELECT id,code,name FROM mis_user WHERE status='0' AND FIND_IN_SET(id,#{COORDINATE_USER})
    </select>

获取的结果如下：

DispatcherServlet with name 'SpringMVC' processing POST request for [/project/projectFirstTrial/findAllFirstTrialProjectList.do]
 ...
==>  Preparing: SELECT mpf.ID ID, mpf.PROJECT_ID PROJECT_ID, mpf.PROJECT_NAME PROJECT_NAME, mpf.PROJECT_TYPE PROJECT_TYPE, mpf.INITIATE_ORG INITIATE_ORG, mpf.INITIATE_USER INITIATE_USER, mpf.COORDINATE_ORG COORDINATE_ORG,mpf.COORDINATE_USER COORDINATE_USER, mpf.STATUS STATUS, mpf.APPLICATION_TIME APPLICATION_TIME, mpf.FIRSTTRIAL_TIME FIRSTTRIAL_TIME, mpf.FIRSTTRIAL_CONCLUSION FIRSTTRIAL_CONCLUSION, mpf.REMARKS REMARKS,mpf.TEC_FEA_SUB_TIME TEC_FEA_SUB_TIME,mpf.BUS_FEA_SUB_TIME BUS_FEA_SUB_TIME, mpf.FIRSTTRIAL_CONS_TIME FIRSTTRIAL_CONS_TIME,mpf.SCHEDULE_TYPE SCHEDULE_TYPE, mu.code INITIATE_USER_CODE,mu.name INITIATE_USER_NAME, mo.code INITIATE_ORG_CODE,mo.name INITIATE_ORG_NAME FROM mis_project_first_trial mpf,mis_user mu,mis_orgin mo WHERE mpf.INITIATE_USER = mu.Id AND mpf.INITIATE_ORG = mo.id 
==> Parameters: 
<==    Columns: ID, PROJECT_ID, PROJECT_NAME, PROJECT_TYPE, INITIATE_ORG, INITIATE_USER, COORDINATE_ORG, COORDINATE_USER, STATUS, APPLICATION_TIME, FIRSTTRIAL_TIME, FIRSTTRIAL_CONCLUSION, REMARKS, TEC_FEA_SUB_TIME, BUS_FEA_SUB_TIME, FIRSTTRIAL_CONS_TIME, SCHEDULE_TYPE, INITIATE_USER_CODE, INITIATE_USER_NAME, INITIATE_ORG_CODE, INITIATE_ORG_NAME
<==        Row: 1, DSA0990, 测试1, 未立项, 010039, 101, 010242,010033,010039, 102,103,104, 1, 2019-01-11, 2019-01-12, qqq, qqq, 2019-01-13, 2019-01-13, 2019-01-11, 项目排期, wangj1, 王珏, 010039, 风险管理部
====>  Preparing: SELECT id,code,name FROM mis_user WHERE status='0' AND FIND_IN_SET(id,?) 
====> Parameters: 102,103,104(String)
<====    Columns: id, code, name
<====        Row: 102, liutc, 刘天成
<====        Row: 103, zhangx, 张翔
<====        Row: 104, wul1, 吴磊
<====      Total: 3
====>  Preparing: SELECT * FROM mis_orgin WHERE status = '0' AND FIND_IN_SET(id,?) 
====> Parameters: 010242,010033,010039(String)
<====    Columns: id, CODE, NAME, STATUS, PARENT_ID, CREATOR, MODIFIE, SSO_ORG_CODE, TYPE
<====        Row: 010033, 010033, 小微金融部, 0, 010001, null, null, 33, 1
<====        Row: 010039, 010039, 风险管理部, 0, 010001, null, null, 39, 1
<====        Row: 010242, 010242, 总行营业部, 0, 010001, null, null, 242, 1
<====      Total: 3
<==        Row: 2, DSA09910, 测试2, 未立项, 010039, 101, 010242,010033,010039, 102,103,104, 1, 2019-01-11, 2019-01-12, qqq, qqq, 2019-01-13, 2019-01-13, 2019-01-11, 项目排期, wangj1, 王珏, 010039, 风险管理部
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2a733b10]
[DEBUG][2019/02/25 18:08:515  ][org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:327)]
Returning JDBC Connection to DataSource
...

这是两条COORDINATE_USER相同的数据，最后collection里的查询语句只执行了一遍，当数据为：

image.png

执行的结果如下：

==>  Preparing: SELECT mpf.ID ID, mpf.PROJECT_ID PROJECT_ID, mpf.PROJECT_NAME PROJECT_NAME, mpf.PROJECT_TYPE PROJECT_TYPE, mpf.INITIATE_ORG INITIATE_ORG, mpf.INITIATE_USER INITIATE_USER, mpf.COORDINATE_ORG COORDINATE_ORG,mpf.COORDINATE_USER COORDINATE_USER, mpf.STATUS STATUS, mpf.APPLICATION_TIME APPLICATION_TIME, mpf.FIRSTTRIAL_TIME FIRSTTRIAL_TIME, mpf.FIRSTTRIAL_CONCLUSION FIRSTTRIAL_CONCLUSION, mpf.REMARKS REMARKS,mpf.TEC_FEA_SUB_TIME TEC_FEA_SUB_TIME,mpf.BUS_FEA_SUB_TIME BUS_FEA_SUB_TIME, mpf.FIRSTTRIAL_CONS_TIME FIRSTTRIAL_CONS_TIME,mpf.SCHEDULE_TYPE SCHEDULE_TYPE, mu.code INITIATE_USER_CODE,mu.name INITIATE_USER_NAME, mo.code INITIATE_ORG_CODE,mo.name INITIATE_ORG_NAME FROM mis_project_first_trial mpf,mis_user mu,mis_orgin mo WHERE mpf.INITIATE_USER = mu.Id AND mpf.INITIATE_ORG = mo.id 
==> Parameters: 
<==    Columns: ID, PROJECT_ID, PROJECT_NAME, PROJECT_TYPE, INITIATE_ORG, INITIATE_USER, COORDINATE_ORG, COORDINATE_USER, STATUS, APPLICATION_TIME, FIRSTTRIAL_TIME, FIRSTTRIAL_CONCLUSION, REMARKS, TEC_FEA_SUB_TIME, BUS_FEA_SUB_TIME, FIRSTTRIAL_CONS_TIME, SCHEDULE_TYPE, INITIATE_USER_CODE, INITIATE_USER_NAME, INITIATE_ORG_CODE, INITIATE_ORG_NAME
<==        Row: 1, DSA0990, 测试1, 未立项, 010039, 101, 010242,010033,010039, 102,103,104, 1, 2019-01-11, 2019-01-12, qqq, qqq, 2019-01-13, 2019-01-13, 2019-01-11, 项目排期, wangj1, 王珏, 010039, 风险管理部
====>  Preparing: SELECT id,code,name FROM mis_user WHERE status='0' AND FIND_IN_SET(id,?) 
====> Parameters: 102,103,104(String)
<====    Columns: id, code, name
<====        Row: 102, liutc, 刘天成
<====        Row: 103, zhangx, 张翔
<====        Row: 104, wul1, 吴磊
<====      Total: 3
====>  Preparing: SELECT * FROM mis_orgin WHERE status = '0' AND FIND_IN_SET(id,?) 
====> Parameters: 010242,010033,010039(String)
<====    Columns: id, CODE, NAME, STATUS, PARENT_ID, CREATOR, MODIFIE, SSO_ORG_CODE, TYPE
<====        Row: 010033, 010033, 小微金融部, 0, 010001, null, null, 33, 1
<====        Row: 010039, 010039, 风险管理部, 0, 010001, null, null, 39, 1
<====        Row: 010242, 010242, 总行营业部, 0, 010001, null, null, 242, 1
<====      Total: 3
<==        Row: 2, DSA09910, 测试2, 未立项, 010039, 101, 010027,010263, 110,113, 1, 2019-01-11, 2019-01-12, qqq, qqq, 2019-01-13, 2019-01-13, 2019-01-11, 项目排期, wangj1, 王珏, 010039, 风险管理部
====>  Preparing: SELECT id,code,name FROM mis_user WHERE status='0' AND FIND_IN_SET(id,?) 
====> Parameters: 110,113(String)
<====    Columns: id, code, name
<====        Row: 110, tengrk, 滕人科
<====        Row: 113, hanz, 韩震
<====      Total: 2
====>  Preparing: SELECT * FROM mis_orgin WHERE status = '0' AND FIND_IN_SET(id,?) 
====> Parameters: 010027,010263(String)
<====    Columns: id, CODE, NAME, STATUS, PARENT_ID, CREATOR, MODIFIE, SSO_ORG_CODE, TYPE
<====        Row: 010027, 010027, 科技管理部, 0, 010001, null, null, 27, 2
<====        Row: 010263, 010263, 产品创新中心, 0, 010001, null, null, 263, 2
<====      Total: 2
<==      Total: 2