【求优化】MySQL批量安插5W条左右数据

【求优化】mysql批量插入5W条左右数据

java code

 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(); } } } 
欢迎大家阅读《【求优化】MySQL批量安插5W条左右数据》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权 
转载请注明原文链接：【求优化】MySQL批量安插5W条左右数据 
 
 喜欢 (0)赏
[搞代码]
分享 (0)