MySQL：数据批量导入方法及相关问题介绍

墨蓝 2021-12-14 18:01 226阅读 0赞

**目录**

一、批量数据导入方法

二、常见错误的说明

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

### 一、批量数据导入方法 ###

MySQL做批量数据导入时一般有如下三种方式：

1、使用MySQL提供的connector，自己写代码导入。比如使用JDBC、ODBC、C++等。

2、使用mysqlimport

3、使用"LOAD DATA LOCAL INFILE

下面分别举例说明一下：

1、使用MySQL提供的connector，本文以JAVA代码为例来说明：

private void doTest(List<Map<String, String>> userList, List<Map<String, String>> companyList){
    	try (Connection conn = dataSource.getConnection();) {
    		try (Statement st = conn.createStatement();) {
    			conn.setAutoCommit(false);
    			String insertSql = "";
    			
    			insertSql = "insert user values " + createSQLSegment(userList);
    			st.addBatch(insertSql);
    			
    			insertSql = "insert company values " + createSQLSegment(companyList);
    			st.addBatch(insertSql);
    			
    			st.executeBatch();
    			conn.commit();
    		} catch (Exception e) {
    			conn.rollback();
    			e.printStackTrace();
    		}
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
    }
    private String createSQLSegment(List<Map<String, String>> rowMapList) {
    	String insertSql = "";
    	int j = 0;
    	for (Map<String, String> rowMap : rowMapList) {
    		if (j != 0) {
    			insertSql += ",";
    		}
    		int i = 0;
    		insertSql += "(";
    		for (Entry<String, String> entry : rowMap.entrySet()) {
    			if (i != 0) {
    				insertSql += ",";
    			}
    			insertSql += "'" + entry.getValue() + "'";
    			i++;
    		}
    		insertSql += ")";
    		j++;
    	}
    	insertSql += ";";
    	return insertSql;
    }

由于MySQL支持将多个insert拼接成一条insert，可以使用此方法提高insert效率，上述代码中createSQLSegment就是做此事的

> insert into user values (XX1, YY1, ZZ1), (XX2, YY2, ZZ2), (XX3, YY3, ZZ3)......

2、使用mysqlimport

> mysqlimport -h 127.0.0.1 -u testuser -p12345678 --local=0 --silent --fields-terminated-by="\\t" --lines-terminated-by="\\r\\n" --default-character-set="UTF8"  testdb C:/Temp/t\_company

这里需要说明的是，由于此命令不能指定表名，命令默认将去掉后缀的文件名作为表名，所以去掉后缀的文件名必须跟表名相同。另外，mysqlimport内部实际使用的就是LOAD DATA LOCAL INFILE，它只是相对LOAD DATA LOCAL INFILE使用更加方便而已。mysqlimport的源代码可以在如下地址找到：

[https://github.com/mysql/mysql-server/blob/8.0/client/mysqlimport.cc][https_github.com_mysql_mysql-server_blob_8.0_client_mysqlimport.cc]

mysqlimport详细说明：[https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html][https_dev.mysql.com_doc_refman_8.0_en_mysqlimport.html]

3、使用LOAD DATA LOCAL INFILE

> mysql --local-infile=1 -h 127.0.0.1 -u testuser -p12345678 -D testdb -e "LOAD DATA LOCAL INFILE 'C:/Temp/t\_company' INTO TABLE  t\_company  FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\r\\n'"

LOAD DATA详细说明：[https://dev.mysql.com/doc/refman/8.0/en/load-data.html][https_dev.mysql.com_doc_refman_8.0_en_load-data.html]

### 二、常见错误的说明 ###

1、

> Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table:

先查询secure\_file\_priv变量的值

> show variables like 'secure\_file\_priv';  
> secure\_file\_priv            C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\

表示只能从C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\目录导入文件，而我们需要放开让所有目录都可以导入文件，所以需要将此值设置为空。找到并修改my.ini或my.cnf文件：

> secure-file-priv=

2、

> mysqlimport: Character set '"UTF8"' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file  
> mysqlimport: Error: 2019 Can't initialize character set "UTF8" (path: /usr/share/mysql/charsets/)

修改my.ini或my.cnf文件：

> default-character-set=utf-8

3、

> ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

查询local\_infile变量是否已打开

> SHOW GLOBAL VARIABLES LIKE 'local\_infile';  
> local\_infile    OFF

如果处于关闭状态，则在my.cnf中的\[mysqld\]下面添加

> local-infile  
> 或  
> local-infile=1

重启MySQL，查询local\_infile变量

> SHOW GLOBAL VARIABLES LIKE 'local\_infile';  
> local\_infile    ON

参考文档：[MySQL: Enable LOAD DATA LOCAL INFILE][MySQL_ Enable LOAD DATA LOCAL INFILE]  
4、

> mysqlimport: Error: 1130 Host '192.168.1.11' is not allowed to connect to this MySQL server

查询user表可以看到只允许localhost访问，需要修改成允许所有人访问

> update user set host = '%' where user = 'root';  
> flush privileges;

5、

> mysqlimport: Error: 13, Can't get stat of '/home/test/t\_company' (OS errno 2 - No such file or directory), when using table: t\_company

这个可能是apparmor服务限制了mysqld可读写的目录造成的，需要添加导入文件所在目录。

> sudo vi /etc/apparmor.d/usr.sbin.mysqld

然后重启

> sudo /etc/init.d/apparmor reload

另外，selinux服务也有这个问题。参考文档：

https://www.linuxidc.com/Linux/2012-02/55533.htm  
http://www.111cn.net/database/mysql/46839.htm

6、

> mysqlimport: Error: 2059 Authentication plugin 'caching\_sha2\_password' cannot be loaded: /usr/lib/x86\_64-linux-gnu/mariadb18/plugin/caching\_sha2\_password.so: cannot open shared object file: No such file or directory

MySQL8中使用了新的认证插件caching\_sha2\_password，如果不想用，可以换成旧的mysql\_native\_password，可以使用如下SQL试试

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql\_native\_password BY '12345678';  
> ALTER USER 'testuser'@'%' IDENTIFIED WITH mysql\_native\_password BY '12345678';

[https_github.com_mysql_mysql-server_blob_8.0_client_mysqlimport.cc]: https://github.com/mysql/mysql-server/blob/8.0/client/mysqlimport.cc
[https_dev.mysql.com_doc_refman_8.0_en_mysqlimport.html]: https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html
[https_dev.mysql.com_doc_refman_8.0_en_load-data.html]: https://dev.mysql.com/doc/refman/8.0/en/load-data.html
[MySQL_ Enable LOAD DATA LOCAL INFILE]: https://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile