【求优化】MySQL批量插入5W条左右数据
Connection conn = null; PreparedStatement ps = null; try { conn = DBUtils.getConnection(); conn.setAutoCommit(false); long s = System.currentTimeMillis(); StringBuffer sb = new StringBuffer(); sb.append(" INSERT INTO TEST1 "); sb.append(" ( "); sb.append(" A , B , C , "); sb.append(" D , E , F , "); sb.append(" G , H , I "); sb.append(" J , K , K "); sb.append(" M , N , O "); sb.append(" P , Q , R "); sb.append(" S , T , U "); sb.append(" V , W , X "); sb.append(" Y , Z "); sb.append(" ) "); sb.append(" VALUES"); sb.append(" (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? (SELECT NAME FROM TEST2 WHERE ID =? ) "); ps = conn.prepareStatement(sb.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); int count = 0 ; for (int i=0,n=50000;i<n;i++) { ps.setString(1, "value1"); ps.setString(2, "value2"); ps.setString(3, "value3"); ps.setString(4, "value4"); ps.setString(5, "value5"); ps.setString(6, "value6"); ps.setString(7, "value7"); ps.setString(8, "value8"); ps.setString(9, "value9"); ps.setString(10, "value10"); ps.setString(11, "value11"); ps.setString(12, "value12"); ps.setString(13, "value13"); ps.setString(14, "value14"); ps.setString(15, "value15"); ps.setString(16, "value16"); ps.setString(17, "value17"); ps.setString(18, "value18"); ps.setString(19, "value19"); ps.setString(20, "value20"); ps.setString(21, "value21"); ps.setString(22, "value22"); ps.setString(23, "value23"); ps.setString(24, "value24"); ps.setString(25, "value25"); ps.setInt(26, i); ps.addBatch(); count ++ ; if(count % 10000 == 0){ //1W条一提交 ps.executeBatch(); conn.commit(); count = 0; } } ps.executeBatch(); conn.commit(); ps.clearBatch(); long e = System.currentTimeMillis(); System.out.println("共用时：" + (e - s)/1000.00000000 + "秒"); } catch (Exception e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }