MySQL导入文本文件数据（LOAD DATA LOCAL INFILE...）

---

一、导入SQL语法

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}] ...]

LOAD DATA是补充 SELECT … INTO OUTFILE。要将表中的数据写入文件，请使用 SELECT … INTO OUTFILE。要将文件读回表中，请使用 LOAD DATA。两个语句的FIELDS和LINES子句的语法 相同。

更多的相关内容，可以参照官网。

---

二、数据分隔符介绍

fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
terminated by 以什么字符作为分隔符
enclosed by 字段闭合标签
escaped by 转义字符
terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
enclosed by 描述的是字段的括起字符。
escaped by 描述的转义字符。默认的是反斜杠（backslash：\ ）
lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符 (不指定一个lines子句，缺省值默认写的如: lines terminated by’\n’)
[IGNORE number LINES] 忽略特定行数，CSV文件可以忽略掉第一行标题

换行符说明：

（1）Unix系统里，每行结尾只有“<换行>”，即“\n”；
（2）Windows系统里面，每行结尾是“<回车><换行>”，即“\r\n”；
（3）Mac系统里，每行结尾是“<回车>”,即“\r”。
CR（Carriage Return）表示回车
LF（Line Feed）表示换行
Dos和Windows采用回车+换行（CR+LF）表示下一行
而UNIX/Linux采用换行符（LF）表示下一行
苹果机(MAC OS系统)则采用回车符（CR）表示下一行

---

三、示例

1、例：将a.txt数据导入到tab_mytest表

load data local infile 'D:\\a.txt'
into table tab_mytest
fields terminated by '\t' -- 字段分隔符
lines terminated by '\n' -- 行分隔符
ignore 1 lines -- 忽略第一行（一般是忽略字段/标题行）
(id,name); -- 数据库表列（与文件数据列对应）

---

2、例：处理空字段

使用IF(…) 或 NULLIF(…)处理空字段。

load data local infile '/app/a.del' into table brcp_rdifs_index_new 
fields terminated by '' lines terminated by '\n' 
(@cert_no,@cust_name,@credit_money,@guarantee_money,@loan_up_count)
 set 
 cert_no=if(@cert_no is null or @cert_no='',null,@cert_no),
 cust_name=if(@cust_name is null or @cust_name='',null,@cust_name),
 credit_money=if(@credit_money is null or @credit_money='',null,@credit_money),
 guarantee_money=if(@guarantee_money is null or @guarantee_money='',null,@guarantee_money),
 loan_up_count=if(@loan_up_count is null or @loan_up_count='',null,@loan_up_count)

或者

load data local infile '/app/a.del' into table brcp_rdifs_index_new 
fields terminated by '' lines terminated by '\n' 
(cert_no,cust_name,@credit_money,@guarantee_money,@loan_up_count) 
set credit_money=NULLif(@credit_money,''),
guarantee_money=NULLif(@guarantee_money,''),
loan_up_count=NULLif(@loan_up_count,'')

-- 定义sql语句
mysql_sql="load data local infile '/app/a.del' into table brcp_rdifs_index_new 
fields terminated by '' lines terminated by '\n' 
(cert_no,cust_name,@credit_money,@guarantee_money,@loan_up_count) 
set credit_money=NULLif(@credit_money,''),
guarantee_money=NULLif(@guarantee_money,''),
loan_up_count=NULLif(@loan_up_count,'')"
-- 执行sql语句
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} --local-infile=1 -e "${mysql_sql}"

---

四、常见问题

1、问题1：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv

使用命令查看当前值：

show variables like '%secure%';

解决：

（1）修改my.ini，在[mysqld]中加入：

secure-file-priv=''

PS： 表示任意目下都能导入文件到mysql中。

（2）重启mysql服务

---

问题2：

ERROR 1300 (HY000): Invalid utf8 character string: ''

一般是txt文件格式不匹配导致。使用以下命令查看mysql中变量的格式：

show full columns from testtb;

如果显示utf-8，则需要将txt用记事本打开，另存为，选编码格式为utf-8.

---

问题3：

ERROR 1261 (01000): Row 228 doesn’t contain data for all columns

查看当前连接的sql_mode：

show variables like 'sql_mode';

解决方法：

（1）修改my.ini，设置set sql_mode不包含‘strict_trans_tables’

（2）重启mysql服务

---

问题4：

ERROR 1148 (42000): The used command is not allowed with this MySQL version

解决方法（2种）：

第一种： 重新编译安装加上上面的参数（这种一般不采取）：

./configure --prefix=/usr/local/mysql --enable-local-infile

make

make install

第二种： SQL命令加参数：--local-infile=1

mysql -uroot -proot  mydb_name --local-infile=1 -e 
'load  data local  infile  "/home/a.txt" into table AS_China fields terminated
 by "\t"  lines terminated by "\n";'

---