0
0

MySQL十秒插入百万条数据

mysql数据库准备

private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    //封装与数据库建立连接的类
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    //封装异常类
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

方式一：普通插入

package com.wt;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author wt
 * @Date 2022/11/14 21:17
 * @PackageName:com.wt
 * @ClassName: TestAddBatch01
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch01 {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);//填充sql语句种得占位符
                ps.execute();//执行sql语句
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");

    }

}

用时：62分钟多

方式二：使用批处理插入

package com.wt;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
//            connection.setAutoCommit(false);//取消自动提交
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();

                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
//            connection.commit();//所有语句都执行完毕后才手动提交sql语句

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");

    }

}

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

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

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

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

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

package com.wt;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();

                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");

    }

}

用时：【10秒左右】

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

注意

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

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

package com.wt;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author wt
 * @Date 2022/11/14 20:25
 * @PackageName:com.wt.util
 * @ClassName: TestAddBatch
 * @Description: TODO
 * @Version 1.0
 */
public class TestAddBatch {
    private String Driver = "com.mysql.cj.jdbc.Driver";
    private String url ="jdbc:mysql://localhost:3306/mp?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        Class.forName(Driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void erro(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void ccc(){
        long start = System.currentTimeMillis();
        String sql = "insert into a(id, name) VALUES (?,null)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            connection.setAutoCommit(false);//取消自动提交
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();

                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            connection.commit();//所有语句都执行完毕后才手动提交sql语句

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            erro();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");

    }

}

用时：【9秒左右】

总结：

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

2.其他的就正常使用PreparedStatement ps;的以下三个方法即可

  *      ps.addBatch();      将sql语句打包到一个容器中
  *      ps.executeBatch();  将容器中的sql语句提交
  *      ps.clearBatch();    清空容器，为下一次打包做准备
标签： mysql 数据库 java
本文转载自: https://blog.csdn.net/weixin_56028042/article/details/127855696
版权归原作者 子非鱼呀 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL十秒插入百万条数据”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

使用 scp 指令互传（一种在不同主机之间进行文件传输的安全手段，基于SSH协议）

【数据结构与算法】合并链表、链表分割、链表回文结构

IntelliJ IDEA 2024.3（Ultimate Edition）免费化教学

毕业设计：基于python商品数据采集分析可视化系统 淘宝数据采集 大数据 大屏可视化（附源码+论文）

Java通过calcite实时读取kafka中的数据

【前端】CSS格式化上下文详解

Web 攻防：PHP特性漏洞 - MD5值比较绕过

文章导航