MYSQL - MySQL Data Import and Export methods and tools introduced (1 - myslqimport utility)MYSQL

MySQL Data Import and Export methods and tools introduced (1 - myslqimport utility)

MySQL Data Import and Export methods and tools introduced (1 - myslqimport utility)
Mysqlimport text file import tool introduced

Translation:
This paper content from Sam's Teach Yourself MySQL in 21 Days part of the contents of the book, by Mark Maslakowski
The original English Copyright 2005 original author, a Chinese translation of the part of some additions and deletions; original book about the place too clear cut, stressing the increasing local unclear if the translation is wrong with or without the right place, correction.

Translation: David Euler, SCU. De_euler-david@www.yahoo.com.cn
Time: 2004/04/24 in Sichuan University

1). Mysqlimport syntax description:
Mysqlimport in mysql / bin directory, mysql is a load (or import) data of a very effective tool. This is a command-line tool. There are two parameters, as well as a large number of options to choose from. This tool to a text file (text file) into the database and you specify in the table. For example, we should Customers.txt documents in the database to import data in the table Custermers Meet_A_Geek:
Mysqlimport Meet_A_Geek Customers.txt
NOTE: Customers.txt here is the data we want to import text files, and Meet_A_Geek is that we must operate the database, a table in the database are Customers, where the data format text file with the Customers table in the same recording format, or else mysqlimport orders will be wrong.
Table which is the name of document into a full stop (.) Document in front of the string, another example:
Mysqlimport Meet_A_Geek Cus.to.mers.txt
Then we will import the contents of documents to the database in the Cus Meet_A_Geek table.
In the example above, only two parameters used, and not used more options, the options described below mysqlimport

2). Mysqlimport common options description:
Options function
- D or - delete data into new data in the table to delete information and data in the table all the information
- F or - regardless of whether the force encountered errors, mysqlimport continue to insert data will be mandatory
- I or - or ignore ignore mysqlimport Skip only those who have the same
Keyword trip, the data in the import file will be ignored.
- L or-lock-tables data to be inserted before the locked, and it on the prevention,
You update the database, the user's query and update affected.
- R or-replace this option and - contrary to the role of i options; this option will replace
Table keyword only in the same record.
— Fields enclosed-by-char =
Specified text file in the data record, including what effect, and in many cases
Data with a two-enclosed in quotation marks. The default data circumstances is not enclosed in the character.
— Fields terminated-by-char =
The value of all specified data between the separator in the end the separation of paper,
Separator is full stop. You can use this option to specify data between the separator.
The default separator is tabbing at (Tab)
— Terminated-by-lines = str
This option is specified text file data between the Bank of China in line with a string of separation
Or characters. Mysqlimport default circumstances for a newline to separation.
You can choose to use a string to replace a single character:
A new line or a carriage return.
Mysqlimport orders are commonly used options - v show version (version), p-prompted, enter your password (password).

3). Example: Import a comma to separate the document at the records of the Bank of China format is this:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Our task is to the document inside the data into the database in the form Meet_A_Geek Orders,
We use this command:
Bin / prl mysqlimport—enclosed fields-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt
This may seem veryä¸çˆ½orders, but when you familiar, it is very simple. The first part, bin / mysqlimport, you have to tell the operating system running order is mysql / bin directory of mysqlimport, p option is to require enter a password, this requires changes in the database before you enter a password, it would operate more safely. We spent r option because we want to bring in the table only keywords, and documented in the records of repeat keywords only replace the data in the document. Our form is not the latest data, the need to use the data in the document to update, which will use r this option, the alternative has some records in the database. L option is the role of data in the time we inserted locked, and it has to stop users when we update the table on the table for the operation or changes.

Bookmark it: These icons link to social bookmarking sites where readers can share and discover new web pages.