MySQL Data Import Export Method and Tool Description (2-Import from SQL Files) Batch File, import data from SQL file into the database
Translation statement: This article comes from Sam's TEACH YOURSELF MYSQL in 21 Days Book part of the book The unclear place is increasing; if there is a translation or incorrect place, please correct it.
Translator: David Euler, SCU. De_euler-david@yahoo.com.cn Time: 2004/04/24 Yuchuan University
Batch is a way to run the MySQL program, just like you are using the commands you use in MySQL, you will still use these commands.
To achieve batch, you redirect a file to the MySQL program, first we need a text file, this text file contains the same text as the command you entered in MySQL. For example, we have to insert some data, use files containing the following text (file named new_data.sql, of course, we can also name new_data.txt and any other legal name, do not have to end in the end of the 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 The syntax of these sentences must be correct, and each sentence ends in a semicolon. The USE command above selects the database, and the insert command is inserted into the data.
Below we want to import the above files into the database, confirm that the database is already running before importing, that is, the MySQLD process (or service, Windows NT is called "service", UNIX is "Process") is already running. Then run the following command: bin / mysql -p
Use Load Data Infile from the command line to import data from the file to the database: You may ask yourself now, "Why do I want to enter all these SQL statements into the file, then run them through the program?" It seems that it seems to be needed. A lot of work. Very good, you think like this is right. But if you have a log record from all of these commands? This is great now, um, most databases automatically generate logs of event records in the database. Most of the logs contain the original SQL commands used. So if you cannot use data from your current database to the new MySQL database, you can use LOG and MySQL batch features to quickly and easily import your data. Of course, this will save trouble of typing.
Load Data Infile This is a method we want to introduce data to the mysql database. This command is very similar to MySQLIMPORT, but this method can be used in the MySQL command line. That is to say you can use this command in all programs that use the API. With this method, you can import the data you want to import in your application.
Before using this command, MySQLD process (service) must be running. Start the mysql command line: bin / mysql -p Press the password by prompt, successfully enter the mysql command line, enter the following command: use meet_a_geek; load data infile "/home/mark/data.sql" inTo Table ORDERS; simple This will import the contents of the file data.sql into the table order, as the Mysqlimport tool, this command also has some parameters that can be selected. For example, you need to import data on your computer into a remote database server, you can use the following command: load data local infile "c: /mydocs/sql.txt" INTO TABLE ORDERS;
The above Local parameter indicates that the file is a local file, and the server is the server you have logged in. This saves the use of FTP to upload files to the server, MySQL is done for you. You can also set the priority of the insert statement, if you want to mark it as a low priority (low_priority), then MySQL will wait without others When you read this table, you will insert the data. 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 the key values that are repeated in the data table when inserting data. Syntax for repeated key value: load data low_priority infile "/Home/mark/data.sql" Replace Into Table ORDERS; above the sentence looks a bit awkward, but put the keyword in the description of your parser to understand The place.
One pair of options describe the file record format, which is also available in the MySQLIMPORT tool. They look a bit different here. First, use the fields keyword, if you use this keyword, MySQL parsriver wants to see at least one of the following options: Terminated by characterenclosed by Characterescape by Character These keywords with their parameters are the same as the usage in MySQLIMPORT. . Theterminated By describes the separator of the field. By default, the Tab character (/ t) encluded BY describes the character of the field. Each of the fields is included in the quotation marks. Escaped by the escape character described. The default is the opposite bar (backslash: /). An example of the previous mysqlimport command is still used, import the same file into the database with the Load Data Infile statement: loading 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: Load Data Infile can import files into the database in the specified column. This feature is important when we want to import some of the contents of the data. For example, when we need to upgrade from the Access database to the MySQL database, we 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 of these data, the fields of data are no longer matching in MySQL, so I cannot use the MySQLIMPORT tool. Despite this, we can still use Load Data Infile, the following example shows how to import data to the 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 required columns (Fields). These designated fields are still enclosed in parentheses, separated by commas, if you miss any one, MySQL will remind you ^ _ ^
Importing Data from Microsoft Access (imported data from Access, omitted)
