Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use batch processing to achieve MySQL data import and export", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "how to use batch processing to achieve MySQL data import and export" this article.

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

To implement batch processing, if you redirect a file to the mysql program, we first need a text file that contains

The same text as the command we entered in mysql.

For example, if we want to insert some data, use a file containing the following text (the file name is New_Data.sql, of course, we can also name it as

New_Data.txt and any other legal names do not have to be suffixed with the end of 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, and the INSERT command inserts data.

Next, we want to import the above file into the database, and make sure that the database is already running before import, that is, the d process (or

Services, referred to as "services" in Windows NT and "processes" under unix), are already running.

Then run the following command:

Bin/mysql-p < / home/mark/New_Data.sql

Then enter the password as prompted, and if there are no errors in the statements in the above file, then the data is imported into the database.

Use LOAD DATA INFILE on the command line to import data from a file to the database:

Now you might ask yourself, "Why on earth should I enter all these SQL statements into a file and run them through the program?"

It looks like it takes a lot of work. Well, you're probably right to think so. But if you have something that comes from all these commands,

What about the log records? Now that's great, well, most databases automatically generate log for event records in the database. And most log

All contain raw SQL commands that have been used. Therefore, if you cannot export data from your current database to a new my,

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

The 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. That means you can use this command in all programs that use API. Use 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.

Start the mysql command line:

Bin/mysql-p

Enter the password when 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

In a nutshell, this will import the contents of the file data.sql into the table Orders, like the mysqlimport tool, this command also

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

Commands for faces:

LOAD DATA LOCAL INFILE "C:MyDocsSQL.txt" INTO TABLE Orders

The above LOCAL parameter indicates that the file is a local file and the server is the server you logged in to.

This eliminates the need to use ftp to upload files to the server, and MySQL does it for you.

You can also set the priority of the insert statement, and if you want to mark it as low priority (LOW_PRIORITY), MySQL will wait until

Insert the data only when no one else reads the table. 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 data tables when inserting data. Syntax that replaces 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 pair of options describe the record format of the file, which are also available in the mysqlimport tool. They watch from here.

It's a little different. First, the FIELDS keyword is used, and if this keyword is used, the MySQL profiler wants to see at least one of the following

Options:

TERMINATED BY character

ENCLOSED BY character

ESCAPED BY character

These keywords and their parameters are used the same as in mysqlimport. The

The delimiter of the TERMINATED BY description field, which is the tab character () by default

ENCLOSED BY describes the bracketed characters of a field. For example, enclose each field in quotation marks.

The escape character of the ESCAPED BY description. The default is the reverse bar (backslash:).

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

LOAD DATA INFILE "/ home/mark/Orders.txt" REPLACE INTO TABLE Orders FIELDS TERMINATED BY

ENCLOSED BY "

There is a mysqlimport tool in the LOAD DATA INFILE statement that has no features:

LOAD DATA INFILE can import files into the database in the specified columns.

This feature is important when we want to import part of the data. For example, we need to upgrade from the Access database to

When MySQL database, you need to add some columns (columns / fields / 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 field of the data is not the same as that in MySQL

Match again, so the mysqlimport tool can no longer be used. However, we can still use LOAD DATA INFILE, which is as follows

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 can specify the desired column (fields). These specified fields are still enclosed in parentheses and separated by commas, as shown in

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

Importing Data from Microsoft Access (import data from Access, briefly)

The above is all the contents of the article "how to use batch processing to import and export MySQL data". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.