sql语句批量执行多条语句和执行一句IN语句对比

--一共费时0.005秒
DELETE from pt_data_inbound_cms_h where id in(1,2,3,4,5);
--一共费时0.004*5
DELETE from pt_data_inbound_cms_h where id=1;
DELETE from pt_data_inbound_cms_h where id=2;
DELETE from pt_data_inbound_cms_h where id=3;
DELETE from pt_data_inbound_cms_h where id=4;
DELETE from pt_data_inbound_cms_h where id=5;

 

--每句执行时间平均0.005，共费时0.005*5
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id=1);
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id=2);
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id=3);
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id=4);
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id=5);
 

--共费时0.005s
INSERT into pt_data_inbound_cms_h (select * from pt_data_inbound_cms where id in(1,2,3,4,5));

mybatis 用foreach建议使用

<insert id="insertById" parameterType="com.newtv.search.core.entity.TaskClearContentType">
    insert into ${toTable}
    ( select * from ${fromTable} where id in
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
        #{item,jdbcType=INTEGER}
    </foreach>
    )
</insert>

<delete id="deleteById" parameterType="com.newtv.search.core.entity.TaskClearContentType">
    delete from ${fromTable}
    where
    id in
    <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
        #{item,jdbcType=INTEGER}
    </foreach>
</delete>

不建议使用如下：

<insert id="insertById" parameterType="com.newtv.search.core.entity.TaskClearContentType">
    <foreach collection="ids" index="index" item="item" separator=";">
        insert into ${toTable}
        ( select * from ${fromTable} where id=#{item,jdbcType=INTEGER})
    </foreach>
</insert>

<delete id="deleteById" parameterType="com.newtv.search.core.entity.TaskClearContentType">
    <foreach collection="ids" item="item" index="index" separator=";">
    delete from ${fromTable}
    where
    id = #{item,jdbcType=INTEGER}
    </foreach>
</delete>