MySQL Load Data 多种用法

MySQL Load Data 多种用法

1、
--导出基础参数 （以为,逗号作为分隔符，以"双引号作为界定符）

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;

select * from new into outfile '/tmp/new_files.txt' character set utf8mb4 fields terminated by ',' enclosed by '"';

--导入基础参数

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';

2、文件中的字段比数据表中的字段多
-- 导入数据语句

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;

示例：

查看new表字段
mysql> desc new;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| user_id | varchar(200) | YES  |     | NULL    |       |
| bike_sn | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

创建new_one表：
mysql> desc new_one;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| user_id | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

--只导入user_id字段数据

mysql> load data infile '/tmp/new_files.txt' replace into table new_one character set utf8mb4 fields terminated by ',' enclosed by '"' lines terminated by '\n'(@c1,@c2) set user_id=@c1;
Query OK, 182 rows affected (0.00 sec)

3、文件中的字段比数据表中的字段少
-- 导入数据语句

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;

4、Load 生成自定义的数据
表中新增的字段 fullname,modify_date,delete_flag 字段在导入时并未做处 理，被置为了 NULL 值，如果需要对其进行处理，可在 LOAD 时通过 MySQL 支持的函数 或给定 固定 值 自行定义数据，对于文件中存在的字段也可做函数处理，结合导入导出，实现简单的 ETL 功能，如 下所示：
-- 导入数据语句

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=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');  -- 对需要生成的值基于某一列做条件运算

5、导出定长数据

select concat(rpad(emp_no,10,' '), rpad(birth_date,19,' '), rpad(first_name,14,' '), rpad(last_name,16,' '), rpad(gender,2,' '), rpad(hire_date,19,' ')) as fixed_length_data 
into outfile '/data/mysql/3306/tmp/employees_fixed.txt' character set utf8mb4 lines terminated by '\n' from employees.employees limit 10;

    导入定长数据 

load data infile '/data/mysql/3306/tmp/employees_fixed.txt' 
replace into table demo.emp 
character set utf8mb4 
fields terminated by ',' 
enclosed by '"' lines terminated by '\n' (@row) -- 对一行数据定义为一个整体 
set emp_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 Data 总结：
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 中再进行加工转换处理。