mysql百万级数据批量插入与更新

---

前言

直入主题：

---

1.表结构

sql如下：

CREATE TABLE aaa (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    first VARCHAR(255),  
    second VARCHAR(255),  
    three VARCHAR(255),  
    four VARCHAR(255),  
    five VARCHAR(255)  
);

2.新增

代码如下：

    @Transactional(rollbackFor = Exception.class)
    public long batchInsert() {
        //初始化1000000条数据
        List<Map<String, String>> data = createList(10000);
        Long startTime = System.currentTimeMillis();
        String addSql = "insert into aaa (first, second, three, four,five) VALUES(?,?,?,?,?)";
        jdbcTemplate.batchUpdate(addSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, data.get(i).get("first"));
                ps.setString(2, data.get(i).get("second"));
                ps.setString(3, data.get(i).get("three"));
                ps.setString(4, data.get(i).get("four"));
                ps.setString(5, data.get(i).get("five"));
            }

            @Override
            public int getBatchSize() {
                return data.size();
            }
        });
        Long endTime = System.currentTimeMillis();
        System.out.println("插入" + data.size() + "条数据用时：" + (endTime - startTime) + "毫秒");
        System.out.println("插入" + data.size() + "条数据用时：" + (endTime - startTime) / 1000 + "秒");
        return data.size();
    }

3.更新

代码如下：

    @Transactional(rollbackFor = Exception.class)
    public long batchInsert2() {
        List<Map<String, String>> data = createList2(10000);
        Long startTime = System.currentTimeMillis();
        String updateSql = "update aaa set first = ?,second=?,three=?,four=?,five=? where id = ?";
        jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, data.get(i).get("first"));
                ps.setString(2, data.get(i).get("second"));
                ps.setString(3, data.get(i).get("three"));
                ps.setString(4, data.get(i).get("four"));
                ps.setString(5, data.get(i).get("five"));
                ps.setInt(6, 1 + i);
            }

            @Override
            public int getBatchSize() {
                return data.size();
            }
        });
        Long endTime = System.currentTimeMillis();
        System.out.println("更新" + data.size() + "条数据用时：" + (endTime - startTime) + "毫秒");
        System.out.println("更新" + data.size() + "条数据用时：" + (endTime - startTime) / 1000 + "秒");
        return data.size();
    }

4.数据方法

代码如下：

       public static List<Map<String, String>> createList(int size) {
        List<Map<String, String>> data = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            Map<String, String> rowData = new HashMap<>();
            rowData.put("first", "Value1-" + i); // 假设每个值都是一个递增的字符串
            rowData.put("second", "Value2-" + i);
            rowData.put("three", "Value3-" + i);
            rowData.put("four", "Value4-" + i);
            rowData.put("five", "Value5-" + i);
            data.add(rowData);
        }
        return data;
    }

    public static List<Map<String, String>> createList2(int size) {
        List<Map<String, String>> data = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            Map<String, String> rowData = new HashMap<>();
            rowData.put("first", "UpdateValue1-" + i); // 假设每个值都是一个递增的字符串
            rowData.put("second", "UpdateValue2-" + i);
            rowData.put("three", "UpdateValue3-" + i);
            rowData.put("four", "UpdateValue4-" + i);
            rowData.put("five", "UpdateValue5-" + i);
            data.add(rowData);
        }
        return data;
    }

---