CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL数据库 Load Data 多种用法由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

MySQL Load Data 的多种用法

  。

1、LOAD 基本背景

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

2、LOAD 基础参数

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

1. -- 导出基础参数
2. select * into outfile '/data/mysql/3306/tmp/employees.txt'
3. character set utf8mb4
4. fields terminated by ','
5. enclosed by '"'
6. lines terminated by ' '
7. from employees.employees limit 10;
8.  
9. -- 导入基础参数
10. load data infile '/data/mysql/3306/tmp/employees.txt'
11. replace into table demo.emp
12. character set utf8mb4
13. fields terminated by ','
14. enclosed by '"'
15. lines terminated by ' '
16. ...

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

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

1. -- 导出的文件数据内容
2. [root@10-186-61-162 tmp]# cat employees.txt
3. "10001","1953-09-02","Georgi","Facello","M","1986-06-26"
4. "10002","1964-06-02","Bezalel","Simmel","F","1985-11-21"
5. "10003","1959-12-03","Parto","Bamford","M","1986-08-28"
6. "10004","1954-05-01","Chirstian","Koblick","M","1986-12-01"
7. "10005","1955-01-21","Kyoichi","Maliniak","M","1989-09-12"
8. "10006","1953-04-20","Anneke","Preusig","F","1989-06-02"
9. "10007","1957-05-23","Tzvetan","Zielinski","F","1989-02-10"
10. "10008","1958-02-19","Saniya","Kalloufi","M","1994-09-15"
11. "10009","1952-04-19","Sumant","Peac","F","1985-02-18"
12. "10010","1963-06-01","Duangkaew","Piveteau","F","1989-08-24"
13.  
14. -- 示例表结构
15. SQL > desc demo.emp;
16. +-------------+---------------+------+-----+---------+-------+
17. | Field | Type | Null | Key | Default | Extra |
18. +-------------+---------------+------+-----+---------+-------+
19. | emp_no | int | NO | PRI | NULL | |
20. | birth_date | date | NO | | NULL | |
21. | first_name | varchar(16) | NO | | NULL | |
22. | last_name | varchar(16) | NO | | NULL | |
23. | fullname | varchar(32) | YES | | NULL | | -- 表新增字段,导出数据文件中不存在
24. | gender | enum('M','F') | NO | | NULL | |
25. | hire_date | date | NO | | NULL | |
26. | modify_date | datetime | YES | | NULL | | -- 表新增字段,导出数据文件中不存在
27. | delete_flag | char(1) | YES | | NULL | | -- 表新增字段,导出数据文件中不存在
28. +-------------+---------------+------+-----+---------+-------+
29.  
30. -- 导出的数据与字段对应关系
31. emp_no birth_date first_name last_name gender hire_date
32. "10001" "1953-09-02" "Georgi" "Facello" "M" "1986-06-26"
33. "10002" "1964-06-02" "Bezalel" "Simmel" "F" "1985-11-21"
34. "10003" "1959-12-03" "Parto" "Bamford" "M" "1986-08-28"
35. "10004" "1954-05-01" "Chirstian" "Koblick" "M" "1986-12-01"
36. "10005" "1955-01-21" "Kyoichi" "Maliniak" "M" "1989-09-12"
37. "10006" "1953-04-20" "Anneke" "Preusig" "F" "1989-06-02"
38. "10007" "1957-05-23" "Tzvetan" "Zielinski" "F" "1989-02-10"
39. "10008" "1958-02-19" "Saniya" "Kalloufi" "M" "1994-09-15"
40. "10009" "1952-04-19" "Sumant" "Peac" "F" "1985-02-18"
41. "10010" "1963-06-01" "Duangkaew" "Piveteau" "F" "1989-08-24"

4、LOAD 场景示例

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

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

1. -- 临时创建2个字段的表结构
2. SQL > create table emp_tmp select emp_no,hire_date from emp;
3. SQL > desc emp_tmp;
4. +-----------+------+------+-----+---------+-------+
5. | Field | Type | Null | Key | Default | Extra |
6. +-----------+------+------+-----+---------+-------+
7. | emp_no | int | NO | | NULL | |
8. | hire_date | date | NO | | NULL | |
9. +-----------+------+------+-----+---------+-------+
10.  
11. -- 导入数据语句
12. load data infile '/data/mysql/3306/tmp/employees.txt'
13. replace into table demo.emp_tmp
14. character set utf8mb4
15. fields terminated by ','
16. enclosed by '"'
17. lines terminated by ' '
18. (@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
19. -- 只对导出数据中指定的2个列与表中字段做匹配,mapping关系指定的顺序不影响导入结果
20. set hire_date=@C6,
21. emp_no=@C1;
22.  
23. -- 导入数据结果示例
24. SQL > select * from emp_tmp;
25. +--------+------------+
26. | emp_no | hire_date |
27. +--------+------------+
28. | 10001 | 1986-06-26 |
29. | 10002 | 1985-11-21 |
30. | 10003 | 1986-08-28 |
31. | 10004 | 1986-12-01 |
32. | 10005 | 1989-09-12 |
33. | 10006 | 1989-06-02 |
34. | 10007 | 1989-02-10 |
35. | 10008 | 1994-09-15 |
36. | 10009 | 1985-02-18 |
37. | 10010 | 1989-08-24 |
38. +--------+------------+
39. 10 rows in set (0.0016 sec)

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

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

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

  。

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

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

1. -- 导入数据语句
2. load data infile '/data/mysql/3306/tmp/employees.txt'
3. replace into table demo.emp
4. character set utf8mb4
5. fields terminated by ','
6. enclosed by '"'
7. lines terminated by ' '
8. (@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
9.  
10. -- 以下部分明确对表中字段与数据文件中的字段做Mapping关系,不存在的数据通过函数处理生成(也可设置为固定值)
11. set emp_no=@C1,
12. birth_date=@C2,
13. first_name=upper(@C3), -- 将导入的数据转为大写
14. last_name=lower(@C4), -- 将导入的数据转为小写
15. fullname=concat(first_name,' ',last_name), -- 对first_name和last_name做拼接
16. gender=@C5,
17. hire_date=@C6 ,
18. modify_date=now(), -- 生成当前时间数据
19. delete_flag=if(hire_date<'1988-01-01','Y','N'); -- 对需要生成的值基于某一列做条件运算

场景4. LOAD 定长数据 。

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

1. SQL > select
2. c1 as sample_data,
3. substr(c1,1,3) as c1,
4. substr(c1,4,3) as c2,
5. substr(c1,7,2) as c3,
6. substr(c1,9,5) as c4,
7. substr(c1,14,3) as c5,
8. substr(c1,17,3) as c6 from t1
9.  
10. *************************** 1. row ***************************
11. sample_data: ABC余振兴CDMySQLEFG数据库
12. c1: ABC
13. c2: 余振兴
14. c3: CD
15. c4: MySQL
16. c5: EFG
17. c6: 数据库

定长数据导入需要明确每列数据占用的字符个数，以下直接使用 rpad 对现有的表数据填充空格的方式生成定长数据用作示例使用 。

1. -- 生成定长数据
2. SQL > select
3. concat(rpad(emp_no,10,' '),
4. rpad(birth_date,19,' '),
5. rpad(first_name,14,' '),
6. rpad(last_name,16,' '),
7. rpad(gender,2,' '),
8. rpad(hire_date,19,' ')) as fixed_length_data
9. from employees.employees limit 10;
10.  
11. +----------------------------------------------------------------------------------+
12. | fixed_length_data |
13. +----------------------------------------------------------------------------------+
14. | 10001 1953-09-02 Georgi Facello M 1986-06-26 |
15. | 10002 1964-06-02 Bezalel Simmel F 1985-11-21 |
16. | 10003 1959-12-03 Parto Bamford M 1986-08-28 |
17. | 10004 1954-05-01 Chirstian Koblick M 1986-12-01 |
18. | 10005 1955-01-21 Kyoichi Maliniak M 1989-09-12 |
19. | 10006 1953-04-20 Anneke Preusig F 1989-06-02 |
20. | 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10 |
21. | 10008 1958-02-19 Saniya Kalloufi M 1994-09-15 |
22. | 10009 1952-04-19 Sumant Peac F 1985-02-18 |
23. | 10010 1963-06-01 Duangkaew Piveteau F 1989-08-24 |
24. +----------------------------------------------------------------------------------+
25.  
26. -- 导出定长数据
27. select
28. concat(rpad(emp_no,10,' '),
29. rpad(birth_date,19,' '),
30. rpad(first_name,14,' '),
31. rpad(last_name,16,' '),
32. rpad(gender,2,' '),
33. rpad(hire_date,19,' ')) as fixed_length_data
34. into outfile '/data/mysql/3306/tmp/employees_fixed.txt'
35. character set utf8mb4
36. lines terminated by ' '
37. from employees.employees limit 10;
38.  
39. -- 导出数据示例
40. [root@10-186-61-162 tmp]# cat employees_fixed.txt
41. 10001 1953-09-02 Georgi Facello M 1986-06-26
42. 10002 1964-06-02 Bezalel Simmel F 1985-11-21
43. 10003 1959-12-03 Parto Bamford M 1986-08-28
44. 10004 1954-05-01 Chirstian Koblick M 1986-12-01
45. 10005 1955-01-21 Kyoichi Maliniak M 1989-09-12
46. 10006 1953-04-20 Anneke Preusig F 1989-06-02
47. 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10
48. 10008 1958-02-19 Saniya Kalloufi M 1994-09-15
49. 10009 1952-04-19 Sumant Peac F 1985-02-18
50. 10010 1963-06-01 Duangkaew Piveteau F 1989-08-24
51.  
52. -- 导入定长数据
53. load data infile '/data/mysql/3306/tmp/employees_fixed.txt'
54. replace into table demo.emp
55. character set utf8mb4
56. fields terminated by ','
57. enclosed by '"'
58. lines terminated by ' '
59. (@row) -- 对一行数据定义为一个整体
60. set emp_no = trim(substr(@row,1,10)),-- 使用substr取前10个字符,并去除头尾空格数据
61. birth_date = trim(substr(@row,11,19)),-- 后续字段以此类推
62. first_name = trim(substr(@row,30,14)),
63. last_name = trim(substr(@row,44,16)),
64. fullname = concat(first_name,' ',last_name), -- 对first_name和last_name做拼接
65. gender = trim(substr(@row,60,2)),
66. hire_date = trim(substr(@row,62,19)),
67. modify_date = now(),
68. delete_flag = if(hire_date<'1988-01-01','Y','N'); -- 对需要生成的值基于某一列做条件运算

5、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 中再进行加工转换处理 。

以上就是 MySQL Load Data 数据的多种用法的详细内容，更多关于MySQL Load Data 的用法的资料请关注我其它相关文章！,希望大家以后多多支持我们！ 。

原文链接：https://blog.csdn.net/ActionTech/article/details/115212340 。

最后此篇关于MySQL数据库 Load Data 多种用法的文章就讲到这里了,如果你想了解更多关于MySQL数据库 Load Data 多种用法的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。