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

This article mainly explains "how to use batch processing for MySQL data operation", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "how to use batch processing to perform data operations on MySQL"!

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

To implement batch processing, if you redirect a file into mysql, first we need a text file containing the same text as the command we typed 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 New_Data.txt and any other legal name, and it does not have to 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 these sentences above must be correct and that each sentence ends with a semicolon. The USE command above selects and the INSERT command inserts data.

Below we want to import the above file into the database, before importing to confirm that the database is already running, that is, the d process (or service, Windows NT called "service" below, unix below "process") has been running.

Then run the following command:

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

Then press the prompt to enter the password. If there are no errors in the statements in the above file, then the data is imported into the database.

Import data from a file to a database using LOAD DATA INFILE on the command line:

Now you might ask yourself,"Why on earth would I type all these SQL statements into a file and then run them programmatically? "

This seems like a lot of work. Well, you're probably right. But what if you had logs generated from all these commands? Now that's great, well, most databases automatically generate logs of events recorded in the database. Most logs contain original SQL commands that have been used. So if you can't export data from your existing database to your new my, you can import your data quickly and easily using the batch processing features of log and mysql. Of course, this saves the trouble of typing.

LOAD DATA INFILE

This is the last method we'll cover for importing data into a MySQL database. This command is very similar to mysqlimport, but this method can be used from 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 into your application.

Before you can use this command, the mysqld process (service) must already be running.

Start mysql command line:

bin/mysql -p

Press the prompt to enter the password. 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, this command also has some optional parameters. For example, if you need to import data from your computer to a remote database server, you can use the following command:

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

The LOCAL parameter above indicates that the file is local and the server is the server you are logged on. This eliminates the need to use ftp to upload files to the server, MySQL does it for you.

You can also set the priority of the insert statement, if you mark it as LOW_PRIORITY, MySQL will wait until no one else reads the table before inserting data. You can use the following command:

LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" INTO TABLE Orders;

You can also specify whether duplicate key values in files and data tables are replaced or ignored when inserting data. Syntax for replacing duplicate key values:

LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" REPLACE INTO TABLE Orders;

The sentence above may seem awkward, but it places keywords where your parser can understand them.

The following pair of options describes the file record format and 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 expects 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 way as in mysqlimport. The

TERMINATED BY Separator for description field, tab character ( ) by default

ENCLOSED BY describes the enclosing character of the yes field. For example, enclose each field in quotation marks.

Escape character for ESCAPED BY description. The default is backslash: .

The following is an example of importing the same file into the database using the LOAD DATA INFILE statement, still using the previous mysqlimport command:

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

There is a mysqlimport feature in the LOAD DATA INFILE statement that is not found in the mysqlimport tool:

LOAD DATA INFILE can import files into a database by specified columns.

This feature is important when we want to import parts of the data. For example, when upgrading from an access database to a MySQL database, we need to add some columns (columns/fields/fields) to the MySQL database to accommodate some additional needs.

At this point, the data in our Access database is still available, but because the fields of this data no longer match those in MySQL, the mysqlimport tool can no longer be used. Nevertheless, we can still use LOAD DATA INFILE, and the following example shows how to import data into a specified field:

LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date, Customer_ID);

At this point, I believe that everyone has a deeper understanding of "how to use batch processing to operate MySQL data", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.