MySQL Data Import Export tool Mysqlimport Introduction

Source: Internet
Author: User
Tags command line table name
mysql| data 1). Mysqlimport's Syntax Introduction:

Mysqlimport, located in the Mysql/bin directory, 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 into the database and table you specify. Let's say we want to import the data from the file Customers.txt into the table custermers in the database Meet_a_geek:
Mysqlimport 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 manipulate.
The table name in the database is customers, where the data format of the text file must be the same as the record format in the Customers table, or the Mysqlimport command will be faulted. The name of the table is the file string preceding the first period (.) of the import file, 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 example above, only two parameters are used, and no more options are used, and the Mysqlimport options are described below
  
   2). Mysqlimport's common options are:

Options feature
-D or--delete all information in the data table before the new data is imported into the datasheet
-F or--force Mysqlimport will force continued insertion of data regardless of whether an error has been encountered
-I or--ignore mysqlimport skips or ignores rows with the same unique keyword, the data in the import file is ignored.
-L or-lock-tables Lock the table before the data is inserted, which prevents the user's queries and updates from being affected when you update the database.
-R or-replace This option is the opposite of the-I option; This option will replace the record for the same unique key in the representative.
--fields-enclosed-by= Char
Specifies what is enclosed in the record of the data in the text file, in many cases the data is enclosed in double quotes. By default, data is not Fu Guachi by character.
--fields-terminated-by=char
Specifies the separator between the values of each data, and the separator is a period in a file separated by a period. You can specify the separator between data by using this option. The default delimiter is Hop Geff (TAB)
--lines-terminated-by=str
This option specifies a delimited string or character of data between rows and lines in a text file. By default, Mysqlimport is newline as a row separator. You can choose to replace a single character with a string: A new line or a carriage return.
The Mysqlimport command commonly used options include the-v display (version),-p prompts for a password (password), and so on.
  
   3. Example: Import a comma-delimited file

The record format for rows in a file is this:
"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
The order may seem unpleasant, but when you're familiar with it, it's very simple. The first part, Bin/mysqlimport, tells the operating system that the command you want to run is the Mysqlimport in the Mysql/bin directory, and the option P asks for a password, which requires you to enter the password before you change the database, and it will be safer to operate. We used the R option because we wanted to replace the unique keywords in the table with the ones with duplicate unique keywords in the file records to 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 use the R option to replace the records already in the database. The l option is to lock the table when we insert the data, which prevents the user from querying or changing the table while we are updating the table.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.