MySQL data import and export methods and tools introduced in the second

Batching is a non-interactive way to run a MySQL program, and you will still use these commands, just like the commands you use in MySQL.

In order to implement batch processing, you redirect a file into a MySQL program, first we need a text file that contains a text file containing

The same text as the command we entered in MySQL.
For example, we want to insert some data, use a file containing the following text (file name is New_data.sql, of course, we can also be named

New_data.txt and any other legal name, do not necessarily end with the suffix SQL):
Use Meet_a_geek;
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Block");
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Newton");
INSERT into Customers (customer_id, last_name) VALUES (NULL, "Simmons");
Note that the syntax of the above sentences must be correct, and each sentence ends with a semicolon.
The use command above selects the database, and the INSERT command inserts the data.

Here we will import the above file into the database, before importing to confirm that the database is already running, that is, the mysqld process (or

Services, Windows NT is called "Services", and UNIX below is "process") already running.
Then run the following command:
Bin/mysql–p Then follow the prompts to enter the password, if the above file is not an error in the statement, then this data is imported into the database.

The command line uses load data INFILE to import from the file to the database:
Now you might ask yourself, "Why do I have to enter all these SQL statements into a file and then run them through the program?" ”
This seems to require a lot of work. Well, it's probably right that you think so. But if you have a list of all these commands,

What about log records? Now that's great, well, most databases will automatically generate log of event logs in the database. and most log

Contains the original SQL commands that were useful. Therefore, if you cannot export data from your current database to a new MySQL database, make

, you can use log and MySQL batch features to quickly and easily import your data. Of course, this eliminates the typing

of trouble.

LOAD DATA INFILE
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. This means that you can use this command in all programs that use the API. Using this method,

You can import the data you want to import in your application.

Before using this command, the MYSQLD process (service) must already be running.
To start the MySQL command line:
Bin/mysql–p
Enter the password as prompted, and 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 file Data.sql into the table orders, like the Mysqlimport tool, and this command

There are some parameters that can be selected. For example, if you need to import data from your computer to a remote database server, you can use the following

Command for the polygon:
LOAD DATA LOCAL INFILE "C:mydocssql.txt" into TABLE Orders;

The local parameter above indicates that the file is a native file and that the server is the server you are logged on to.
This eliminates the use of FTP to upload files to the server, MySQL for you to complete.
You can also set the priority of the INSERT statement, and if you want to mark it as low priority (low_priority), then MySQL will wait until

When no one else reads the table, the data is inserted. 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 omit duplicate key values in the file and data table when inserting data. Syntax for overriding duplicate key values

：
LOAD DATA low_priority INFILE "/home/mark/data.sql" REPLACE into TABLE Orders;
The sentence above looks a bit clumsy, but it puts the keyword in a place that your profiler can understand.

The following pair of options describe the file format, which is also available in the Mysqlimport tool. They look up here.

Come a little different. First, to use the fields keyword, if you use this keyword, the MySQL profiler would like to see at least one of the following

Options:
TERMINATED by character
Enclosed by character
Escaped by character
These keywords are the same as their arguments, as in Mysqlimport. The
TERMINATED by describes the delimiter for the field, which by default is the tab character ()
Enclosed by describes the enclosed character of a field. For example, enclose each field in quotation marks.
Escaped by describes the escape character. The default is the backslash (backslash:).
The following is still an example of the previous Mysqlimport command, using the load DATA infile statement to import the same file into the database:
LOAD DATA INFILE "/home/mark/orders.txt" REPLACE into TABLE Orders fields TERMINATED by,

enclosed by ";

There is no feature in the LOAD DATA infile statement in a mysqlimport tool:
The LOAD DATA INFILE can import files into the database by the specified columns.
This feature is important when we want to import part of the data. Let's say we're upgrading from an Access database to

MySQL database, you need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs.
At this time, the data in our Access database is still available, but because the columns of this data (field) and MySQL are not

Again, so the Mysqlimport tool is no longer available. However, we can still use the load DATA INFILE, the following

The 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 may specify the required fields. These specified fields are still enclosed in parentheses, separated by commas, such as

If you omit any of them, MySQL will remind you ^_^

Importing data from Microsoft access (imported from Access, slightly)

Transfer from http://www.2cto.com/database/200411/1801.html

MySQL data import and export methods and tools introduced in the second