Mysql 使用 Load Data 实现数据的快速导入

在数据库管理中，批量导入数据是一个常见的任务。MySQL提供了一种非常高效的方式来导入数据，即使用LOAD DATA命令。这个命令可以快速地将数据从文本文件加载到数据库表中，特别适合处理大量数据的场景，本地和远程目录都可以实现导入数据。

客户端和Mysql服务器在同一服务器

在使用之前先使用 show global variables like '%secure_file_priv%'; 查看一下你的 Load Data 导出导入目录是什么，有以下几种情况

• secure_file_priv 为 NULL 时，表示限制 mysql 不允许导入导出

• secure_file_priv 为特定目录时，表示只能在此目录导入导出

• secure_file_priv 没有任何值的情况下，表示不限制任何目录导入导出

我使用的是 docker 创建的 Mysql 8.0 使用上面的命令返回了以下的内容

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

代表我需要把要导入的文件放入 /var/lib/mysql-files/ 目录下才可以。如果你需要修改为任何目录或者修改为其他目录则自己去修改 my.cnf 或者 my.ini 找到 secure_file_priv 修改为空

secure_file_priv=''

登录 Mysql 之后执行下面的语句

-- use database;
-- 数据示例：6632bd32206a4e7dba45309215da9ab9,商品名称：测试商品,1287.30,产品描述：是一款具有强大功能的高品质产品。
-- load data 这里不要写成 Load DATA LOCAL 不然就不能用服务的内容了 
load data infile '要读取txt的文件地址'
replace into table 表名
character set utf8mb4
fields terminated by ','
lines terminated by '\n'

客户端和Mysql服务器不在同一服务器

在使用之前先使用 show VARIABLES like 'local%' 查询是否支持远程执行脚本

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.02 sec)

可以看见我这台服务器是可以远程执行的，如果返回结果是 OFF 就设置一下这个值的状态为 ON 即可, set global local_infile = 1; 。

使用下面的语句就能导入数据了。

-- use database
-- 数据示例：6632bd32206a4e7dba45309215da9ab9,商品名称：测试商品,1287.30,产品描述：是一款具有强大功能的高品质产品。
Load DATA LOCAL 
INFILE '要导入数据的文件位置' REPLACE into TABLE 表名
character set utf8mb4
-- 使用 "," 作为分隔符
fields terminated by ','
-- 每一行为一条数据
lines terminated by '\n'
-- 如果你文件的第一行是标题行，使用这个选项来忽略它
-- IGNORE 1 ROWS; 

用JAVA实现批量导入100个文件每个文件10000条数据

package com.runbrick;

import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

public class DataProcessing {

    /**
     * 创建 1000万条数据，分别放到 100 文件中。
     *
     * @throws IOException
     */
    public void createDataFile() throws IOException {
        // 总商品数
        Integer totalProduct = 10000000;
        // 每个文件生成数量
        Integer productsPerFile = 100000;
        // 文件数量
        Integer fileNum = totalProduct / productsPerFile;

        for (int i = 1; i <= fileNum; i++) {
            // 创建文件
            String fileName = "prodect_info_" + i + ".txt";
            Path path = Paths.get(fileName);
            StringBuilder stringBuilder = new StringBuilder();
            for (int j = 1; j <= productsPerFile; j++) {
                stringBuilder.append(randomGoods(j));
            }
            Files.write(path, stringBuilder.toString().getBytes(), Files.exists(path) ? StandardOpenOption.APPEND : StandardOpenOption.CREATE);
        }
    }

    /**
     * 导入1000W数据到 mysql 中
     *
     * @throws IOException
     * @throws SQLException
     */
    public void writeFileData() throws IOException, SQLException {
        // &allowLoadLocalInfile=true" 这个参数是为了开启 mysql 的 load local infile 功能,如果不加会出现
        // java.sql.SQLSyntaxErrorException: The used command is not allowed with this MySQL version 错误
        String JDBC_URL = "jdbc:mysql://localhost:23306/test?useSSL=false&characterEncoding=utf8&allowLoadLocalInfile=true";
        String JDBC_USERNAME = "root";
        String JDBC_PASSWORD = "123qwe.";

        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD)) {

            try (Statement statement = conn.createStatement()) {
                for (int i = 1; i <= 100; i++) {
                    String fileName = "prodect_info_" + i + ".txt";
                    Path path = Paths.get(fileName);
                    String s = path.toAbsolutePath().toString().replace("\\", "\\\\");
                    // 将文件内容直接导入 mysql 中
                    String sql = String.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE goods character set utf8mb4 FIELDS TERMINATED BY ',' LINES TERMINATED BY '%n';", s);
                    statement.execute(sql);
                }
            }
        }
    }


    /**
     * 商城随机商品数据
     *
     * @return
     */
    public static String randomGoods(Integer number) {
        String goodsId = UUID.randomUUID().toString().replace("-", "");
        String goodsName = "商品" + number;
        BigDecimal goodsPrice = BigDecimal.valueOf(Math.random() * 1000);
        String goodsDesc = "商品" + number + "商品非常好";

        return String.format("%s,%s,%.2f,%s%n", goodsId, goodsName, goodsPrice, goodsDesc);
    }
}