MySQL database Load Data multiple uses

Multiple uses of MySQL Load Data

1. LOAD Basic Background
2. LOAD basic parameters
3. LOAD sample data and sample table structure
4. LOAD scenario example
5. LOAD Summary

Multiple uses of MySQL Load Data

1. LOAD Basic Background

In the process of database operation and maintenance, we will inevitably need to process text data and import it into the database. This article organizes some common scenarios of import and export for example demonstration.

2. LOAD basic parameters

The following examples in this article all use the following command to export sample data in csv format (with commas as separators and double quotes as delimiters)

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

-- 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. LOAD sample data and sample table structure

The following is sample data, table structure and corresponding relationship information

--Exported file data content [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","Christian","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"

-- Sample table structure 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 | | -- A new field in the table, which does not exist in the exported data file | gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
| modify_date | datetime | YES | | NULL | | -- A new field is added to the table, which does not exist in the exported data file| delete_flag | char(1) | YES | | NULL | | -- A new field is added to the table, which does not exist in the exported data file+-------------+---------------+------+-----+---------+-------+

-- The corresponding relationship between the 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" "Christian" "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. LOAD scenario example

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

Only part of the data in the text file needs to be imported into the data table

-- Temporarily create a table structure with two 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 | |
+-----------+------+------+-----+---------+-------+

-- Import data statement 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 data in the employees.txt file -- Only the 2 columns specified in the exported data are matched 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; 

-- Import data result example 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 data table

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

-- Import data statement 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 -- Map the fields in the file to the fields in the table, and do not process the extra fields in the table. set emp_no=@C1,
   birth_date=@C2,
   first_name=@C3,
   last_name=@C4,
   gender=@C5,
   hire_date=@C6;

Scenario 3. LOAD generates custom field data

From the verification of scenario 2, we can see that the newly added fields fullname,modify_date,delete_flag in the emp table are not processed during import and are set to NULL values. If you need to process them, you can define the data yourself through MySQL支持的函數or by giving固定值during LOAD. You can also process the fields in the file with functions. Combined with import and export, a simple ETL function can be implemented, as shown below:

-- Import data statement 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 -- The following part clearly maps the fields in the table to the fields in the data file. Non-existent data is generated through function processing (it can also be set to a fixed value)
set emp_no=@C1,
   birth_date=@C2,
   first_name=upper(@C3), -- Convert the imported data to uppercase last_name=lower(@C4), -- Convert the imported data to lowercase fullname=concat(first_name,' ',last_name), -- Concatenate first_name and last_name gender=@C5,
   hire_date=@C6 ,
   modify_date=now(), -- Generate current time data delete_flag=if(hire_date<'1988-01-01','Y','N'); -- Perform conditional operations on the values to be generated based on a certain column

Scenario 4. LOAD fixed-length data

The characteristics of fixed-length data are as follows. You can use the function to extract the fixed length in 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 Yu Zhenxing CD MySQL EF G database c1: ABC
         c2: Yu Zhenxing c3: CD
         c4: MySQL
         c5: EFG
         c6: Database

Fixed-length data import requires the number of characters occupied by each column of data to be clear. The following example uses rpad to fill spaces in the existing table data to generate fixed-length data.

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

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

--Export data example [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 Christian 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

-- 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) -- Define a row of data as a whole set emp_no = trim(substr(@row,1,10)), -- Use substr to get the first 10 characters and remove the leading and trailing spaces birth_date = trim(substr(@row,11,19)), -- The subsequent fields are similar first_name = trim(substr(@row,30,14)),
   last_name = trim(substr(@row,44,16)),
   fullname = concat(first_name,' ',last_name), -- 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<'1988-01-01','Y','N'); -- Perform conditional operations on the values to be generated based on a certain column

5. LOAD Summary

1. By default, the import order is列-從左到右，行-從上到下bottom in the text file.

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

3. For scenarios where the text file to be imported is large, it is recommended to按行拆分, such as using split

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

GET DIAGNOSTICS @p1=NUMBER,@p2=ROW_COUNT;
select @p1 AS ERROR_COUNT,@p2 as ROW_COUNT;

5. If there is a huge difference between the text file data and the table structure or the data needs to be cleaned and converted, it is recommended to use a professional ETL tool or roughly import it into MySQL before processing and converting it.

The above is the detailed content of the various uses of MySQL Load Data. For more information about the usage of MySQL Load Data, please pay attention to other related articles on 123WORDPRESS.COM! , I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

Springboot uses spring-data-jpa to operate MySQL database
DataGrip connects to Mysql and creates a database
Springboot configures mysql database spring.datasource.url error solution
Implementation of restoring data by directly copying files in the data directory in mysql
Python pymysql link database query results converted to Dataframe instance
MySQL database data basic operations

<<: Detailed explanation of Docker compose orchestration tool

>>: This article will show you what Vite does to the browser's request