在开发中有时会使用到 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
