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

This article mainly introduces the example analysis of MySQL data import and export, the article is very detailed, has a certain reference value, interested friends must read it!

1). Introduction to the grammar of mysql import

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 (text file) into your specified and table. For example, we want to import the data from the file Customers.txt into the table Custermers in the database Meet_A_Geek:

Import Meet_A_Geek Customers.txt

Note: here Customers.txt is the text file where we want to import the data, and Meet_A_Geek is the database we want to operate, and the table name in the database is Customers, where the data format of the text file must be consistent with the record format in the Customers table, otherwise the mysqlimport command will make an error.

Where the name of the table is the first period (.) of the import file before the file string, another example:

Mysqlimport Meet_A_Geek Cus.to.mers.txt

Then we will import the contents of the file into the Cus table in the database Meet_A_Geek.

In the above example, only two parameters are used, and no more options are used. Here are the options for mysqlimport.

2) introduction to common options of .mysqlimport:

Option function

-d or-- delete delete all information in the data table before the new data is imported into the data table

-- for-- force will force data insertion to continue regardless of whether an error is encountered or not

-I or-- ignore mysqlimport skips or ignores those that have the same unique

Keyword, the data in the import file will be ignored.

-l or-lock-tables data locks the table before it is inserted, which prevents

When you update the database, the user's queries and updates are affected.

The-r or-replace option is the opposite of the-I option; this option replaces the

A record with the same unique keyword in the table.

-- fields-enclosed- by= char

Specifies what is enclosed when recording data in a text file, in many cases

The data is enclosed in double quotes. By default, the data is not enclosed in characters.

-- fields-terminated- by=char

Specifies the separator between the values of each data, in a period-delimited file

The delimiter is a full stop. You can use this option to specify a separator between data.

The default delimiter is the jumper (Tab)

-- lines-terminated- by=str

This option specifies a delimited string of data between lines in a text file

Or characters. By default, mysqlimport uses newline as the line delimiter.

You can choose to replace a single character with a string:

A new line or a carriage return.

Other common options for the mysqlimport command are-v display version (version),-p prompt for password (password), and so on.

3)。 Example: import a file with a comma delimiter

The record format of the lines in the file is as follows:

"1", "ORD89876", "1 Dozen Roses", "19991226"

Our task is to import the data from this file into the table Orders in the database Meet_A_Geek

We use this command:

Bin/mysqlimport-prl-fields-enclosed-by= "- fields-terminated-by=, Meet_A_Geek Orders.txt

This command may seem uncomfortable, but when you get familiar with it, it's very simple. The first part, bin/mysqlimport, tells the operating system that the command you want to run is mysqlimport under the mysql/bin directory. Option p requires you to enter a password, which requires you to enter a password before changing the database. We use the r option because we want to replace the unique keyword in the table with the record 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 with the data in the file, so we use the option r to replace the records already in the database. The function of the l option is to lock the table when we insert the data, which prevents users from querying or changing the table when we update the table.

The above is all the contents of the article "sample Analysis of MySQL data Import and Export". Thank you for reading! Hope to share the content to help you, more related 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.