java - 通过 JDBC 执行时 mysql LOAD DATA LOCAL INFILE 语法错误

Question

我正在尝试使用以下示例查询从本地 csv 文件加载内容:

SET GLOBAL local_infile=1; LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (`type_o`,`n_order`,`n_return`);

从 MySQL 执行查询工作正常，但是，当我尝试从 JDBC 执行相同的查询时，出现语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` ' at line 1

这是我的代码:

    Connection conn = null;

    String url = "jdbc:mysql://" + mysqlHost + ":" + mysqlPort + "/" + mysqlDatabase + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

    try {
        conn = DriverManager.getConnection(url, mysqlUser, mysqlPassword);

        Statement stmt = conn.createStatement();


        String sql = "SET GLOBAL local_infile=1; LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (`type_o`,`n_order`,`n_return`);";
        System.out.println(sql);
        stmt.execute(sql);

    } catch(Exception e) {
       System.out.println(e.getMessage());
    }

这是我正在使用的驱动程序:

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.20</version>
    </dependency>

我错过了什么？

Answer 1

为了将来的引用，我还必须添加:

&allowLoadLocalInfile=true

连接字符串以避免错误:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side