Contents
- Introduction of LOAD
- Basic parameters of LOAD
- Sample data and sample table structure of LOAD
- 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
- 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;
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
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
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.