mysql--load data infile导入文本数据

mysql LOAD DATA INFILE使用与详解

基本语法：

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]
复制

根据官方的资料和网上搜集的资料,解释如下
1、LOW_PRIORITY
如果你指定关键词LOW_PRIORITY,这个只对那些只采用了表级别锁（如MYISAM)的引擎有影响，比如InnoDB使用的是行锁，不受这个影响，具体的来说，使用了LOW_PRIORITY，则本操作会在其它线程完成之后再操作。

2、CONCURRENT
CONCURRENT会和其它线程同时进行，这个对性能是有一些影响。

3、LOCAL
这是个非常重要的关键字，指明了文件的位置，简单的说，如果指定了local，则表示文件位于客户端，如果没有，则表示文件在Server端。同时，这个关键字的使用还会影响到load data命令对于错误数据的处理方式。如果指定了local，则当某条数据处理有误时，系统把这个错误记录为一个warning，不会影响下一条数据的处理，因为涉及到数据传输。而如果没有指定local，则默认情况下，遇到错误后不会继续执行。如果我们是在客户端执行load data命令，一定记得加上local参数。

4、REPLACE | IGNORE
如果指定了,那么当前的数据跟表中的数据有惟一性冲突的时候，采用什么样的方式，是替换已有还是忽略当前。特别需要说明的是，当这两种方式都未指定时，如果数据来自于客户端，则重复的数据会忽略，如果来源于服务端，则命令将终止执行。

5、CHARACTER SET
指定编码集，如果文件的编码跟数据库的编码不一致，可能会出现乱码的问题。所以要注意的是，这里指定的是文件的编码集，不是数据库的编码集

6、字段处理方式

[{FIELDS | COLUMNS} [TERMINATED BY ‘string’][[OPTIONALLY] ENCLOSED BY ‘char’][ESCAPED BY ‘char’]]
[{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
复制

FIELDS和COLUMNS 指定其中一个即可，
TERMINATED 表示字段间的分隔符，
ENCLOSED BY 意思是字段值由什么符号包围，
ESCAPED 表示指定转义字符。

在不指定这个参数的情况下，默认的字段分隔符是\t, 默认字段值无任何值包围，默认转义字符为(反斜)

7、 起始符与终止符

[LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]
复制

默认情况下，起始符为空，终止符为\n，对于windows产生的文本文件来说，需要指定换行符为’\r\n’。

8、IGNORE number LINES
忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么要忽略的放在，这儿的值设置为１。
注意的是这里是行的数量，而不是行号。

9、[(col_name_or_user_var,…)]
有的时候我们不需要给所有的字段都填充值，这个时候就可以指定列名，以()将列名括起来，注意这里也可以是用户自定义的用户表达式。

案例

创建数据库

mysql> create database test;
Query OK, 1 row affected (0.02 sec)
复制

创建表

mysql> CREATE TABLE tb_emp1
    ->      (
    ->      name VARCHAR(25),
    ->      age INT(10),
    ->      description VARCHAR(50)
    ->      );
复制

load data infile

mysql> load data infile '/u01/tb_emp1.txt' into table tb_emp1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' lines terminated BY '\n';
复制

报错 ERROR 1290

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
复制

出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。
首先使用下面的命令

show variables like '%secure%'; 
复制

查看数据库的存储路径。如果查出的secure_file_priv是null的时候就证明在my.cnf文件里面没有配置写出路径。

mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | NULL  |
+--------------------------+-------+
复制

secure_file_priv 参数是一个只读参数,需要修改my.cnf文件
[mysqld] 代码下增加
secure_file_priv=/tmp
再重启 mysql就能顺利导入

编辑脚本导入

#!/bin/bash
STNAME="192.168.56.99"
USERNAME="***"
PASSWORD="***"
DBNAME="test"
PORT="3310"
loadDataFile="/u01/tb_emp1.txt"    #导入的文件路径

#导入sql语句(忽略第一行 列名)
loadCommand="load data local infile '$loadDataFile' into table tb_emp1 FIELDS TERMINATED BY ',' lines terminated BY '\n';"

#连接数据库
/u01/mysql-8.0.28-el7-x86_64/bin/mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} --local-infile -e "$loadCommand"
exit
复制

报错 ERROR 3948

[root@liping u01]# ./imp_data.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides
复制

原因MySQL默认关闭了本地数据加载功能。
查看系统参数

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
复制

服务端
导入时报错需开启本地数据加载功能

set global local_infile=ON;
复制

客户端
连接服务时指定开启或关闭本地数据加载功能，如果关闭则需要通过 --load-data-local-dir 指定本地数据加载目录
当然你直接设置 local-infile 为 1 ，这样就不用设置文件夹限制了

如果启用了数据加载，则默认情况下在 MySQL 客户端库中或通过指定 --local-infile[=1]，将忽略 --load-data-local-dir 选项。

如果禁用了数据加载，则默认情况下在 MySQL 客户端库中或通过指定 --local-infile=0，则应用 --load-data-local-dir 选项

报错 ERROR 2068

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2068 (HY000) at line 1: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
复制

当出现报错ERROR 3948 (42000)后将local_infile=ON之后又出现了Error2068

解决方法是需要以mysql -u 用户名 -p --local-infile的命令登陆，如：

mysql -u root -p --local-infile
复制

改进后的shell脚本：

#!/bin/bash
HOSTNAME="192.168.56.99"
USERNAME="***"
PASSWORD="***"
DBNAME="test"
PORT="3310"
loadDataFile="/u01/tb_emp1.txt"

loadCommand="load data local infile '$loadDataFile' into table tb_emp1 FIELDS TERMINATED BY ',' lines terminated BY '\n';"

mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} --local-infile -e "$loadCommand"
exit
复制