爱可生 DBA 团队成员，熟习 Oracle、MySQL、MongoDB、Redis，最近在盘 TiDB，善于架构设计、故障诊断、数据迁移、灾备构建等等。
卖力处理客户 MySQL 及我司自研 DMP 数据库管理平台日常运维中的问题。
热衷技能分享、编写技能文档。

本文来源：原创投稿

爱可生开源社区出品，原创内容未经授权不得随意利用，转载请联系

一、LOAD 基本背景

phpmysqlloaddata技巧分享 MySQL Load Data 的多种用法 NoSQL

二、LOAD 根本参数

三、LOAD 示例数据及示例表构造

四、LOAD 场景示例

场景 1. LOAD 文件中的字段比数据表中的字段多

场景 2. LOAD 文件中的字段比数据表中的字段少

场景 3. LOAD 天生自定义字段数据

场景 4. LOAD 定长数据

五、LOAD 总结

LOAD 基本背景

我们在数据库运维过程中难免会涉及到须要对文本数据进行处理，并导入到数据库中，本文整理了一些导入导出时常见的场景进行示例演示。

LOAD 根本参数

文章后续示例均利用以下命令导出的 csv 格式样例数据(以 , 逗号做分隔符，以 " 双引号作为界定符)

--导出根本参数selectintooutfile'/data/mysql/3306/tmp/employees.txt'charactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'fromemployees.employeeslimit10;--导入根本参数loaddatainfile'/data/mysql/3306/tmp/employees.txt'replaceintotabledemo.empcharactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'...LOAD 示例数据及示例表构造

以下为示例数据，表构造及对应关系信息

--导出的文件数据内容[root@10-186-61-162tmp]#catemployees.txt"10001","1953-09-02","Georgi","Facello","M","1986-06-26""10002","1964-06-02","Bezalel","Simmel","F","1985-11-21""10003","1959-12-03","Parto","Bamford","M","1986-08-28""10004","1954-05-01","Chirstian","Koblick","M","1986-12-01""10005","1955-01-21","Kyoichi","Maliniak","M","1989-09-12""10006","1953-04-20","Anneke","Preusig","F","1989-06-02""10007","1957-05-23","Tzvetan","Zielinski","F","1989-02-10""10008","1958-02-19","Saniya","Kalloufi","M","1994-09-15""10009","1952-04-19","Sumant","Peac","F","1985-02-18""10010","1963-06-01","Duangkaew","Piveteau","F","1989-08-24"--示例表构造SQL>descdemo.emp;+-------------+---------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------------+---------------+------+-----+---------+-------+|emp_no|int|NO|PRI|NULL|||birth_date|date|NO||NULL|||first_name|varchar(16)|NO||NULL|||last_name|varchar(16)|NO||NULL|||fullname|varchar(32)|YES||NULL||--表新增字段,导出数据文件中不存在|gender|enum('M','F')|NO||NULL|||hire_date|date|NO||NULL|||modify_date|datetime|YES||NULL||--表新增字段,导出数据文件中不存在|delete_flag|char(1)|YES||NULL||--表新增字段,导出数据文件中不存在+-------------+---------------+------+-----+---------+-------+--导出的数据与字段对应关系emp_nobirth_datefirst_namelast_namegenderhire_date"10001""1953-09-02""Georgi""Facello""M""1986-06-26""10002""1964-06-02""Bezalel""Simmel""F""1985-11-21""10003""1959-12-03""Parto""Bamford""M""1986-08-28""10004""1954-05-01""Chirstian""Koblick""M""1986-12-01""10005""1955-01-21""Kyoichi""Maliniak""M""1989-09-12""10006""1953-04-20""Anneke""Preusig""F""1989-06-02""10007""1957-05-23""Tzvetan""Zielinski""F""1989-02-10""10008""1958-02-19""Saniya""Kalloufi""M""1994-09-15""10009""1952-04-19""Sumant""Peac""F""1985-02-18""10010""1963-06-01""Duangkaew""Piveteau""F""1989-08-24"LOAD 场景示例场景 1. LOAD 文件中的字段比数据表中的字段多

只须要文本文件中部分数据导入到数据表中

--临时创建2个字段的表构造SQL>createtableemp_tmpselectemp_no,hire_datefromemp;SQL>descemp_tmp;+-----------+------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+------+------+-----+---------+-------+|emp_no|int|NO||NULL|||hire_date|date|NO||NULL||+-----------+------+------+-----+---------+-------+--导入数据语句loaddatainfile'/data/mysql/3306/tmp/employees.txt'replaceintotabledemo.emp_tmpcharactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'(@C1,@C2,@C3,@C4,@C5,@C6)--该部分对应employees.txt文件中6列数据--只对导出数据中指定的2个列与表中字段做匹配,mapping关系指定的顺序不影响导入结果sethire_date=@C6,emp_no=@C1;--导入数据结果示例SQL>selectfromemp_tmp;+--------+------------+|emp_no|hire_date|+--------+------------+|10001|1986-06-26||10002|1985-11-21||10003|1986-08-28||10004|1986-12-01||10005|1989-09-12||10006|1989-06-02||10007|1989-02-10||10008|1994-09-15||10009|1985-02-18||10010|1989-08-24|+--------+------------+10rowsinset(0.0016sec)场景 2. LOAD 文件中的字段比数据表中的字段少

表字段不仅包含文本文件中所有数据,还包含了额外的字段

--导入数据语句loaddatainfile'/data/mysql/3306/tmp/employees.txt'replaceintotabledemo.empcharactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'(@C1,@C2,@C3,@C4,@C5,@C6)--该部分对应employees.txt文件中6列数据--将文件中的字段与表中字段做mapping对应,表中多出的字段不做处理setemp_no=@C1,birth_date=@C2,first_name=@C3,last_name=@C4,gender=@C5,hire_date=@C6;

场景 3. LOAD 天生自定义字段数据

从场景 2 的验证可以看到，emp 表中新增的字段 fullname,modify_date,delete_flag 字段在导入时并未做处理，被置为了 NULL 值，如果须要对其进行处理，可在 LOAD 时通过 MySQL支持的函数 或给定 固定值 自行定义数据，对付文件中存在的字段也可做函数处理，结合导入导出，实现大略的 ETL 功能，如下所示：

--导入数据语句loaddatainfile'/data/mysql/3306/tmp/employees.txt'replaceintotabledemo.empcharactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'(@C1,@C2,@C3,@C4,@C5,@C6)--该部分对应employees.txt文件中6列数据--以下部分明确对表中字段与数据文件中的字段做Mapping关系,不存在的数据通过函数处理天生(也可设置为固定值)setemp_no=@C1,birth_date=@C2,first_name=upper(@C3),--将导入的数据转为大写last_name=lower(@C4),--将导入的数据转为小写fullname=concat(first_name,'',last_name),--对first_name和last_name做拼接gender=@C5,hire_date=@C6,modify_date=now(),--天生当前韶光数据delete_flag=if(hire_date<'1988-01-01','Y','N');--对须要天生的值基于某一列做条件运算

场景 4. LOAD 定长数据

定长数据的特点如下所示，可以利用函数取出字符串中固定长度来天生指定列数据

SQL>selectc1assample_data,substr(c1,1,3)asc1,substr(c1,4,3)asc2,substr(c1,7,2)asc3,substr(c1,9,5)asc4,substr(c1,14,3)asc5,substr(c1,17,3)asc6fromt11.rowsample_data:ABC余振兴CDMySQLEFG数据库c1:ABCc2:余振兴c3:CDc4:MySQLc5:EFGc6:数据库

定长数据导入须要明确每列数据占用的字符个数，以下直策应用 rpad 对现有的表数据添补空格的办法天生定长数据用作示例利用

--天生定长数据SQL>selectconcat(rpad(emp_no,10,''),rpad(birth_date,19,''),rpad(first_name,14,''),rpad(last_name,16,''),rpad(gender,2,''),rpad(hire_date,19,''))asfixed_length_datafromemployees.employeeslimit10;+----------------------------------------------------------------------------------+|fixed_length_data|+----------------------------------------------------------------------------------+|100011953-09-02GeorgiFacelloM1986-06-26||100021964-06-02BezalelSimmelF1985-11-21||100031959-12-03PartoBamfordM1986-08-28||100041954-05-01ChirstianKoblickM1986-12-01||100051955-01-21KyoichiMaliniakM1989-09-12||100061953-04-20AnnekePreusigF1989-06-02||100071957-05-23TzvetanZielinskiF1989-02-10||100081958-02-19SaniyaKalloufiM1994-09-15||100091952-04-19SumantPeacF1985-02-18||100101963-06-01DuangkaewPiveteauF1989-08-24|+----------------------------------------------------------------------------------+--导出定长数据selectconcat(rpad(emp_no,10,''),rpad(birth_date,19,''),rpad(first_name,14,''),rpad(last_name,16,''),rpad(gender,2,''),rpad(hire_date,19,''))asfixed_length_dataintooutfile'/data/mysql/3306/tmp/employees_fixed.txt'charactersetutf8mb4linesterminatedby'\n'fromemployees.employeeslimit10;--导出数据示例[root@10-186-61-162tmp]#catemployees_fixed.txt100011953-09-02GeorgiFacelloM1986-06-26100021964-06-02BezalelSimmelF1985-11-21100031959-12-03PartoBamfordM1986-08-28100041954-05-01ChirstianKoblickM1986-12-01100051955-01-21KyoichiMaliniakM1989-09-12100061953-04-20AnnekePreusigF1989-06-02100071957-05-23TzvetanZielinskiF1989-02-10100081958-02-19SaniyaKalloufiM1994-09-15100091952-04-19SumantPeacF1985-02-18100101963-06-01DuangkaewPiveteauF1989-08-24--导入定长数据loaddatainfile'/data/mysql/3306/tmp/employees_fixed.txt'replaceintotabledemo.empcharactersetutf8mb4fieldsterminatedby','enclosedby'"'linesterminatedby'\n'(@row)--对一行数据定义为一个整体setemp_no=trim(substr(@row,1,10)),--利用substr取前10个字符,并去除头尾空格数据birth_date=trim(substr(@row,11,19)),--后续字段以此类推first_name=trim(substr(@row,30,14)),last_name=trim(substr(@row,44,16)),fullname=concat(first_name,'',last_name),--对first_name和last_name做拼接gender=trim(substr(@row,60,2)),hire_date=trim(substr(@row,62,19)),modify_date=now(),delete_flag=if(hire_date<'1988-01-01','Y','N');--对须要天生的值基于某一列做条件运算

LOAD 总结

1.默认情形下导入的顺序以文本文件 列-从左到右，行-从上到下 的顺序导入

2.如果表构造和文本数据不一致，建议将文本文件中的各列依次顺序编号并与表中字段建立 mapping 关系，以防数据导入到缺点的字段

3.对付待导入的文本文件较大的场景，建议将文件 按行拆分 为多个小文件，如用 split 拆分

4.对文件导入后建议实行以下语句验证导入的数据是否有 Warning,ERROR 以及导入的数据量

GET DIAGNOSTICS @p1=NUMBER,@p2=ROW_COUNT;select @p1 AS ERROR_COUNT,@p2 as ROW_COUNT;

5.文本文件数据与表构造存在过大的差异或数据须要做洗濯转换，建议还是用专业的 ETL 工具或先粗略导入 MySQL 中再进行加工转换处理