Importing and exporting txt files in mysql

Import Database: load data infile 'd:/test.txt 'into table nmg fields terminated by', 'Lines terminated by' \ r \ n ';
13.2.5. load data infile syntax load data [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE'File_name. Txt '[REPLACE | IGNORE] INTO TABLE
Tbl_name[FIELDS [terminated'String'] [[OPTIONALLY] enclosed'Char'] [Escaped'Char'] [LINES [starting'String'] [TERMINATED'String']
[IGNORENumberLINES] [(Col_name_or_user_var,...)] [SET
Col_name=Expr,...)]
The load data infile statement is used to quickly read rows from a text file and LOAD a table. The file name must be a text string.
For more information about the Efficiency Comparison of INSERT and load data infile and the acceleration of load data infile, see
Section 7.2.16: "INSERT statement speed"
.
The character set indicated by character_set_database system variables is used to interpret information in the file. The setting of set names and character_set_client does not affect the interpretation of input.
Note: currently, UCS2 data files cannot be loaded.
You can also useMysqlimportThe application loads DATA files. This function is implemented by sending a load data infile statement to the server. The -- local option is used to enableMysqlimportReads data files from the client host. If the client and server support the compression protocol, you can specify the-compress option to improve the performance in the slow network. See
Section 8.10, "mysqlimport: data import program
.
If you use LOW_PRIORITY, the execution of the load data statement is delayed until no other client reads from the table.
If a MyISAM Table meets the conditions for Simultaneous Insertion (that is, the table has idle blocks in the middle) And you specify CONCURRENT for this MyISAM table, when load data is being executed, other threads will re-obtain data from the table. Even if no other thread is using this table at the same time, using this option will slightly affect the performance of load data.
If LOCAL is specified, it is considered to be related to the connected client:
· If LOCAL is specified, the file will be read by the client on the client host and sent to the server. The file will be given a complete path name to specify the exact location. If a relative path name is given, the name is considered as relative to the directory where the client is started.
· If LOCAL is not specified, the file must be located on the server host and be directly read by the server.
When locating files on the server host, the server uses the following rules:
· If an absolute path name is given, the server uses this path name.
· If a relative path name with one or more pilot components is given, the server searches for files relative to the server data directory.
· If a file name without a boot component is given, the server searches for the file in the database directory of the default database.
Note that these rules mean that files named./myfile.txt will be read from the server data directory, and files named myfile.txt will be read from the database directory of the default database. For example, the following LOAD data sentence reads the file data.txt from the db1data warehouse directory because db1 is the current database. Even if the statement explicitly loads the file into the db2 database table, it will also read from the db1 directory.
Mysql>USE db1;Mysql>Load data infile 'data.txt 'into table db2.my _ table;
Note: Use a forward slash to specify the Windows path name instead of a backslash. If you use a backslash, you must use two.
For security reasons, when reading text files on the server, the files must be in the database directory or all readable. In addition, to use load data infile for server files, you must have the FILE permission.
See
Section 5.7.3, "permissions provided by MySQL"
.
Compared to allowing the server to directly read files, the LOCAL speed is slightly slower, because the file content must be sent to the server through the client. However, you do not need the FILE Permission to load local files.
LOCAL runs only when your server and your client are licensed. For example, if-local-infile = 0 is used to startMysqld, LOCAL does not run. See
Section 5.6.4: "load data local Security Issues"
.
If you need to load data to read DATA from a pipe, you can use the following method (Here we LOAD the/directory list into a table ):
Mkfifo/mysql/db/x/xchmod 666/mysql/db/x/xfind/-ls>/mysql/db/x/xmysql-e "load data infile 'X' TABLE x "x
Some input records copy the original records to the unique keyword value. The REPLACE and IGNORE keywords are used to control the operations on these input records.
If REPLACE is specified, the input row replaces the original row (in other words, the same as the original row, with the same value for a primary index or unique index ). See
Section 13.2.6 "REPLACE Syntax"
.
If you specify IGNORE, the original row copied to the input row with the unique keyword value is skipped. If neither of the two options is specified, the running condition depends on whether the LOCAL keyword is specified. If you do not use LOCAL, an error occurs when the keyword value is repeated and the remaining text files are ignored. When LOCAL is used, the default running condition is the same as that when IGNORE is specified. This is because the server cannot stop file transfer during running.
If you want to ignore the foreign key restriction during loading and running, you can send a SET FOREIGN_KEY_CHECKS = 0 statement before executing load data.
If you use load data infile for an empty MyISAM TABLE, all non-unique indexes will be created in an independent batch (for repair table ). When you have many indexes, this usually speeds up load data infile. In general, the load data infile speed is very fast, but in some extreme cases, you can use alter table before loading the file into the TABLE... disable keys to disable load data infile, or use alter table... enable keys re-create an index to make it faster. See
Section 7.2.16: "INSERT statement speed"
.
Load data infile is the complement of SELECT... into outfile. (See
Section 13.2.7 "SELECT Syntax"
.) To write data INTO a file from a table, SELECT... into outfile should be used. To read files and put them back to the table, use load data infile. The syntax of FIELDS and LINES clauses is the same for the two statements. Both clauses are optional, but if both are specified, FIELDS must be located before LINES.
If you specify a FIELDS clause, each sub-sentence (terminated by, [OPTIONALLY] enclosed by and escaped by) is also optional. However, you must specify at least one of them.
If you do not specify the FIELDS clause, the default value is the value when you write the following statement:
Fields terminated by '\ t' enclosed by ''escaped '\\'
If you do not specify the LINES clause, the default value is the value when you write the following statement:
Lines terminated by '\ n' starting''
In other words, when reading input values, the default value causes load data infile to run as follows:
· Find the boundary of the row at the new line.
· The row prefix is not skipped.
· Split rows into fields at the tab.
· You do not want fields to be included in any quotation marks.
· When a tab, new line, or '\' is present before '\', it is understood as a text character that is part of the field value.
On the contrary, when writing output values, the default value causes SELECT... into outfile to run as follows:
· Write tabs between fields.
· Do not include fields in any quotation marks.
· When a tab, new line, or '\' appears in the field value, escape.
· Write a new row at the end of the row.
Note: To write fields escaped by '\', you must specify two backslashes for the value to be read as a single SLR slash.
Note: If you have generated a text file in Windows, you may have to use lines terminated by '\ r \ n' to read the file correctly, because Windows usually uses two characters as a line terminator. Some programs, suchWordPadWhen writing a file, \ r may be used as the line terminator. To read such a file, use lines terminated by '\ R '.
If all the rows you want to read contain a common prefix that you want to ignore, you can use'Prefix_string'To skip the prefix (and the characters before the prefix ). If a row does not contain a prefix, the entire row is skipped. Note:Prefix_stringIt appears in the middle of a row.
Example:
Mysql>Load data infile '/tmp/test.txt'->Into table test lines starting by "xxx ";
With this statement, you can read files that contain the following content:
XXX "row", 1 something XXX "row", 2
And only get the data ("row", 1) and ("row", 2 ).
IgnoreNumberThe lines option can be used to ignore rows at the beginning of a file. For example, you can use ignore 1 lines to skip a start header row containing the column Name:
Mysql>Load data infile '/tmp/test.txt'->Into table test IGNORE 1 LINES;
When you use select... into OUTFILE and load data infile are used to write data into a file from a database, read the file, and return the data to the database, the field-and line-handling options used for two statements must match. Otherwise, load data infile does not correctly understand the file content. Assume that you use Select... into OUTFILE to write a file. The fields are separated by commas:
Mysql>SELECT * into outfile 'data.txt'->Fields terminated ','->
FROM table2;
To read files separated by commas and return results, the correct statement should be:
Mysql>Load data infile 'data.txt 'into table table2->Fields terminated ',';
If you try to read the file using the following statement, it will not run because the command load data infile looks for the TAB between fields:
Mysql>Load data infile 'data.txt 'into table table2->Fields terminated by '\ T ';
The result is probably that each input line is considered as a single field.
Load data infile can also be used to read files obtained from external sources. For example, a file in dBASE format has fields separated by commas and contained in double quotation marks. If each row in the file ends with a new behavior, the statements shown here describe the field-and line-handling options you can use to load the file:
Mysql>Load data infile 'data.txt 'INTO TABLETbl_name->Fields terminated by ', 'enabledby '"'->Lines terminated by '\ n ';
All field-or line-handling options can specify an empty string (''). If the string is not empty, the FIELDS [OPTIONALLY] enclosed by and fields escaped by values must be single characters. The fields terminated by, lines starting by, and lines terminated by values can exceed one character. For example, to write a line ending with a pair of carriage return/line feed, or read a file containing such rows, you should specify a line terminated by '\ r \ n' clause.
If jokes is separated by rows consisting of %, you can perform the following operations to read files containing jokes:
Mysql>Create table jokes->(A int not null AUTO_INCREMENT primary key,->Joke text not null );Mysql>
Load data infile '/tmp/jokes.txt' into table jokes->Fields terminated''->Lines terminated by '\ n % \ n' (joke );
FIELDS [OPTIONALLY] enclosed by is used to control the quotation marks of a field. For (SELECT... into outfile), if you ignore the word OPTIONALLY, all fields are included in the enclosed by string. An example of such output is shown here (using a comma as the field separator ):
"1", "a string", "100.20" "2", "a string containing a, comma", "102.20" "3 ", "a string containing a \" quote "," 102.20 "" 4 "," a string containing a \ ", quote and comma", "102.20"
If OPTINALLY is specified, the enclosed by character is only used to contain values in columns with string data types (such as CHAR, BINARY, TEXT, or ENUM:
1, "a string", 100.202, "a string containing a, comma", 102.203, "a string containing a \" quote ", 102.204," a string containing \", quote and comma ", 102.20
Note: If the enclosed by character appears in the field value, use the escaped by character as the prefix to escape the enclosed by character. In addition, if you specify an empty escaped by value, the output value that cannot be correctly read by load data infile may be generated. For example, if the escape character is null, the previously displayed output value should be as follows. Observe that the Second Field in the fourth row contains a comma after the quotation marks. The quotation marks (incorrectly) are displayed as the end of the field:
1, "a string", 100.202, "a string containing a, comma", 102.203, "a string containing a" quote ", 102.204," a string containing ", quote and comma ", 102.20
For the input value, the enclosed by character is stripped from the end of the field word. (Whether OPTIONALLY is specified or not, it is stripped. OPTIONALLY has no effect on the interpretation of the input value .) If the enclosed by character is preceded BY the escaped by character, it is considered as part of the current field value.
If the field starts with the enclosed by character, when such characters appear, only the followed field or the line terminated by sequence, such characters are considered as the end of a field value. To avoid ambiguity, when an enclosed by character appears in a field value, this character can be repeatedly written and understood as a single character. For example, if enclosed by '"' is specified, use the following method to quote quotation marks:
"The" "BIG" "boss"-> The "BIG" bossThe "BIG" boss-> The "BIG" bossThe "" BIG "" boss-> The "BIG" "boss
Fields escaped by is used to control how to write or read special characters. If the fields escaped by character is not an empty character, you can add a prefix to the following characters in the output:
· Fields escaped by character
· Fields [optionally] enclosed by character
· The first character of fields terminated by and lines terminated by values
· ASCII 0 (the character written after the escape character is actually '0', rather than a byte with a value of 0)
If fields escaped by is a null character, no character is escaped and null is output as null rather than \ n. It is not a good way to specify an empty escape character, especially if the field value of the data contains any character in the just given list, it is not recommended to do so.
For the input value, if the fields escaped by character is not a null character, this character is stripped and the following characters are used as part of the field value. The exception is that the escaped '0' or 'N' (for example, \ 0 or \ n, and the escape character is '\'). These sequences are understood as ascii nul (a zero-value byte) and null. Rules for null processing are described at the back of this section.
For more information about '\'-escape syntax, see
Section 9.1, "text value"
.
In specific circumstances, the field-and line-handling options affect each other:
· If lines terminated by is a null string and fields terminated by is not a Null String, each row ends with fields terminated.
· If the fields terminated by and fields enclosed by values are both null values (''), the fixed row (no separator) format is used. When fixed line format is used, no separator is used between fields (but you can still have a line terminator ). The column value is written and read using the display width of the column. For example, if a column is defined as INT (7), a 7-character field is used to write the column value. When the output is made, the column value is obtained by reading 7 characters.
Lines terminated by is still used to separate rows. If a row does not contain all fields, the remaining columns are set to the default value. If you do not have a line terminator, set it ''. In this case, the text file must contain all fields in each line.
The fixed row format also affects the operation of NULL values, which will be described later. Note: If you are using a multi-Byte Character Set, the fixed specification format does not run.
The operation of the NULL value varies according to the FIELDS and LINES options in use:
· For default FIELDS and LINES values, NULL is written as the \ N field value for output; \ N field value is read as NULL, used for input (assume that the escaped by character is '\').
· If fields enclosed by is not a NULL value, the FIELDS containing NULL words are read as NULL values. This is different from NULL, which is surrounded by fields enclosed by characters. The word is read as the string 'null.
· If fields escaped by is NULL, NULL is written as the word NULL.
· When the regular row format is used (when fields terminated by and fields enclosed by are both null values), null is written as an empty string. Note: When a file is written, the null value and null string in the table cannot be identified because both are written as null strings. If you need to distinguish the two when reading the file and returning it, you should not use a fixed row format.
Load data infile does not support some situations:
· Fixed specifications (fields terminated by and fields enclosed by are both null) and blob or text columns.
· If you specify a delimiter and the Delimiter is the same as other prefixes, load data infile cannot properly understand the input value. For example, the following fields clause may cause problems:
· Fields terminated by '"'enabledby '"'
· If fields escaped by is null, then the value of the field containing fields enclosed by or lines terminated by is followed by the fields terminated by value, which causes the load data infile to stop reading a field or line too early. The reason for this is that load data infile cannot correctly determine where the field or row value ends.
The following example loads all columns in the persondata table:
Mysql>Load data infile 'persondata.txt 'into table persondata;
By default, if no column list is set at the end of the load data infile statement, the input row is expected to contain a field for each column in the table. If you only want to load some columns of a table, you should specify a column list:
Mysql>Load data infile 'persondata.txt'->Into table persondata (col1, col2 ,...);
If the order of fields in the input file is different from that in the table, you must specify a column list. Otherwise, MySQL cannot match the input field with the column in the table.
A column list can contain column names or user variables. The SET clause is supported. This allows you to assign input values to user variables and convert these values before assigning the results to columns.
User variables in the SET clause can be used in multiple ways. The following example uses the first column in the data file to directly use the value of t1.column1. Assign the second column to the user variable before the user variable is used for the t2.column2 value. This variable belongs to a separate operation.
Load data infile 'file.txt 'into table t1 (column1, @ var1) SET column2 = @ var1/100;
The SET clause can be used to provide values that are not from the input file. The following statement sets column3 to the current date and time:
Load data infile 'file.txt 'into table t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;
You can also discard the input value by assigning the input value to a user variable without assigning the variable to the column in the table:
Load data infile 'file.txt 'into table t1 (column1, @ dummy, column2, @ dummy, column3 );
The use of the column/Variable list and SET clause is subject to the following restrictions:
· The Value assignment in the SET clause should contain only the column names on the left of the value assignment operator.
· You can use subqueries on the right of the SET value assignment. If a subquery returns a value that is assigned to a column, this subquery can only be a scalar query. In addition, you cannot use subqueries to select from a table being loaded.
· Rows ignored by the IGNORE clause are not processed in the column/Variable list or SET clause.
· When loading data in fixed row format, user variables cannot be used because user variables do not display width.
When processing an input row, load data splits the row into fields based on the column/Variable list and SET clause, and uses the value. Then, the row is inserted into the table. If there is a before insert or after insert trigger for the table, the trigger is started BEFORE and AFTER the row is inserted.
If an input line contains too many fields, extra fields are ignored and the number of warnings increases.
If one input row contains too few fields, the columns in the table with missing input fields are set to the default value. The default value is
Section 13.1.5, "create table Syntax"
.
If the field value is missing, an empty field value will be understood in different ways:
· For the string type, the column is set to a null string.
· For the numeric type, the column is set to 0.
· For the date and time types, the column is set to "zero" corresponding to this type ". See
Section 11.3, "Date and Time type"
.
If you explicitly assign an empty string to the string type, numeric type, date or time type in an INSERT or UPDATE statement, these values are the same.
The TIMESTAMP column is set to the current date and time only in two cases. One case is that the column has a NULL value (\ N); the other case is (only for the first TIMESTAMP column). When a field list is specified, the TIMESTAMP column is omitted from the field list.
Load data infile treats all input values as strings. Therefore, you cannot use the ENUM or SET column numerical values using the INSERT statement. All ENUM and SET values must be specified as strings.
When the load data infile statement ends, an information string is returned in the following format:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
If you are using c api, you can call the mysql_info () function to obtain information about statements. See
Section 25.2.3.34, "mysql_info ()"
.
When the value is inserted using the INSERT statement or the same situation occurs, a warning is triggered (see
Section 13.2.4, "INSERT Syntax"
). The exception is that when there are too many or too few fields in the input line, load data infile also generates a warning. These warnings are not stored; the number of warnings is only used to indicate whether the operation is good.
You can use show warnings to get the list of the first batch of max_error_count WARNINGS as information about running errors. See
Section 13.5.4.22, "show warnings Syntax"
.

This article is from the ChinaUnix blog. If you want to view the original text, click:Http://blog.chinaunix.net/u1/51541/showart_1003791.html