【mysql】load data 避雷针

@Hadi

---

初来乍到 多多点赞

---

一、前言

最近都会PO出一些
关于各类数据库ETL过程的经验总结

---

load data - 简介

MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中（一般文本的一行对应表的一条记录）。
数据库应用程序开发中，涉及大批量数据需要插入时，使用 load data 语句的效率比一般的 insert 语句的*高很多，可以看成select … into outfile语句的反操作，select … into outfile将数据库表中的数据导出保存到一个文件中，常用于批处理数据中的ETL流程。

load data - 语法

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、环境确认

• LOCAL关键字

如果 load data 使用时指定了 local关键字，则表示文件放在客户端主机上，从客户端读取文本文件；如果没指定，则表示从服务器主机读取文本文件

所以在确认使用load data方式入库后，应尽早确认MySQL系统表中对于文件导入的各种配置，里面会涉及到文件导入路径的限制，以及客户端引用本地文件的各类限制，而在生产环境ETL过程中，出于安全限制考虑我们并不会直接触到数据库所在服务器，一般会通过远程连接安全机开放端口再跳转到数据库所在服务器，所以引用load data 前必须把MYSQL对于客户端导入相关设定配置好。

1、本机导入路径查询
==============
show variables like '%secure%';


2、权限查询
==============
SHOW GRANTS FOR @@user@@;


3、客户端导入开关
==============
show variables like '%local_infile%'



4、客户端导入开关设置
==============
set global local_infile=1;


导入过程
========================
mysql -h @@ip@@ -u @@user@@ -p@@password@@ @@db_name@@ < @@导入控制文件具体位置@@
[注意-p后紧接密码参数]

2、注意空值问题

其实我觉得load data最大的问题是在于数据空值的情况，这种问题在测试时可能较容易忽略，因为我发现MYSQL在导入文件时遇到空值时，会有不同的处理机制。

• 数据库字段如果是varchar/char，插入空时，load data 默认导入 空字符串
• 数据库字段如果是decimal，插入空时，load data 默认导入 0.00000000
• 数据库字段如果是datetime，插入空时，load data 默认导入 0000-00-00 00:00:00

---

所以我会用空值判断的语法避免数据出错
以下为导入范式

load data infile ‘/var/lib/mysql-files/edp_dgxdfhz_tg_f.csv’
into table edp_dgxdfhz_tg_f
character set utf8
fields terminated by ’ | ’
lines terminated by ‘\n’
(
dkfhzh,
xdjjh,
khtybh,
yxjgdm,
jrxkzh,
nbjgh,
mxkmbh,
yxjgmc,
mxkmmc,
zhmc,
bz,
zjhkrq,
tjkmbh,
dkhth,
xdyxm,
dkwjfl,
hkzh,
dkrzzh,
@dkll,
@dkbjze,
@zcbj,
@yqbj,
@yslx,
@bwqxye,
@dkqx,
dqrq,
qxrq,
khrq,
xhrq,
yqrq,
zhzt,
cjrq,
RLUE_DEP
)
set
dkll =NULLif(@dkll,’’),
dkbjze =NULLif(@dkbjze,’’),
zcbj =NULLif(@zcbj,’’),
yqbj =NULLif(@yqbj,’’),
yslx =NULLif(@yslx,’’),
bwqxye =NULLif(@bwqxye,’’),
dkqx =NULLif(@dkqx,’’)
;

---

写在最后

‘于我而言
我会隔一段时间记录下
各类问题以及解决方案
这样即使过了一段时间
我也可以很快地绕过深坑 ’