欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-11 13:53:57
...

目录

一、批量数据导入方法

二、常见错误的说明


一、批量数据导入方法

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';

 

相关标签: 数据批量导入