MySQL load data, high-speed text files inserted into the database

1 syntax

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name.txt '

[REPLACE | IGNORE]

Into TABLE tbl_name

[Fields

[TERMINATED by ' string ']

[[optionally] enclosed by ' char ']

[Escaped by ' char ']

]

[LINES

[Starting by ' string ']

[TERMINATED by ' string ']

]

[IGNORE number LINES]

[(Col_name_or_user_var,...)]

[SET col_name = expr,...] ]

Keyword explanation

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name.txt '

[REPLACE | IGNORE]

Into TABLE tbl_name

1low_priority: Load data is delayed until no other client is read from the table
CONCURRENT: When the loaded data is in progress, a new thread is opened to fetch the data if a user requests it

2 If the local keyword is specified, it interprets the connected client:

? If local is specified, the client component on the client host reads the file and sends it to the server. You can give the full path to the file to determine its exact location. If a relative path is given, the file name is relative to the directory where the client component was started.
? If you do not specify local, the file is located on the server's host and is read directly by the server.
When you locate a file from a server host, the server uses the following rules:
? If a full path is given, the server uses that pathname.
? Given a relative path to one or more predecessor artifacts, the server searches for files in the relative server's data directory.
? Given a file name that does not have a predecessor, the server searches for the file from the database directory of the current database.

3REPLACE and Ignore keywords handle input records that are duplicated with existing primary key values
If replace is specified, the input row replaces the existing row (that is, the row with the same primary index value as the existing line). Refer to replace syntax.
If ignore is specified, the input rows that are duplicated with the existing row primary key values are skipped. If you do not specify either of these, the action behavior depends on whether the local keyword is specified.
No local is specified, and if duplicate key values are found, an error is generated and the remainder of the text file is ignored.
If local is specified, the default action behavior is the same as specifying ignore, because during the operation, the server has no way to terminate the transfer of the file.

[Fields

[TERMINATED by ' string ']

[[optionally] enclosed by ' char ']//enclosed by describes the enclosed character of a field.

[Escaped by ' char ']//escaped is a description of the escape character. The default is a backslash (backslash:\)

]
[LINES

[Starting by ' string ']

[TERMINATED by ' string ']

]
4
Data format:
"Son of China", "100", "Mighty"
"Rice", "63", "I love to eat"
"Apple", "15", "Yummy"
Load data infile ' d:/aa.txt ' ignore into table name character set GBK fields terminated by ', ' enclosed by ' "' lines Termina Ted by ' \ r \ n ' (Field 1 in table, field 2 in table, field 3 in table);
Terminated by ', ' with what style characters
Enclosed by ' "' characters with what wrapped up
Lines terminated by ' \ r \ n ' Line end newline character (liunx to \ n)


[IGNORE number LINES]

[(Col_name_or_user_var,...)]

[SET col_name = expr,...] ]

The 5IGNORE number lines option can be used to ignore a line in a file

Suppose the data file is as follows:

Book1.csv

Number, name, description
1, test data 1, "Test CSV file, there are commas"
2, test Data 2, "The test CSV file has" "double quotes" ""
3, test Data 3, "in the test CSV file, there are commas and" "double quotes" ""
4, test data 4, normal data

Mysql> CREATE TABLE Test_book1 (
ID int,
Name VARCHAR (10),
Data VARCHAR (100)
);
Query OK, 0 rows affected (0.05 sec)


The following lines terminated by ' \ r \ n ' is a newline symbol that requires line breaks for Windows
The following ignore 1 lines is the header row that ignores the first row.
mysql> LOAD DATA INFILE ' f:/book1.csv '
Into TABLE Test_book1
--TERMINATED by ', '
-Optionally enclosed by ' "'
-lines terminated by ' \ r \ n '
Ignore 1 lines
(ID, name, data);

Mysql> select * from Test_book1;
+------+-----------+--------------------------------+
| ID | name | Data |
+------+-----------+--------------------------------+
| 1 | Test Data 1 | Test csv file with comma |
| 2 | Test Data 2 | "Double quotes" in the test CSV file |
| 3 | Test Data 3 | In the test CSV file, there are commas and "double quotes" |
| 4 | Test Data 4 | General Data |
+------+-----------+--------------------------------+

Ignore 1 lines ignores the header row of the first row.

Use of [email protected]
Suppose the data file is as follows:

Book1.csv


1, test data 1, "Test CSV file, there are commas"
2, test Data 2, "The test CSV file has" "double quotes" ""
3, test Data 3, "in the test CSV file, there are commas and" "double quotes" ""
4, test data 4, normal data

Mysql> CREATE TABLE Test_book1 (
ID int,
Name VARCHAR (10),
Data VARCHAR (100)
);
Query OK, 0 rows affected (0.05 sec)
I just need the first, two columns of data.
mysql> LOAD DATA INFILE ' F:/test_main.txt '
Into TABLE test_main6
--TERMINATED by ', '
-Optionally enclosed by ""
(Id,name, @dummy);

Mysql> select * from Test_book1;
+------+-----------+--------------------------------+
| ID | name | Data |
+------+-----------+--------------------------------+
| 1 |                                Test Data 1 | |
| 2 |                                Test Data 2 | |
| 3 |                                Test Data 3 | |
| 4 |                                Test Data 4 | |
+------+-----------+--------------------------------+

The key point here is the @dummy.
For you, if the file has 10 columns, you only guide 2, 7, that is
@dummy, column 2, @dummy, @dummy, @dummy, @dummy, column 7, @dummy, @dummy, @dummy

7set clause
The SET clause can provide a value that is not derived from the input file. The following statement sets Column3 to the current date and time:
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, Column2)
SET column3 = Current_timestamp;
You can have the values of a column participate in some operations
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, @var1)
SET column2 = @var1/100;
You can also make the data in the front and back columns relational (assuming that the first column is a date-formatted data, my third column calculates the week information based on the date of the first column and then into the database)
LOAD DATA INFILE ' file.txt '
into TABLE t1
(Column1, Column2)
Set column3 = Set name=case when Date_format (DATE, '%w ') =1 then "Monday"
When Date_format (DATE, '%w ') =2 and then "Tuesday"
End

Use the column/variable list and the SET clause to be restricted:
? The assignment column name in the SET clause should be only to the left of the assignment operator.
? In a set assignment statement, you can use a subquery. This query returns a value that will be assigned to the column, which may be just a scalar query. You cannot use a subquery to query the table that will be imported.
? For a column/variable list or SET clause, rows that are ignored because the IGNORE clause is used will not be processed.
? Because user variables do not display widths, user variables cannot be used when the imported data is in a row-invariant format.

8 Setting the characters to use when depositing

GBK UTF8

Load data infile ' c:\\users\\edgewalk\\desktop\\test.txt ' ignore into table test character set GBK fields terminated by ', ‘
Lines terminated by ' \ n ' (
Date
Money
Desctipt
)

2 Import and export backup data using load data infile

If you want to export some of the fields in a table or some of the qualifying records, you need to use MySQL into outfile and load data infile. For example, the following MySQL command exports data from the MyTable table of Select to C:\\users\\7q\\desktop\\test1.txt.
SELECT * FROM test to outfile ' C:\\users\\7q\\desktop\\test1.txt ' fields terminated by ', ' lines terminated by ' \ r \ n ';

If you want to import the data you just backed up, you can use the load file method, such as the following MySQL command, to import the exported data into the Mytable_bak table:
Load data infile ' c:\\users\\7q\\desktop\\test1.txt ' into table Mytable_bak fields terminated by ', ' lines terminated by ' \ r \ n ';
The advantage of this approach is that the exported data can be self-defined format, and the export is pure data, there is no table information, you can directly import another database with different tables.

MySQL load data, high-speed text files inserted into the database