MySQL: load data infil

Load data infile "/home/mark/data. SQL" into Table orders;

To put it simply, the content in the file data. SQL will be imported into the table orders. Like the mysqlimport tool, this command also has some optional parameters. For example, if you want to import data from your computer to a remote database server, run the following command:

Load data local infile "C:/mydocs/SQL .txt" into Table orders;

The local parameter above indicates that the file is a local file, and the server is the server you log on. In this way, you can use ftp to upload files to the server, and MySQL completes the process for you. you can also set the priority of the insert statement. If you want to mark it as low_priority, MySQL will wait until no one else reads the table, insert 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 the file and data table when inserting data. Syntax to replace duplicate key values:

Load data low_priority infile "/home/mark/data. SQL" replace into Table orders;

The above sentence looks a little clumsy, but it puts the keywords in a place that your parser can understand.

The following options describe the file record format. These options are also available in mysqlimport. They look a little different here. First, you need to use the fields keyword. If you use this keyword, the MySQL parser wants to see at least one of the following options:

Terminated by character

Enclosed by character

Escaped by character

These keywords have the same usage as their parameters in mysqlimport. The terminated by delimiter that describes the field, which is a Tab character (/T) by default)

Enclosed by describes the starting characters of a field. For example, enclose each field in quotation marks.

Escape characters described by escaped. The default value is backslash :/).

The following example uses the mysqlimport command to import the same file to the database using the load data infile statement:

Load data infile "/home/mark/orders.txt" replace into Table orders fields terminated by ', 'enabledby '"';

In the load data infile statement, mysqlimport has no features:

Load data infile can import files to the database according to the specified columns. This feature is important when we want to import part of the data. For example, when upgrading from an Access database to a MySQL database, you need to add some columns (column/field) to the MySQL database to meet some additional needs.

At this time, the data in our access database is still available, but because the columns (fields) of the data are no longer matched with those in MySQL, The mysqlimport tool cannot be used. Even so, we can still use load data infile. The following example shows how to import data to a specified field:

Load data infile "/home/order.txt" into Table orders (order_number, order_date, customer_id );

As you can see, we can specify the required topic (fields ). The specified fields are still enclosed in parentheses and separated by commas (,).