Fast MySQL import and export data (load data and outfile)

1.load Data:

Practical application: Load the XLS file generated by the log into MySQL:

Mysql_cmd ="iconv-c-F utf-8-t GBK./data/al_ver_"+ Yesterday_time +". Xls-o./data/gbk_al_ver_"+ Yesterday_time +". xls"Print(Mysql_cmd) Os.system (mysql_cmd) Mysql_cmd="mysql-h60.28.200.78-uroot-pyeelion-a logstat_rt-e \ "Load data local infile \"./data/gbk_al_ver_"+ Yesterday_time +". xls ' into table hivedata_508\ '"Print(Mysql_cmd) Os.system (mysql_cmd)

The code above is transcoded before load, Iconv usage: http://www.cnblogs.com/leezhxing/p/3929435.html

Basic syntax:

Load data  'file_name txt' [replace | ignore]into table tbl_name[fields[terminated by't' (][escaped by ' \ ')] [ Lines terminated by'n'][ignore number lines][(Col_name,   )]

  

The OAD data infile statement reads from a text file into a table at a high speed. Before using this command, the MYSQLD process (service) must already be running. For security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load data infile on the files on the server, you must have file permissions on the server host.

1 If you specify the keyword low_priority, then MySQL will wait until no one else reads the table before inserting the data. You can use the following command:

Load data low_priority infile "/home/mark/data sql" into table Orders;

2 If the local keyword is specified, the file is read from the client host. If local is not specified, the file must be located on the server.

3 Replace and ignore keyword controls duplicate processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored. For example:

Load data low_priority infile "/home/mark/data SQL" replace into table Orders;

4 separators

(1) The fields keyword specifies the segmentation format of the file segment, and if this keyword is used, the MySQL profiler wants to see at least one of the following options:

Terminated by delimiter: What characters are used as separators

Enclosed by field character

Escaped by escape character

Terminated by describes the delimiter for the field, which by default is the tab character (\ t)

Enclosed by describes the enclosed character of a field.

Escaped by describes the escape character. The default is a backslash (backslash:\)

For example: Load data infile "/home/mark/orders txt" replace into table Orders fields terminated by ', ' enclosed by ' ";

(2) lines keyword specifies the separation of each record defaults think ' \ n ' is a line break

If all two fields are specified, that field must precede lines. If you do not specify the fields keyword default value with the same as if you write: Fields terminated by ' \ t ' enclosed by ' \ ' escaped by ' \ \ '

If you do not specify a lines clause, the default value is the same as if you write: lines terminated by ' \ n '

For example: Load data infile "/jiaoben/load.txt" replace into table test fields terminated by ', ' lines terminated by '/n ';

5 Load Data infile can import files into the database by the specified columns. When we want to import part of the data, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. For example, when we were upgrading from an Access database to a MySQL database

The following example shows how to import data into a specified column (field):

Load data infile "/home/order txt" into table Orders (Order_number, order_date, customer_id);

6 when looking for files on a server host, the server uses the following rules:

(1) If an absolute pathname is given, the server uses that path name.

(2) If a relative pathname with one or more predecessor parts is given, the server searches for files relative to the server's data directory.

(3) If you give a file name that does not have a predecessor, the server looks for the file in the database directory of the current database.

For example:/MyFile txt "The file is read from the server's data directory, and a file given as" MyFile txt "is read from the database directory of the current database.

2.outfile

SELECT * from Table to outfile '/path/filename '
Fields terminated by ', '
Enclosed by ' "'
Lines terminated by ' \ r \ n '

Fields clause: There are three Yazi sentences in the fields clause: TERMINATED by, [optionally] enclosed by and escaped by. If you specify a fields clause, you must specify at least one of these three Yazi sentences.

TERMINATED by is used to specify the symbol between field values, for example, "TERMINATED by", "" specifies a comma as a flag between two field values.

The enclosed by clause is used to specify the symbol for the character value in the package file, for example, "enclosed by" "means that the character value in the file is placed between the double quotation marks, and the keyword optionally means that all values are placed between the double quotation marks.

The escaped by clause is used to specify the escape character, for example, "escaped by" specifies "*" as an escape character, instead of "\", such as a space that is represented as "*n".

LINES clause: In the LINES clause, use TERMINATED by specifying a line to end the flag, such as "LINES TERMINATED by '?" Represents a row with "?" As the end flag.

Fast MySQL import and export data (load data and outfile)