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>