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
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);
共有 0 条评论