Latest web development tutorials

MySQL data import

MySQL can be used in two easy ways to import MySQL data export.

Import data using the LOAD DATA

MySQL provides a LOAD DATA INFILE statement to insert the data. The following examples from the current directory to read files dump.txt, the data file into the current mytbl table in the database. 

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

If you specify LOCAL keyword, then the path by reading files from the client host. If not specified, the files on the server by reading the file path.

Can you clear that in the LOAD DATA statement separator and end of line marker column value, but the defaults are, and newline.

The syntax of the two commands FIELDS and LINES clauses is the same. Two clauses are optional, but if the two are simultaneously specified, FIELDS clause must appear before LINES clause.

If you specify a FIELDS clause, which clause (TERMINATED BY, [OPTIONALLY] ENCLOSED BY and ESCAPED BY) is also optional, however, the user must specify at least one of them. 

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA default data is inserted in the order of the columns in the data file, and if not a column in the data file and insert a column in the table, you need to specify the order of columns.

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

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

Import data using mysqlimport

mysqlimport client provides a command LOAD DATA INFILEQL statement line interface. Most options to mysqlimport correspond directly to the LOAD DATA INFILE clause.

From the File dump.txt mytbl will import data into the data table, you can use the following command: 

$ mysqlimport -u root -p --local database_name dump.txt
password *****

mysqlimport command to specify the option to set the specified format command statement format is as follows: 

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

mysqlimport statement --columns use option to set the order of columns: 

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

Introduction of common options mysqlimport

Options Features
-d or --delete Delete all information and data in the data table before the new data import data in the table
-f or --force Regardless of whether an error is encountered, mysqlimport forces continue to insert data
-i or --ignore mysqlimport skip or ignore those who have the same unique Keyword line, the data in the import file will be ignored.
-l or -lock-tables Locked tables before data is inserted, thus prevented, you update the database, the user's query and update affected.
-r or -replace The role of this option with the -i option contrary; this option will have the same unique key record for delegates.
--fields-enclosed- by ​​= char What enclosed, and in many cases the data to double quotes when recording data in the specified text file. By default data is not enclosed in the character.
--fields-terminated- by ​​= char Specify the delimiter between values ​​of each data in the period-separated file, a separator is a full stop. You can use this option to specify the separator between the data. The default delimiter is tabbing character (Tab)
--lines-terminated- by ​​= str This option specifies the character string or delimited text file data between the BOC and the line. By default mysqlimport to newline as a line separator. You can choose to use a string to replace a single character: a new line or a carriage return.

mysqlimport command common options as well -v show version (version), -p prompted for a password (password) and so on.