Usage and Usage Scenarios of MySQL Load Data

Usage and Usage Scenarios of MySQL Load Data

7.png


Contents

  1. Introduction of LOAD
  2. Basic parameters of LOAD
  3. Sample data and sample table structure of LOAD
  4. Scenario examples of LOAD

(1). There are more fields in the LOAD file than in the table

(2). There are fewer fields in the LOAD file than in the table

(3). LOAD to generate custom field data

(4). LOAD fixed-length data

  1. Summary

1. Introduction of LOAD

When in the database operation and maintenance, we will inevitably need to process text data and import them into database. This article gathers some common scenarios as examples where text data are imported and exported.

2. Basic parameters of LOAD

The following examples in this article use the following command to export sample data in csv format (use comma as a separator, and use double quotation mark as a delimiter).

-- To export basic parameters

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;

-- To import basic parameters

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'

...

3. Sample data and sample table structure of LOAD

The following is the sample data, sample table structure and the correspondence of them

-- Exported file data

[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"

-- The structure of sample table

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 | | -- New fields in the table, do not exist in the exported data files

| gender | enum('M','F') | NO | | NULL | |

| hire_date | date | NO | | NULL | |

| modify_date | datetime | YES | | NULL | | -- New fields in the table, do not exist in the exported data files

| delete_flag | char(1) | YES | | NULL | | -- New fields in the table, do not exist in the exported data files

+-------------+---------------+------+-----+------ ---+-------+

-- Corresponding relations between exported data and fields

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"

4. Scenario examples of LOAD

Scenario 1: There are more fields in the LOAD file than in the table

You only need to import part of the data in the text file into the table

-- To temporarily create a table structure with 2 fields

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

+-----------+------+------+-----+---------+------- +

-- Statements to import data

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) -- This part corresponds to the 6 columns of the data in the employees.txt file

-- Only match the 2 columns specified in the exported data with the fields in the table. The order specified by the mapping relationship does not affect the import result.

set hire_date=@C6,

emp_no=@C1;

-- Example of imported data and the result

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)

Scenario 2: There are fewer fields in the LOAD file than in the table

The table fields contain not only all the data in the text file, but also additional fields.

-- Statements to import data

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

(@C1,@C2,@C3,@C4,@C5,@C6) - This part corresponds to th 6 columns of data in the employees.txt file

-- To create Mapping relationship between the fields in the file and the fields in the table. The extra fields in the table are not processed.

set emp_no=@C1,

birth_date=@C2,

first_name=@C3,

last_name=@C4,

gender=@C5,

hire_date=@C6;

使用场景1.png

Scenario 3: LOAD to create custom field data

We can see from the verification of scenario 2 that the new fields of fullname, modify_date, and delete_flag in the emp table are not processed when imported and the values are set to NULL. If you need to process them, you can assign the value by yourself through functions supported by MySQL or giving fixed values to customize the data during LOAD. The fields existing in the file can also be processed with functions, combined with import and export, to realize the simple ETL function, as shown below:

-- Statements to import data

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) -- This part corresponds to the 6 columns of data in the employees.txt file

-- To create the Mapping relationship between the fields in the table and the fields in the data file in the following part, and the inexistent data is generated by function processing (or set to a fixed value)

set emp_no=@C1,

birth_date=@C2,

first_name=upper(@C3), -- To convert the imported data to uppercase

last_name=lower(@C4), -- To convert the imported data to lowercase

fullname=concat(first_name,'',last_name), -- To concatenate first_name and last_name

gender=@C5,

hire_date=@C6,

modify_date=now(), -- To generate current time data

delete_flag=if(hire_date

使用场景2.png

Scenario 4: LOAD fixed-length data

The features of fixed-length data are as follows. You can use the function to extract the fixed-length data from the string to generate the specified column data

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 Zhenxing Yu CDMySQLEFG database

c1: ABC

c2: Zhenxing Yu

c3: CD

c4: MySQL

c5: EFG

c6: database

To import fixed-length data, it is necessary to clarify the number of characters occupied by each column of data. We directly use rpad to fill in the existing table data to generate fixed-length data as an example below.

-- To generate fixed-length data

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 |

+------------------------------------------------- ---------------------------------+

-- To export fixed-length data

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;

-- An example to export data

[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

-- To import fixed-length data

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) -- To define a row of data as an ensemble

set emp_no = trim(substr(@row,1,10)), -- To take the first 10 characters with substr, and remove the leading and trailing spaces.

birth_date = trim(substr(@row,11,19)), -- The same with the operation above in the subsequent fields.

first_name = trim(substr(@row,30,14)),

last_name = trim(substr(@row,44,16)),

fullname = concat(first_name,'',last_name), -- To concatenate first_name and last_name

gender = trim(substr(@row,60,2)),

hire_date = trim(substr(@row,62,19)),

modify_date = now(),

delete_flag = if(hire_date

使用场景3.png

5.Summary

(1) The default order of import is “column-from left to right, row-from top to bottom”.

(2) If the table structure is inconsistent with the text data, it is recommended to number the columns in the text file in order and establish a mapping relationship with the fields in the table to prevent the data from being imported into the wrong field.

(3) As for the scenarios to be imported with large text files, it is recommended to split the file into multiple small files by line.

(4) After importing the file, it is recommended to execute the following statements to verify the amount of imported data and whether the imported data has Warning or ERROR.

GET DIAGNOSTICS @p1=NUMBER,@p2=ROW_COUNT;

select @p1 AS ERROR_COUNT,@p2 as ROW_COUNT;

(5) If there are striking differences between the text file data and the table structure, or the data need to be cleaned and converted, it is recommended to use professional ETL tools, or roughly import it into MySQL before processing and converting.