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

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

从场景 2 的验证可以看到，emp 表中新增的字段 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'); -- 对需要生成的值基于某一列做条件运算

场景4. LOAD 定长数据

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

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

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

-- 生成定长数据
SQL > 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 
      from employees.employees limit 10;

+----------------------------------------------------------------------------------+
| fixed_length_data                                                                |
+----------------------------------------------------------------------------------+
| 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          |
+----------------------------------------------------------------------------------+

-- 导出定长数据
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;

-- 导出数据示例
[root@10-186-61-162 tmp]# cat employees_fixed.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

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