MySQL Data Import and Export methods and tools 2

Batch processing is a non-interactive way to run mysql programs. Like the commands you use in mysql, you will still use these commands.

To implement batch processing, you need to redirect a file to the mysql program. First, we need a text file, which contains

The same text as the command we entered in mysql.
For example, we want to insert some data and use a file containing the following text (the file name is New_Data. SQL, of course, we can also name it

New_Data.txt and any other valid names do not have to end with SQL ):
USE Meet_A_Geek;
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Block ");
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Newton ");
Insert into MERs (Customer_ID, Last_Name) VALUES (NULL, "Simmons ");
Note that the syntax of these sentences must be correct, and each sentence ends with a semicolon.
Select the database for the above USE command, and INSERT the command to INSERT data.

Next, we need to import the above files to the database. Before importing, we need to confirm that the database is running, that is, the mysqld process (or

Service, which is called "service" in Windows NT and "process" in unix) is already running.
Then run the following command:
Bin/mysql-p Enter the password as prompted. If the statements in the above file are correct, the data will be imported to the database.

Use load data infile in the command line to import DATA from the file to the database:
Now you may ask yourself, "Why do I need to input all these SQL statements into the file and then run them through the program ?"
This seems to require a lot of work. That's good. You may be right. However, if you generate

What about log records? This is great now. Well, most databases will automatically generate the log of event records in the database. While most log

Contains the original SQL commands that have been used. Therefore, if you cannot export data from your current database to the new mysql database

You can use the batch processing feature of log and mysql to import your data quickly and conveniently. Of course, this saves typing.

.

LOAD DATA INFILE
This is the last method to import data to the MySQL database. This command is very similar to mysqlimport, but this

The method can be used in the mysql command line. That is to say, you can use this command in all the programs that use the API. Using this method,

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

Before using this command, the mysqld process (service) must be running.
Start mysql command line:
Bin/mysql-p
Enter the password as prompted. after entering the mysql command line, enter the following command:
USE Meet_A_Geek;
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 will also

There are some optional parameters. For example, if you want to import data from your computer to a remote database server, you can use

Command:
Load data local infile "C: MyDocsSQL.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

When no one else reads this 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 ignore duplicate key values in the file and data table when inserting data. Replace the syntax of 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 can see

Is a little different. 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 used to describe the field. BY default, it is a tab character ()
Enclosed by describes the starting characters of a field. For example, enclose each field in quotation marks.
Escape characters described by escaped. 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,

Enclosed ";

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, we want to upgrade from the Access database

Some columns (columns/fields) need to be added to the MySQL database to meet some additional requirements.
At this time, the data in our Access database is still available, but because the columns (fields) of the data are different from those in MySQL

Therefore, you cannot use the mysqlimport tool. Even so, we can still use load data infile, the following

The 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 (,). For example

If you omit any of them, MySQL will remind you

Importing Data from Microsoft Access (import Data from Access, omitted)