MySQL Data Import and Export methods and tools mysqlimport (1)

1. mysqlimport syntax introduction:

Mysqlimport is located in the mysql/bin directory and is a very effective tool for mysql to load or import data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports a text file to your specified database and table. Finally, we want to import data from the customers.txt file to the Custermers table in the Meet_A_Geek database:
Mysqlimport Meet_A_Geek Customers.txt

Note: customers.txt is the text file for data import, while Meet_A_Geek is the database to be operated, and the table name in the database is Customers, the data format of the text file must be the same as that of the record in the MERs table. Otherwise, the mysqlimport command will fail.

The table name is the first full stop of the imported file.) The preceding file string. Another example is as follows:
Mysqlimport Meet_A_Geek Cus.to.mers.txt
Then we will import the content in the file to the Cus table in the database Meet_A_Geek.

In the above example, only two parameters are used and no more options are used. The following describes the options of mysqlimport.

2. Introduction to common mysqlimport options:

Option
-D or -- delete all information in the data table before the new data is imported into the data table.
-F or -- force: No matter whether an error occurs or not, mysqlimport will force data insertion to continue.
-I or -- ignore mysqlimport skip or ignore those with the same unique

The data in the imported file is ignored.
-L or-lock-tables: The table is locked before data is inserted. This prevents you from affecting your queries and updates when updating the database.
-R or-replace is opposite to-I. This option replaces records with the same unique keywords in the table.
-- Fields-enclosed-by = char specifies the data record in a text file. In many cases, the data is enclosed by double quotation marks. By default, data is not enclosed by characters.
-- Fields-terminated-by = char specifies the delimiter between values of each data. In a file separated by periods, the Delimiter is a period. You can use this option to specify the delimiter between data. The default Delimiter is the Tab)
-- Lines-terminated-by = str this option specifies the Data Separator string or character between the row and row in a text file. By default, mysqlimport uses newline as the line separator. You can choose to use a string to replace a single character: a new line or a carriage return.
Common options of mysqlimport include-v display version),-p prompt input password), and so on.

3. Example: import a file with a comma as the Separator

The record format of the file row is as follows:
"1", "ORD89876", "1 Dozen Roses", "19991226"

Our task is to import the data in this file to the table Orders in the database Meet_A_Geek. We use this command:
Bin/mysqlimport-Prolactin-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt
This command may seem uncomfortable, but it is very simple when you are familiar with it. The first part is bin/mysqlimport, which tells the operating system that the command you want to run is mysqlimport In the mysql/bin directory. Option p requires a password, this requires you to enter the password before changing the database, which makes the operation safer. We use the r option because we want to replace the record with a unique keyword that already exists in the file record with the data in the file. The data in our form is not up-to-date and needs to be updated using the data in the file. Therefore, we use the r option to replace the existing records in the database. L option is used to lock the table when we insert data, which prevents users from querying or changing the table when we update the table.

Batch Processing imports files and imports data from SQL files to databases. batch processing is a non-interactive way to run mysql programs, just 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 containing the same text as the command we entered in mysql. // Www.w3sky.com

The suffix and any other legal 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.