Batch processing is a non-interactive way of running mysql programs, and you will still use the same commands as you would in mysql. Batch processing is a non-interactive way of running mysql programs, and you will still use the same commands as you would in mysql.
In order to implement batch processing, you redirect a file to the mysql program. First we need a text file. This text file 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 New_Data.txt or 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 above sentences must be grammatically correct and each sentence must end with a semicolon. The above USE command selects the database, and the INSERT command inserts data.
Next we need to import the above file into the database. Before importing, we must confirm that the database is already running, that is, the mysqld process (or service, called "service" under Windows NT, "process" under Unix) is already running.
Then run the following command:
bin/mysql –p < /home/mark/New_Data.sql
Then enter the password as prompted. If there are no errors in the statements in the above file, then the data will be imported into the database.
Use LOAD DATA INFILE on the command line to import data from a file into the database:
Now you may be asking yourself, "Why on earth do I type all these SQL statements into a file and then run them through a program?"
This may seem like a lot of work. Well, you're probably right when you think so. But what if you have log records from all these commands? Now this is great, well, most databases will automatically generate a log of the events recorded in the database. Most of the logs contain original SQL commands that have been used. Therefore, if you cannot export data from your current database to a new mysql database, you can use log and mysql's batch processing features to quickly and easily import your data. Of course, this saves you the trouble of typing.
LOAD DATA INFILE
This is the last method we will introduce to import data into a MySQL database. This command is very similar to mysqlimport, but this method can be used on the mysql command line. This means you can use this command in all programs that use the API. Using this method, you can import the data you want to import into your application.
Before using this command, the mysqld process (service) must be running.
Start the mysql command line:
bin/mysql –p
Enter the password as prompted. 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 own computer into 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 a local file and the server is the server you are logged in to. 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 want to mark it as low priority (LOW_PRIORITY), then MySQL will wait until no one else reads the table before inserting data. You can use the following commands:
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 tables when inserting data. Syntax for replacing duplicate key values:
LOAD DATA LOW_PRIORITY INFILE "/home/mark/data.sql" REPLACE INTO TABLE Orders;
The above sentence may seem a bit clunky, but it puts the keywords in a place your profiler can understand.
The following pair of options describe the file recording format. These options are also available in the mysqlimport tool. They look a little different here. First, the FIELDS keyword must be used. If this keyword is used, the MySQL profiler hopes 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
TERMINATED BY describes the separator character of the field, which is the tab character (t) by default.
ENCLOSED BY describes the bracketing characters of the field. For example, enclose each field in quotation marks.
The escape character described by ESCAPED BY. The default is backslash: ).
The following still uses the previous mysqlimport command example and uses 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 a feature in the LOAD DATA INFILE statement that is not available in the mysqlimport tool:
LOAD DATA INFILE can import files into the database by specified columns.
This feature is very important when we want to import part of the data. For example, when we upgrade from an Access database to a MySQL database, we need to add some columns (columns/fields/fields) to the MySQL database to meet some additional needs.
At this time, the data in our Access database is still available, but because the fields of these data no longer match those in MySQL, the mysqlimport tool can no longer be used. Nonetheless, we can still use LOAD DATA INFILE. 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);
As you can see, we can specify the required fields. These specified fields are still enclosed in parentheses and separated by commas. If you miss any of them, MySQL will remind you.