MySQL Import Data

MySQL Import Data

There are two simple ways to import MySQL-exported data in MySQL.

Importing data using LOAD data

The load data infile statement is provided in MySQL to insert the information. The following instance will read the file Dump.txt from the current directory, inserting the data from the file into the Mytbl table in the current database.

MySQL>' dump.txt ' into TABLE mytbl;   

If you specify a local keyword, it indicates that the file is read by path from the client host. If not specified, the file reads the file on the server by path.

You can explicitly indicate the delimiter and end-of-line tags of the column values in the load data statement, but the default tags are locators and line breaks.

The syntax for the fields and LINES clauses of the two commands is the same. Two clauses are optional, but if two are specified at the same time, the fields clause must appear before the LINES clause.

If the user specifies a fields clause, its clauses (TERMINATED by, [optionally] enclosed by and escaped by) are also optional, but the user must specify at least one of them.

MySQL> 'dump.txt ' into TABLE mytbl  , ': '\ r \ n ';    

LOAD data is inserted by default in the order of the columns in the data file, and if the columns in the data file are inconsistent with the columns in the inserted table, you need to specify the order of the columns.

For example, the column order in the data file is A,b,c, but the column order of the inserted table is b,c,a, the data import syntax is as follows:

MySQL>' dump.txt '     (b, C, a);     

Importing data using Mysqlimport

The Mysqlimport client provides a command-line interface for the load DATA INFILEQL statement. Most options for mysqlimport correspond directly to the load DATA infile clause.

To import data from the file dump.txt into the MYTBL data table, you can use the following command:

---localdump.  *****

The Mysqlimport command can specify options for setting the specified format, and the command statement format is as follows:

----Local--fields-terminated-by=":"--lines -terminated-by="\ r \ n"dump.  ***** 

Use the--columns option in the Mysqlimport statement to set the order of the columns:

----Local--Columns=b,c,dump.  *****

Introduction to the common options for Mysqlimport

options	functions
-D or--delete	new Delete all information in data table before data Import data table
-F or--force	regardless of whether or not an error is encountered, Mysqlimport forces the data to continue to be inserted
-I or--ignore	mysqlimport skips or ignores rows that have the same unique keyword, and the data in the import file is ignored.
-L or-lock-tables	data is inserted before locking the table, which prevents you from being affected by the user's queries and updates when you update the database.
-R or-replace	This option is the opposite of the-I option, and this option will replace the record with the same unique keyword in the representative.
--fields-enclosed-by= Char	specifies what is included in the data in a text file, and in many cases the data is enclosed in double quotation marks. By default, the data is not surround by the word.
--fields-terminated-by=char	Specifies the delimiter between the values of each data, in a period-delimited file, where the delimiter is a period. You can use this option to specify a delimiter between data. The default delimiter is Jump Geff (Tab)
--lines-terminated-by=str	This option specifies the delimited string or character of data between rows and lines in a text file. By default, Mysqlimport with newline as the line delimiter. You can choose to replace a single character with a string: A new line or a carriage return.

The common options for the Mysqlimport command are the-v display (version), the-P prompt for a password (password), and so on.

MySQL Import Data