Mybatis 相关操作(想起来就记录一下~~)

布满荆棘的人生 2023-06-27 03:18 29阅读 0赞

1.  字符串用逗号隔开转列  
    如数据库某一字段存储值为： 01,02,03 查询转列显示

SELECT 
    	substring_index(
    		substring_index('01,02,03',',',help_topic_id+1),',',-1
    	) as Id
    FROM mysql.help_topic
    WHERE help_topic_id < (length('01,02,03')-length(replace('01,02,03',',',''))+1)

![结果][2020010216005038.png]  
2. MYSQL批量插入操作：存在则更新，不存在插入（以主键为唯一值）

/** * * @title insertBatch * @Description 批量保存或更新 * @author chenlf * @param erStatusList */
    	void insertBatch(List<EvalResultStatus> list);

<insert id="insertBatch" parameterType="java.util.List">
    		INSERT INTO t_eval_result_status(
    			id,
    			raters_id,
    			eval_manage_id,
    			bs_id,
    			type,
    			status
    		) VALUES
    		<foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
    			#{item.id},
    			#{item.ratersId},
    			#{item.evalManageId},
    			#{item.bsId},
    			#{item.type},
    			#{item.status}
    		</foreach>
    			 ON DUPLICATE KEY UPDATE
                 	status = values(status),
                 	bs_id= values(bs_id)
    	</insert>

1.  ORCLE批量插入操作：

<insert id="insertGroupCompanyRelation" >
    merge into T_BZH_TTLM_CY cy
    using
    (
        <foreach collection="companyIds" item="item" separator="union all"> 
             SELECT
                #{groupId, jdbcType=VARCHAR} AS GROUP_ID,
                #{item, jdbcType=VARCHAR} AS company_id
             FROM DUAL
    </foreach>
    ) t
    on (cy.GROUP_ID = t.GROUP_ID and cy.company_id = t.company_id) 
    when matched then 
        update set 
        fail.time = t1.time,
    when not matched then 
    insert
    (cy.GROUP_ID, cy.COMPANY_ID)
    values
    (t.GROUP_ID, t.COMPANY_ID)
    </insert>

[2020010216005038.png]: https://img-blog.csdnimg.cn/2020010216005038.png