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

目录

一、批量数据导入方法

二、常见错误的说明

---

一、批量数据导入方法

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

mysqlimport详细说明：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

二、常见错误的说明

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
4、

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

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

mysql> use mysql;
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | 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';