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';
推荐阅读
-
Mysql数据库从5.6.28版本升到8.0.11版本部署项目时遇到的问题及解决方法
-
mysql导入大批量数据出现MySQL server has gone away的解决方法
-
MySql添加新用户及为用户创建数据库和给用户分配权限方法介绍
-
mysql导入大批量数据出现MySQL server has gone away的解决方法
-
weblogic服务建立数据源连接测试更新mysql驱动包的问题及解决方法
-
MySQL数据导入导出方法与工具介绍_PHP教程
-
解决大批量数据插入mysql问题:使用mysqlimport工具从文件中导入
-
MySQL数据导入导出方法与工具介绍
-
MySQL:数据批量导入方法及相关问题介绍
-
MySQL数据导入导出方法与工具介绍