mysql local infile快速导入数据开启使用

mysql local infile的作用

启用加载数据本地INFILE,快速导入数据库

怎么开启使用

参考

https://blog.csdn.net/abcde158308/article/details/60757630

https://blog.csdn.net/weixin_36250220/article/details/113640597

在数据库中输入查看本地是否已经开启

local_infile=off(关闭)

local_infile=on(开启)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+ 
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

我们可以借助ON或布尔值true或数字值1启用本地文件。以下是启用本地文件的语法

mysql> SET GLOBAL local_infile = 'ON'; 这种写法MySQL8不支持)

mysql> SET GLOBAL local_infile = 1;

mysql> SET GLOBAL local_infile = true;

LOAD DATA INFILE 安全和权限问题

在本机没有权限的情况下,导入是会报错的,没有权限读取

服务器读取位于服务器主机上的文本文件,因此必须满足以下安全要求,必须拥有FILE 特权,参考官方文档

https://dev.mysql.com/doc/refman/5.6/en/load-data.html#load-data-local

参考设置 secure_file_priv 路径

https://blog.csdn.net/qq_28921653/article/details/54174341

可以看到secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

当secure_file_priv的值为null ,表示限制mysqld 不允许导入|导出

当secure_file_priv的值为/tmp/ ,表示限制mysqld 的导入|导出只能发生在/tmp/目录下

当secure_file_priv的值没有具体值时,表示不对mysqld 的导入|导出做限制

查看当前secure_file_priv 值和设置权限

mysql>show global variables like '%secure%';

所以再mysql.cnf中的[mysqld]加入secure_file_priv =

my.ini目录找到这个也可以去添加
再重启mysql服务
然后再查一下此时参数的值

 

mysql快速导入优化,大文件合适

my.ini的配置文件

减少数据库操作

关闭二进制日志:去掉log-bin配置选项。

减小事务提交后日志刷新频率:innodb_flush_log_at_trx_commit=0每秒1次

关闭双写缓冲区(doublewrite)提高性能

参考:https://www.cnblogs.com/JockChou/p/4644732.html

查询是否开启


mysql> show variables like '%double%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_doublewrite            | ON    |

 

在my.ini中添加,就可以关闭

InnoDB_doublewrite=0即可关闭

 

如果cpu够多修改默认cpu数量

参考:https://www.cnblogs.com/mydriverc/p/8301689.html

读取和写入都是默认4个cpu,,Windows系统上可以调整

 

1. 参数innodb_write_io_threads  写线程 默认四个,负责数据块的写入
2. 参数innodb_read_io_threads 读线程  默认四个,负责数据块的读取

上面两个参数高并发下,可以设置为8.

show variables like '%write_io%'

show variables like '%read_io%'

在my.ini中添加,就可以关闭

innodb_read_io_threads = 8
innodb_write_io_threads = 8

 

减少数据库操作关闭二进制日志

 

 

local infile导入语句

官方文档参考:https://dev.mysql.com/doc/refman/5.6/en/load-data.html

https://blog.csdn.net/的参考

LOAD DATA INFILE 文件路径 INTO TABLE 表名

character set utf8mb4(gbk,utf8) 设置编码格式,防止中文乱码(也可不设置

FIELDS TERMINATED BY ',' 指定导入文件的字段的分割符是什么格式

OPTIONALLY ENCLOSED BY ''表文本限定符,每个字段被什么字符包围,默认为空,有些是"

ESCAPED BY '\\'转义符,默认是\

LINES TERMINATED BY '\r\n'(name,password,email); 字段列表忽略自增索引每条数据以 \n 分割

(表名)

 

\t 是制表符,就是按下键盘的 tab键,主要用于缩进
\n 是换行附,对应键盘的 回车键 centos里面  Windows系统里面,每行结尾是“<回车><换行>”,即“ \r\n

备注:如果您已经在Windows系统中生成了文本文件,您可能必须使用LINES TERMINATED BY ‘\r\n'来正确地读取文件,因为Windows程序通常使用两个字符作为一个行终止符。

在centos下是 LINES TERMINATED BY '\n'

路径注意复制是:C:\wwwroot\123.txt 需要更改成C:/wwwroot/123.txt

参考导入语句输入:

LOAD DATA INFILE 'C:/wwwroot/1.txt' INTO TABLE  sgk_csdn
FIELDS TERMINATED BY '----'
	OPTIONALLY ENCLOSED BY ''
	ESCAPED BY '' 
LINES TERMINATED BY '\r\n' 
(name,sfz);

 

 

版权声明:
作者:wanghaha
链接:http://www.aiii.vip/566.html
来源:我的生活分享
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>