The load command in mysql USES the method

  • 2020-05-30 21:12:55
  • OfStack

Use the load command in mysql to load the contents of the txt file into the database table. For example, create table with the name user and one field username. There are currently db.txt files with user names separated by Spaces, such as xiaowang xiaoliu zhangsan

To load this file into the data table user, use the command:

load data local infile "/home/beaver/db" into table user lines terminated by ' ';  

The author Beaver 's


This is the last method we will introduce to import data into the MySQL database. This command is very similar to mysqlimport, but this method can be used on the mysql command line. That means you can use this command in all programs that use API. With this approach, you can import the data you want into your application. The mysqld process (service) must already be running before you can use this command.

Launch mysql command line:

bin/mysql � p

After successfully entering the mysql command line, enter the following command:
USE Meet_A_Geek;
LOAD DATA INFILE "/home/mark/data.sql" INTO TABLE Orders;

Simply put, this will import the contents of the data.sql file into table Orders, as shown in mysqlimport tool 1, which also has some optional parameters. For example, if you need to import data from your computer into a remote database server, you can use the following command:

The LOCAL parameter above means that the file is a local file and the server is the server on which you log in. Instead of using ftp to upload the file to the server, MySQL does it for you. You can also set the priority of the insert statement, and if you mark it as low priority (LOW_PRIORITY), MySQL will wait until no one else reads the table before inserting the data. You can use the following command:
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" INTO TABLE Orders;

You can also specify whether to replace or ignore duplicate key values in files and tables when inserting data. Syntax for replacing duplicate key values:
The sentence above looks a little clunky, but it places the keywords where your parser can understand them.

The following 1 pair of options describes the record format of the file, which are also available in the mysqlimport tool. They look a little different here. First, use the FIELDS keyword. If you use this keyword, the MySQL parser will want to see at least one of the following options:
ENCLOSED BY character
ESCAPED BY character

These keywords and their parameters are the same as those used in mysqlimport. The TERMINATED BY description field delimiter, by default tab character (\t)
ENCLOSED BY describes the bracketed characters of a field. For example, enclose each field in quotation marks.
ESCAPED BY describes escape characters. The default is backbars (backslash: \).

Here, again using the previous mysqlimport command example, import the same file into the database with the LOAD DATA INFILE statement:

One LOAD DATA INFILE statement has no features in the mysqlimport tool:

LOAD DATA INFILE can import files into the database as specified columns. This feature is important when importing part 1 of the data. For example, when we upgrade from Access database to MySQL database, we need to add some columns (columns/fields /field) to MySQL database to accommodate some additional needs.

At this point, the data in our Access database is still available, but because the columns of this data (field) no longer match those in MySQL, the mysqlimport tool is no longer available. However, we can still use LOAD DATA INFILE, and the following example shows how to import data into a specified column (field) :
LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date, Customer_ID);

As you can see, we can specify the columns we want (fields). These specified fields are still enclosed in parentheses, separated by commas, and if you miss any of them, MySQL will remind you to ^_^

Related articles: