MySQL数据库 Load Data 多种用法-木庄网络博客

本文整理自网络，侵删。

MySQL Load Data 的多种用法

一、LOAD 基本背景

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

二、LOAD 基础参数

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

-- 导出基础参数
select * into outfile '/data/mysql/3306/tmp/employees.txt'
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
from employees.employees limit 10;

-- 导入基础参数
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
...

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

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

-- 导出的文件数据内容
[root@10-186-61-162 tmp]# cat employees.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 > desc demo.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_no  birth_date    first_name   last_name    gender  hire_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 > create table emp_tmp select emp_no,hire_date from emp;
SQL > desc emp_tmp;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no    | int  | NO   |     | NULL    |       |
| hire_date | date | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

-- 导入数据语句
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp_tmp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
-- 只对导出数据中指定的2个列与表中字段做匹配,mapping关系指定的顺序不影响导入结果
set hire_date=@C6,
    emp_no=@C1; 

-- 导入数据结果示例
SQL > select * from emp_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 |
+--------+------------+
10 rows in set (0.0016 sec)

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

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

-- 导入数据语句
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
-- 将文件中的字段与表中字段做mapping对应,表中多出的字段不做处理
set emp_no=@C1,
   birth_date=@C2,
   first_name=@C3,
   last_name=@C4,
   gender=@C5,
   hire_date=@C6;

阅读剩余部分