MySql 及MyBatis数据的批量操作

1、Mybatis操作

1、批量更新

  <update id="updateCtcc" parameterType="java.util.List">update ctcc set scan=1 where id in<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">${item.id}</foreach></update>

以上是批量更新的正确方式，测试下来80000条数据只需要不到一分钟
而使用case when方式就卡住不动了，如下：

 <update id="updateCtccCase" parameterType="java.util.List">update ctccset scan =<foreach collection="list" item="item" index="index"separator=" " open="case ID" close="end">when id=#{item.id} then 1</foreach></update>

还有一种方式，单条更新，如下：

    <update id="updateCtcc" parameterType="java.util.Map">separator=";" 代表着每次循环完，在sql后面放一个分号item="xml" 循环List的每条的结果集collection="list" list 即为 map传过来的参数key --><foreach collection="list" separator=";" item="cus">update ctcc setscan = 1where id = #{xml.id}</foreach></update>

这种方式操作下来会报错，不知道为啥，至于报的什么错误，已经记不得了

2、批量处理

1、批量插入两种方式

<insert id="insertBatch">INSERT INTO persons(id, name, sex)VALUES<foreach collection ="list" item="user" separator =",">(#{user.id}, #{user.name}, #{user.sex})</foreach >
</insert>

mybatis三种批量插入方式对比

文件导入
jdbc批处理

绝对干货，教你4分钟插入1000万条数据到mysql数据库表

JAVA向Mysql插入亿级别数据—测评

关于批量插入数据之我见（100万级别的数据，mysql）

MySQL大数据量分页查询方法及其优化

字符串截取
MySQL字符串函数substring：字符串截取

Incorrect string value: ‘\xE5\x8C\x97\xE4\xBA\xAC’ for column

通过把输入值赋予一个用户变量，同时不把变量赋予表中的列，来丢弃此输入值：

LOAD DATA INFILE 'file.txt'  INTO TABLE t1  (column1, @dummy, column2, @dummy, column3); 

这样file.txt中的第二列和第四列不被导入到t1表中
https://blog.csdn.net/etimechen/article/details/50521636

  String esquel = " LOAD DATA LOCAL INFILE '" + path +"' INTO TABLE rp_cre_data_mobile_track_ctcc_copy " +" FIELDS TERMINATED BY ',' ENCLOSED BY \'\"'" +" LINES TERMINATED BY \'\\n\' (mobile_id,secret_mobile,create_time,mark,test)";jdbcTemplate.update(esquel);

public void loadDataFromJava(final List<TrackTest> urlList) {String sql = "insert into rp_cre_data_mobile_track_ctcc_copy(mobile_id,secret_mobile,create_time,mark,test) values(?,?,?,?,?)";jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {public void setValues(PreparedStatement ps, int i) throws SQLException {long mobileId = urlList.get(i).getMobileId();String secret_mobile = urlList.get(i).getMmd5();long createTime = urlList.get(i).getCreateTime();int mark = urlList.get(i).getMark();int test = urlList.get(i).getTest();ps.setLong(1, mobileId);ps.setString(2, secret_mobile);ps.setLong(3, createTime);ps.setInt(4, mark);ps.setInt(5, test);}public int getBatchSize() {return urlList.size();}});}

public static void insert() {Connection conn = null;PreparedStatement pstm = null;// 开时时间Long begin = new Date().getTime();try {//加载jdbc驱动Class.forName("com.mysql.jdbc.Driver");//连接mysqlconn = DriverManager.getConnection(url, user, password);// sql前缀String prefix = "INSERT INTO rp_cre_data_mobile_track_ctcc(mobile_id,mmd5,create_time,mark,test) VALUES";// 保存sql后缀StringBuffer suffix = new StringBuffer();// 设置事务为非自动提交conn.setAutoCommit(false);// Statement st = conn.createStatement();// 比起st，pst会更好些PreparedStatement pst = conn.prepareStatement("");// 外层循环，总提交事务次数long unixTime = System.currentTimeMillis() / 1000L;for (int i = 1; i <= 100; i++) {// 第次提交步长   SYSDATE(),long bTime = System.currentTimeMillis();for (int j = 1; j <= 100000; j++) {// 构建sql后缀String result = "(" + j * i + ", '"+Utils.MD5(j * i + "#6s3k")+"', "+unixTime + ",0,1),";suffix.append(result);}// 构建完整sqlString sql = prefix + suffix.substring(0, suffix.length() - 1);// 添加执行sqlpst.addBatch(sql);// 执行操作pst.executeBatch();// 提交事务conn.commit();long aTime = System.currentTimeMillis();System.out.println("成功插入第" + i * 10 + "W条数据耗时：" + (aTime - bTime));// 清空上一次添加的数据suffix = new StringBuffer();}// 头等连接pst.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}// 结束时间Long end = new Date().getTime();// 耗时System.out.println("cast : " + (end - begin) / 1000 + " s");}

以上也可以改成这样

 String sql = "insert into rp_cre_data_mobile_track_ctcc(mobile_id,mmd5,create_time,mark,test) values(?,?,?,?,?)";//预编译sqlpstm = conn.prepareStatement(sql);//开始总计时long bTime1 = System.currentTimeMillis();//循环500次，每次十万数据，一共5000万for (int i = 0; i < 500; i++) {//开启分段计时，计1W数据耗时long bTime = System.currentTimeMillis()/ 1000L;//开始循环while (begin < end) {//赋值pstm.setLong(1, (i+1)*begin);pstm.setString(2, Utils.MD5((i+1)*begin + "#6s3k"));pstm.setLong(3, bTime);pstm.setInt(4, 0);pstm.setInt(5, 1);//添加到同一个批处理中pstm.addBatch();begin++;}//执行批处理pstm.executeBatch();
//                //提交事务conn.commit();//边界值自增10Wend += 100000;//关闭分段计时long eTime = System.currentTimeMillis()/ 1000L;//输出System.out.println("成功插入第" + (i + 1) * 10 + "W条数据耗时：" + (eTime - bTime));}