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

蔚落 2024-02-05 19:20 79阅读 0赞

#### mysql百万级数据高效插入和更新 ####

*  前言
 *   *  1.表结构
     *  2.新增
     *  3.更新
     *  4.数据方法

--------------------

## 前言 ##

`直入主题：`

--------------------

### 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();
        }

![在这里插入图片描述][41d66f142202481d9e8462bb51763897.png]

### 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();
        }

![在这里插入图片描述][4a09d42dec444a4a9e7c49c7044d8bc0.png]

### 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;
        }

--------------------

[41d66f142202481d9e8462bb51763897.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/85030179f9ff4fff98f668f3d798455d.png
[4a09d42dec444a4a9e7c49c7044d8bc0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c2cf11e512ec4bb3aa1a21ca1fbcc25b.png