• MySQL十秒插入百万条数据

    mysql数据库准备

    1. private String Driver = "com.mysql.cj.jdbc.Driver";
    2.     private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    3.     private String user = "root";
    4.     private String password = "root";
    5.     Connection connection = null;
    6.     PreparedStatement ps = null;
    7.     ResultSet rs = null;
    8.     //封装与数据库建立连接的类
    9.     public void coon() throws Exception{
    10.         Class.forName(Driver);
    11.         connection = DriverManager.getConnection(url,user,password);
    12.     }
    13.     //封装异常类
    14.     public void erro(){
    15.         try {
    16.             if (rs!=null){
    17.                 rs.close();
    18.             }
    19.             if (ps!=null){
    20.                 ps.close();
    21.             }
    22.             if (connection!=null){
    23.                 connection.close();
    24.             }
    25.         } catch (Exception e) {
    26.             e.printStackTrace();
    27.         }
    28.     }

    方式一：普通插入

    1. package com.wt;
    2.  
    3. import org.junit.Test;
    4.  
    5. import java.sql.Connection;
    6. import java.sql.DriverManager;
    7. import java.sql.PreparedStatement;
    8. import java.sql.ResultSet;
    9.  
    10. /**
    11.  * @Author wt
    12.  * @Date 2022/11/14 21:17
    13.  * @PackageName:com.wt
    14.  * @ClassName: TestAddBatch01
    15.  * @Description: TODO
    16.  * @Version 1.0
    17.  */
    18. public class TestAddBatch01 {
    19.     private String Driver = "com.mysql.cj.jdbc.Driver";
    20.     private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    21.     private String user = "root";
    22.     private String password = "root";
    23.     Connection connection = null;
    24.     PreparedStatement ps = null;
    25.     ResultSet rs = null;
    26.     public void coon() throws Exception{
    27.         Class.forName(Driver);
    28.         connection = DriverManager.getConnection(url,user,password);
    29.     }
    30.     public void erro(){
    31.         try {
    32.             if (rs!=null){
    33.                 rs.close();
    34.             }
    35.             if (ps!=null){
    36.                 ps.close();
    37.             }
    38.             if (connection!=null){
    39.                 connection.close();
    40.             }
    41.         } catch (Exception e) {
    42.             e.printStackTrace();
    43.         }
    44.     }
    45.  
    46.     @Test
    47.     public void ccc(){
    48.         long start = System.currentTimeMillis();
    49.         String sql = "insert into a(id, name) VALUES (?,null)";
    50.         try {
    51.             coon();
    52.             ps = connection.prepareStatement(sql);
    53.             for (int i = 1; i <= 1000000; i++) {
    54.                 ps.setObject(1, i);//填充sql语句种得占位符
    55.                 ps.execute();//执行sql语句
    56.             }
    57.         } catch (Exception e) {
    58.             e.printStackTrace();
    59.         } finally {
    60.             erro();
    61.         }
    62.         System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    63.  
    64.     }
    65.  
    66. }

    用时：62分钟多 

    方式二：使用批处理插入

    1. package com.wt;
    2.  
    3. import org.junit.Test;
    4.  
    5. import java.sql.Connection;
    6. import java.sql.DriverManager;
    7. import java.sql.PreparedStatement;
    8. import java.sql.ResultSet;
    9.  
    10. /**
    11.  * @Author wt
    12.  * @Date 2022/11/14 20:25
    13.  * @PackageName:com.wt.util
    14.  * @ClassName: TestAddBatch
    15.  * @Description: TODO
    16.  * @Version 1.0
    17.  */
    18. public class TestAddBatch {
    19.     private String Driver = "com.mysql.cj.jdbc.Driver";
    20.     private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    21.     private String user = "root";
    22.     private String password = "root";
    23.     Connection connection = null;
    24.     PreparedStatement ps = null;
    25.     ResultSet rs = null;
    26.     public void coon() throws Exception{
    27.         Class.forName(Driver);
    28.         connection = DriverManager.getConnection(url,user,password);
    29.     }
    30.     public void erro(){
    31.         try {
    32.             if (rs!=null){
    33.                 rs.close();
    34.             }
    35.             if (ps!=null){
    36.                 ps.close();
    37.             }
    38.             if (connection!=null){
    39.                 connection.close();
    40.             }
    41.         } catch (Exception e) {
    42.             e.printStackTrace();
    43.         }
    44.     }
    45.  
    46.     @Test
    47.     public void ccc(){
    48.         long start = System.currentTimeMillis();
    49.         String sql = "insert into a(id, name) VALUES (?,null)";
    50.         try {
    51.             coon();
    52.             ps = connection.prepareStatement(sql);
    53. //            connection.setAutoCommit(false);//取消自动提交
    54.             for (int i = 1; i <= 1000000; i++) {
    55.                 ps.setObject(1, i);
    56.                 ps.addBatch();
    57.  
    58.                 if (i % 1000 == 0) {
    59.                     ps.executeBatch();
    60.                     ps.clearBatch();
    61.                 }
    62.             }
    63.             ps.executeBatch();
    64.             ps.clearBatch();
    65. //            connection.commit();//所有语句都执行完毕后才手动提交sql语句
    66.  
    67.         } catch (Exception e) {
    68.             e.printStackTrace();
    69.         } finally {
    70.             erro();
    71.         }
    72.         System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    73.  
    74.     }
    75.  
    76.  
    77. }
    78.  
    79.

     方式一、二总结：到此可以看出其实其处理程序及批处理是没有起作用的，为此我们使用方式三

    方式三：通过连接配置url设置【&rewriteBatchedStatements=true】（设置重写批处理语句）

    url地址后注意添加【&rewriteBatchedStatements=true】

    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";

    方法三较于方法二的改变是只是url地址上的改变，其它没有任何修改 

    1. package com.wt;
    2.  
    3. import org.junit.Test;
    4.  
    5. import java.sql.Connection;
    6. import java.sql.DriverManager;
    7. import java.sql.PreparedStatement;
    8. import java.sql.ResultSet;
    9.  
    10. /**
    11.  * @Author wt
    12.  * @Date 2022/11/14 20:25
    13.  * @PackageName:com.wt.util
    14.  * @ClassName: TestAddBatch
    15.  * @Description: TODO
    16.  * @Version 1.0
    17.  */
    18. public class TestAddBatch {
    19.     private String Driver = "com.mysql.cj.jdbc.Driver";
    20.     private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    21.     private String user = "root";
    22.     private String password = "root";
    23.     Connection connection = null;
    24.     PreparedStatement ps = null;
    25.     ResultSet rs = null;
    26.     public void coon() throws Exception{
    27.         Class.forName(Driver);
    28.         connection = DriverManager.getConnection(url,user,password);
    29.     }
    30.     public void erro(){
    31.         try {
    32.             if (rs!=null){
    33.                 rs.close();
    34.             }
    35.             if (ps!=null){
    36.                 ps.close();
    37.             }
    38.             if (connection!=null){
    39.                 connection.close();
    40.             }
    41.         } catch (Exception e) {
    42.             e.printStackTrace();
    43.         }
    44.     }
    45.  
    46.     @Test
    47.     public void ccc(){
    48.         long start = System.currentTimeMillis();
    49.         String sql = "insert into a(id, name) VALUES (?,null)";
    50.         try {
    51.             coon();
    52.             ps = connection.prepareStatement(sql);
    53.             for (int i = 1; i <= 1000000; i++) {
    54.                 ps.setObject(1, i);
    55.                 ps.addBatch();
    56.  
    57.                 if (i % 1000 == 0) {
    58.                     ps.executeBatch();
    59.                     ps.clearBatch();
    60.                 }
    61.             }
    62.             ps.executeBatch();
    63.             ps.clearBatch();
    64.  
    65.         } catch (Exception e) {
    66.             e.printStackTrace();
    67.         } finally {
    68.             erro();
    69.         }
    70.         System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    71.  
    72.     }
    73.  
    74.  
    75. }
    76.  
    77.

    用时：【10秒左右】

     

     

     

    到此批处理语句才正是生效

    注意

    数据库连接的url设置了【&rewriteBatchedStatements=true】时，java代码种的sql语句不能有分号【;】号，否则批处理语句打包就会出现错误，导致后面的sql语句提交出现【BatchUpdateException】异常

    方式四：通过数据库连接取消自动提交，手动提交数据

    1. package com.wt;
    2.  
    3. import org.junit.Test;
    4.  
    5. import java.sql.Connection;
    6. import java.sql.DriverManager;
    7. import java.sql.PreparedStatement;
    8. import java.sql.ResultSet;
    9.  
    10. /**
    11.  * @Author wt
    12.  * @Date 2022/11/14 20:25
    13.  * @PackageName:com.wt.util
    14.  * @ClassName: TestAddBatch
    15.  * @Description: TODO
    16.  * @Version 1.0
    17.  */
    18. public class TestAddBatch {
    19.     private String Driver = "com.mysql.cj.jdbc.Driver";
    20.     private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    21.     private String user = "root";
    22.     private String password = "root";
    23.     Connection connection = null;
    24.     PreparedStatement ps = null;
    25.     ResultSet rs = null;
    26.     public void coon() throws Exception{
    27.         Class.forName(Driver);
    28.         connection = DriverManager.getConnection(url,user,password);
    29.     }
    30.     public void erro(){
    31.         try {
    32.             if (rs!=null){
    33.                 rs.close();
    34.             }
    35.             if (ps!=null){
    36.                 ps.close();
    37.             }
    38.             if (connection!=null){
    39.                 connection.close();
    40.             }
    41.         } catch (Exception e) {
    42.             e.printStackTrace();
    43.         }
    44.     }
    45.  
    46.     @Test
    47.     public void ccc(){
    48.         long start = System.currentTimeMillis();
    49.         String sql = "insert into a(id, name) VALUES (?,null)";
    50.         try {
    51.             coon();
    52.             ps = connection.prepareStatement(sql);
    53.             connection.setAutoCommit(false);//取消自动提交
    54.             for (int i = 1; i <= 1000000; i++) {
    55.                 ps.setObject(1, i);
    56.                 ps.addBatch();
    57.  
    58.                 if (i % 1000 == 0) {
    59.                     ps.executeBatch();
    60.                     ps.clearBatch();
    61.                 }
    62.             }
    63.             ps.executeBatch();
    64.             ps.clearBatch();
    65.             connection.commit();//所有语句都执行完毕后才手动提交sql语句
    66.  
    67.         } catch (Exception e) {
    68.             e.printStackTrace();
    69.         } finally {
    70.             erro();
    71.         }
    72.         System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    73.  
    74.     }
    75.  
    76.  
    77. }
    78.  
    79.

     用时：【9秒左右】

    总结：

    1.使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】，以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常，

    2.其他的就正常使用PreparedStatement ps;的以下三个方法即可
         *      ps.addBatch();      将sql语句打包到一个容器中
         *      ps.executeBatch();  将容器中的sql语句提交
         *      ps.clearBatch();    清空容器，为下一次打包做准备

     

  • 相关阅读:
    maven 本地jar打包到镜像仓库
    南美巴西市场最全分析开发攻略，收藏一篇就够了
    QT中怎么设置定时器/周期任务/定时触发任务
    实战
    C++标准输入输出及命名空间
    fastboot 找不到设备
    03-JAVA设计模式-策略模式
    天月德统计
    HALCON联合C#机械手视觉定位——初始化
    JavaScript变量存储机制
  • 原文地址：https://blog.csdn.net/weixin_56028042/article/details/127855696