MySQL的文字匯入之load data local

lhrbest發表於2019-09-04
MySql

MySQL的文字匯入load data local


資料檔案格式：

Aeschylus	time as he grows old teaches many lessons
Alexander Graham Bell	Mr.Watson,come here.i want you!
Benjamin Franklin	it is hard for an empty bag to stand upright
Benjamin Franklin	little strokes fell great oaks
--欄位之間是tab，其它是空格

匯入命令： 

drop table aa;
create table aa(a varchar(40),tt text);
load data local infile 'a.txt' into table aa;
select * from aa;

過程： 

root@localhost[lhrdb]> drop table aa;
Query OK, 0 rows affected (0.17 sec)
root@localhost[lhrdb]> create table aa(a varchar(40),tt text);
Query OK, 0 rows affected (0.30 sec)
root@localhost[lhrdb]> load data local infile 'a.txt' into table aa;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
root@localhost[lhrdb]> select * from aa;
+-----------------------+----------------------------------------------+
| a                     | tt                                           |
+-----------------------+----------------------------------------------+
| Aeschylus             | time as he grows old teaches many lessons    |
| Alexander Graham Bell | Mr.Watson,come here.i want you!              |
| Benjamin Franklin     | it is hard for an empty bag to stand upright |
| Benjamin Franklin     | little strokes fell great oaks               |
+-----------------------+----------------------------------------------+
4 rows in set (0.00 sec)


幫助：

root@localhost[lhrdb]> help load data
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
The LOAD DATA INFILE statement reads rows from a text file into a table
at a very high speed. LOAD DATA INFILE is the complement of SELECT ...
INTO OUTFILE. (See
http://dev.mysql.com/doc/refman/5.7/en/select-into.html.) To write data
from a table to a file, use SELECT ... INTO OUTFILE. To read the file
back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and
LINES clauses is the same for both statements. Both clauses are
optional, but FIELDS must precede LINES if both are specified.
You can also load data files by using the mysqlimport utility; it
operates by sending a LOAD DATA INFILE statement to the server. The
--local option causes mysqlimport to read data files from the client
host. You can specify the --compress option to get better performance
over slow networks if the client and server support the compressed
protocol. See http://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html.
For more information about the efficiency of INSERT versus LOAD DATA
INFILE and speeding up LOAD DATA INFILE, see
http://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html.
The file name must be given as a literal string. On Windows, specify
backslashes in path names as forward slashes or doubled backslashes.
The character_set_filesystem system variable controls the
interpretation of the file name.
LOAD DATA supports explicit partition selection using the PARTITION
option with a comma-separated list of one or more names of partitions,
subpartitions, or both. When this option is used, if any rows from the
file cannot be inserted into any of the partitions or subpartitions
named in the list, the statement fails with the error Found a row not
matching the given partition set. For more information, see
http://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.
For partitioned tables using storage engines that employ table locks,
such as MyISAM, LOAD DATA cannot prune any partition locks. This does
not apply to tables using storage engines which employ row-level
locking, such as InnoDB. For more information, see
http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking
.html.
The server uses the character set indicated by the
character_set_database system variable to interpret the information in
the file. SET NAMES and the setting of character_set_client do not
affect interpretation of input. If the contents of the input file use a
character set that differs from the default, it is usually preferable
to specify the character set of the file by using the CHARACTER SET
clause. A character set of binary specifies "no conversion."
LOAD DATA INFILE interprets all fields in the file as having the same
character set, regardless of the data types of the columns into which
field values are loaded. For proper interpretation of file contents,
you must ensure that it was written with the correct character set. For
example, if you write a data file with mysqldump -T or by issuing a
SELECT ... INTO OUTFILE statement in mysql, be sure to use a
--default-character-set option so that output is written in the
character set to be used when the file is loaded with LOAD DATA INFILE.
*Note*:
It is not possible to load data files that use the ucs2, utf16,
utf16le, or utf32 character set.
If you use LOW_PRIORITY, execution of the LOAD DATA statement is
delayed until no other clients are reading from the table. This affects
only storage engines that use only table-level locking (such as MyISAM,
MEMORY, and MERGE).
If you specify CONCURRENT with a MyISAM table that satisfies the
condition for concurrent inserts (that is, it contains no free blocks
in the middle), other threads can retrieve data from the table while
LOAD DATA is executing. This option affects the performance of LOAD
DATA a bit, even if no other thread is using the table at the same
time.
With row-based replication, CONCURRENT is replicated regardless of
MySQL version. With statement-based replication CONCURRENT is not
replicated prior to MySQL 5.5.1 (see Bug #34628). For more information,
see
http://dev.mysql.com/doc/refman/5.7/en/replication-features-load-data.h
tml.
The LOCAL keyword affects expected location of the file and error
handling, as described later. LOCAL works only if your server and your
client both have been configured to permit it. For example, if mysqld
was started with the local_infile system variable disabled, LOCAL does
not work. See
http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html.
The LOCAL keyword affects where the file is expected to be found:
o If LOCAL is specified, the file is read by the client program on the
  client host and sent to the server. The file can be given as a full
  path name to specify its exact location. If given as a relative path
  name, the name is interpreted relative to the directory in which the
  client program was started.
  When using LOCAL with LOAD DATA, a copy of the file is created in the
  server's temporary directory. This is not the directory determined by
  the value of tmpdir or slave_load_tmpdir, but rather the operating
  system's temporary directory, and is not configurable in the MySQL
  Server. (Typically the system temporary directory is /tmp on Linux
  systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for
  the copy in this directory can cause the LOAD DATA LOCAL statement to
  fail.
o If LOCAL is not specified, the file must be located on the server
  host and is read directly by the server. The server uses the
  following rules to locate the file:
  o If the file name is an absolute path name, the server uses it as
    given.
  o If the file name is a relative path name with one or more leading
    components, the server searches for the file relative to the
    server's data directory.
  o If a file name with no leading components is given, the server
    looks for the file in the database directory of the default
    database.
In the non-LOCAL case, these rules mean that a file named as
./myfile.txt is read from the server's data directory, whereas the file
named as myfile.txt is read from the database directory of the default
database. For example, if db1 is the default database, the following
LOAD DATA statement reads the file data.txt from the database directory
for db1, even though the statement explicitly loads the file into a
table in the db2 database:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
Non-LOCAL load operations read text files located on the server. For
security reasons, such operations require that you have the FILE
privilege. See
http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html. Also,
non-LOCAL load operations are subject to the secure_file_priv system
variable setting. If the variable value is a nonempty directory name,
the file to be loaded must be located in that directory. If the
variable value is empty (which is insecure), the file need only be
readable by the server.
Using LOCAL is a bit slower than letting the server access the files
directly, because the contents of the file must be sent over the
connection by the client to the server. On the other hand, you do not
need the FILE privilege to load local files.
LOCAL also affects error handling:
o With LOAD DATA INFILE, data-interpretation and duplicate-key errors
  terminate the operation.
o With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key
  errors become warnings and the operation continues because the server
  has no way to stop transmission of the file in the middle of the
  operation. For duplicate-key errors, this is the same as if IGNORE is
  specified. IGNORE is explained further later in this section.
The REPLACE and IGNORE keywords control handling of input rows that
duplicate existing rows on unique key values:
o If you specify REPLACE, input rows replace existing rows. In other
  words, rows that have the same value for a primary key or unique
  index as an existing row. See [HELP REPLACE].
o If you specify IGNORE, rows that duplicate an existing row on a
  unique key value are discarded. For more information, see
  http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#ignore-strict-co
  mparison.
o If you do not specify either option, the behavior depends on whether
  the LOCAL keyword is specified. Without LOCAL, an error occurs when a
  duplicate key value is found, and the rest of the text file is
  ignored. With LOCAL, the default behavior is the same as if IGNORE is
  specified; this is because the server has no way to stop transmission
  of the file in the middle of the operation.
URL: http://dev.mysql.com/doc/refman/5.7/en/load-data.html


官網：

https://dev.mysql.com/doc/refman/5.7/en/load-data.html


基本語法：
load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

load data infile 語句從一個文字檔案中以很高的速度讀入一個表中。 使用這個命令之前，mysqld程式（服務）必須已經在執行。 為了安全原因，當讀取位於伺服器上的 文字檔案時，檔案必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上檔案使用 load data infile ，在伺服器主機上你必須有 file 的許可權。
1   如果你指定關鍵詞 low_priority ， 那麼MySQL將會等到沒有其他人讀這個表的時候，
才把插入資料。可以使用如下的命令： 
load data  low_priority infile "/home/mark/data sql" into table Orders;
 
2   如果指定 local 關鍵詞，則表明從客戶主機讀檔案。如果 local 沒指定，檔案必須位於伺服器上。
 
3  replace 和 ignore 關鍵詞控制對現有的唯一鍵記錄的重複的處理。如果你指定 replace ，
新行將代替有相同的 唯一鍵值的現有行。如果你指定 ignore ，跳過有唯一鍵的現有行的
重複行的輸入。如果你不指定任何一個選項，當找到重複鍵時，出現一個錯誤，
並且文字 檔案的餘下部分被忽略。例如：
load data  low_priority infile "/home/mark/data sql" replace into table Orders;
 
4  分隔符
（1） fields關鍵字指定了檔案記段的分割格式，如果用到這個關鍵字，MySQL剖析器希望
看到至少有下面的一個選項： 
terminated by 分隔符：意思是以什麼字元作為分隔符
enclosed by 欄位括起字元
escaped by 轉義字元
terminated by 描述欄位的分隔符，預設 情況下是tab字元（\t） 
enclosed by描述的是欄位的括起字元。
escaped by 描述的轉義字元。預設的是反斜槓 （backslash：\ ）  
例如：load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';
（2）lines 關鍵字指定了每條記錄的分隔符預設為 '\n' 即為換行符
如果兩個欄位都指定了 那 fields 必須在 lines 之前。 如果不指定 fields 關鍵字預設值 與如果你這樣
寫的相同：  fields terminated by'\t' enclosed by ’ '' ‘ escaped by'\\'
如果你不指定一個 lines 子句，預設值與 如果你這樣寫的相同：  lines terminated by'\n'
例如： load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';
5    load data infile  可以按指定的列把檔案匯入到資料庫中。 當我們要把資料的一部分內容
匯入的時候，，需要加入一些欄目（列/欄位/field）到MySQL資料庫中，以適應一些額外的
需要。比方說，我們要從 Access資料庫升級到MySQL資料庫的時候
下面的例子顯示瞭如何向指 定的欄目(field)中匯入資料： 
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);
6    當在伺服器主機上尋找檔案時，服 務器使用下列規則： 
（ 1 ）如果給出一個絕對路徑名，伺服器使用該路徑名。 
（ 2 ）如果給出一個有一個或多個前置部件的相對路徑名，伺服器相對伺服器的資料目錄
搜尋檔案。   
（ 3 ）如果給出一個沒有前置部件的一個 檔名，伺服器在當前資料庫的資料庫目錄尋找檔案。 
例如：  /myfile txt” ="color: red">給出的檔案是從伺服器的資料目錄讀取，而作為 “myfile txt” 給出的一個文 件
是從當前資料庫的資料庫目錄下讀取。
  
注意：欄位中的空值用 \N 表示

我的文章一般淺顯易懂，不會搞那麼深入讓大家很難理解。（其實我水平也不咋樣)

LOAD DATA INFILE 一直被認為是MySQL很強大的一個資料匯入工具，因為他速度非常的快。
不過有幾個問題一定要注意
1、編碼。
2、靈活匯入匯出。


LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]

    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

LOAD DATA INFILE語句從一個文字檔案中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀檔案。如果LOCAL沒指定，檔案必須位於 伺服器上。 (LOCAL在MySQL3.22.6或以後版本中可用。）

為了 安全原因，當讀取位於伺服器上的文字檔案時，檔案必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上檔案使用LOAD DATA INFILE， 在伺服器主機上你必須有file的許可權。見6.5 由MySQL提供的許可權。

如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執行被推遲到沒有其他客戶讀取表後。

使用LOCAL將比讓伺服器直接存取檔案慢些，因為檔案的內容必須從客戶主機傳送到伺服器主機。在另一方面，你不需要file許可權裝載本地檔案。

你也可以使用mysqlimport實用程式裝載資料檔案；它由傳送一個LOAD DATA INFILE命令到伺服器來運作。 --local選項使得mysqlimport從客戶主機上讀取資料。如果客戶和伺服器支援壓縮協議，你能指定--compress在較慢的 網路上獲得更好的效能。

當在伺服器主機上尋找檔案時，伺服器使用下列規則：

如果給出一個絕對路徑名，伺服器使用該路徑名。 
如果給出一個有一個或多個前置部件的相對路徑名，伺服器相對伺服器的資料目錄搜尋檔案。 
如果給出一個沒有前置部件的一個檔名，伺服器在當前資料庫的資料庫目錄尋找檔案。 
注意這些規則意味著一個像“./myfile.txt”給出的檔案是從伺服器的資料目錄讀取，而作為“myfile.txt”給出的一個檔案是從當前資料庫的資料庫目錄下讀取。也要注意，對於下列哪些語句，對db1檔案從資料庫目錄讀取，而不是db2：

mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;

REPLACE和IGNORE關鍵詞控制對現有的唯一鍵記錄的重複的處理。如果你指定REPLACE，新行將代替有相同的唯一鍵值的現有行。如果你指定IGNORE，跳過有唯一鍵的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複鍵鍵時，出現一個錯誤，並且文字檔案的餘下部分被忽略時。

如果你使用LOCAL關鍵詞從一個本地檔案裝載資料，伺服器沒有辦法在操作的當中停止檔案的傳輸，因此預設的行為好像IGNORE被指定一樣。

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，
SELECT句法。為了將一個資料庫的資料寫入一個檔案，使用SELECT ... INTO OUTFILE，為了將檔案讀回資料庫，使用LOAD DATA INFILE。兩個命令的FIELDS和LINES子句的語法是相同的。兩個子句是可選的，但是如果指定兩個，FIELDS必須在LINES之前。

如果你指定一個FIELDS子句，它的每一個子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可選的，除了你必須至少指定他們之一。

如果你不指定一個FIELDS子句，預設值與如果你這樣寫的相同：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果你不指定一個LINES子句，預設值與如果你這樣寫的相同：

LINES TERMINATED BY '\n' 
換句話說，預設值導致讀取輸入時，LOAD DATA INFILE表現如下：

在換行符處尋找行邊界 
在定位符處將行分進欄位 
不要期望欄位由任何引號字元封裝 
將由“\”開頭的定位符、換行符或“\”解釋是欄位值的部分字面字元 
相反，預設值導致在寫入輸出時，SELECT ... INTO OUTFILE表現如下：

在欄位之間寫定位符 
不用任何引號字元封裝欄位 
使用“\”轉義出現在欄位中的定位符、換行符或“\”字元 
在行尾處寫換行符 
注意，為了寫入FIELDS ESCAPED BY '\\'，對作為一條單個的反斜線被讀取的值，你必須指定2條反斜線值。

IGNORE number LINES選項可被用來忽略在檔案開始的一個列名字的頭：

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

當你與LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE將一個資料庫的資料寫進一個檔案並且隨後馬上將檔案讀回資料庫時，兩個命令的欄位和處理選項必須匹配，否則，LOAD DATA INFILE將不能正確解釋檔案的內容。假定你使用SELECT ... INTO OUTFILE將由逗號分隔的欄位寫入一個檔案：

mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
           FIELDS TERMINATED BY ','
           FROM ...

為了將由逗號分隔的檔案讀回來，正確的語句將是：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY ',';

相反，如果你試圖用下面顯示的語句讀取檔案，它不會工作，因為它命令LOAD DATA INFILE在欄位之間尋找定位符：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY '\t';

可能的結果是每個輸入行將被解釋為單個的欄位。

LOAD DATA INFILE能被用來讀取從外部來源獲得的檔案。例如，以dBASE格式的檔案將有由逗號分隔並用雙引號包圍的欄位。如果檔案中的行由換行符終止，下面顯示的命令說明你將用來裝載檔案的欄位和行處理選項：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           LINES TERMINATED BY '\n';

任何欄位或行處理選項可以指定一個空字串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必須是一個單個字元。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超過一個字元。例如，寫入由回車換行符對（CR+LF）終止的行，或讀取包含這樣行的一個檔案，指定一個LINES TERMINATED BY '\r\n'子句。

FIELDS [OPTIONALLY] ENCLOSED BY控制欄位的包圍字元。對於輸出(SELECT ... INTO OUTFILE)，如果你省略OPTIONALLY，所有的欄位由ENCLOSED BY字元包圍。對於這樣的輸出的一個例子(使用一個逗號作為欄位分隔符)顯示在下面：

"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"

如果你指定OPTIONALLY，ENCLOSED BY字元僅被用於包圍CHAR和VARCHAR欄位：

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

注意，一個欄位值中的ENCLOSED BY字元的出現通過用ESCAPED BY字元作為其字首來轉義。也要注意，如果你指定一個空ESCAPED BY值，可能產生不能被LOAD DATA INFILE正確讀出的輸出。例如，如果轉義字元為空，上面顯示的輸出顯示如下。注意到在第四行的第二個欄位包含跟隨引號的一個逗號，它(錯誤地)好象要終止欄位：

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

對於輸入，ENCLOSED BY字元如果存在，它從欄位值的尾部被剝去。（不管是否指定OPTIONALLY都是這樣；OPTIONALLY對於輸入解釋不起作用)由ENCLOSED BY字元領先的ESCAPED BY字元出現被解釋為當前欄位值的一部分。另外，出現在欄位中重複的ENCLOSED BY被解釋為單個ENCLOSED BY字元，如果欄位本身以該字元開始。例如，如果ENCLOSED BY '"'被指定，引號如下處理：

"The ""BIG"" boss" -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY控制如何寫入或讀出特殊字元。如果FIELDS ESCAPED BY字元不是空的，它被用於字首在輸出上的下列字元：

FIELDS ESCAPED BY字元 
FIELDS [OPTIONALLY] ENCLOSED BY字元 
FIELDS TERMINATED BY和LINES TERMINATED BY值的第一個字元 
ASCII 0（實際上將後續轉義字元寫成 ASCII'0'，而不是一個零值位元組） 
如果FIELDS ESCAPED BY字元是空的，沒有字元被轉義。指定一個空轉義字元可能不是一個好主意，特別是如果在你資料中的欄位值包含剛才給出的表中的任何字元。

對於輸入，如果FIELDS ESCAPED BY字元不是空的，該字元的出現被剝去並且後續字元在字面上作為欄位值的一個部分。例外是一個轉義的“0”或“N”（即，\0或\N，如果轉義字元是“\”)。這些序列被解釋為ASCII 0（一個零值位元組）和NULL。見下面關於NULL處理的規則。

對於更多關於“\”- 轉義句法的資訊，在某些情況下，欄位和行處理選項相互作用：

如果LINES TERMINATED BY是一個空字串並且FIELDS TERMINATED BY是非空的，行也用FIELDS TERMINATED BY終止。
如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都是空的('')，一個固定行(非限定的)格式被使用。用固定行格式，在欄位之間不使用分隔符。相反，列值只用列的“顯示”寬度被寫入和讀出。例如，如果列被宣告為INT(7)，列的值使用7個字元的欄位被寫入。對於輸入，列值通過讀取7個字元獲得。固定行格式也影響NULL值的處理；見下面。注意如果你正在使用一個多位元組字符集，固定長度格式將不工作。
NULL值的處理有多種，取決於你使用的FIELDS和LINES選項：

對於預設FIELDS和LINES值，對輸出，NULL被寫成\N，對輸入，\N被作為NULL讀入(假定ESCAPED BY字元是“\”)。
如果FIELDS ENCLOSED BY不是空的，包含以文字詞的NULL作為它的值的欄位作為一個NULL值被讀入(這不同於包圍在FIELDS ENCLOSED BY字元中的字NULL，它作為字串'NULL'讀入)。
如果FIELDS ESCAPED BY是空的，NULL作為字NULL被寫入。 
用固定行格式(它發生在FIELDS TERMINATED BY和FIELDS ENCLOSED BY都是空的時候)，NULL作為一個空字串被寫入。注意，在寫入檔案時，這導致NULL和空字串在表中不能區分，因為他們都作為空字串被寫入。如果在讀迴檔案時需要能區分這兩者，你應該不使用固定行格式。
一些不被LOAD DATA INFILE支援的情況：

固定長度的行(FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空)和BLOB或TEXT列。
如果你指定一個分隔符與另一個相同，或是另一個的字首，LOAD DATA INFILE不能正確地解釋輸入。例如，下列FIELDS子句將導致問題： 
FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果FIELDS ESCAPED BY是空的，一個包含跟隨FIELDS TERMINATED BY值之後的FIELDS ENCLOSED BY或LINES TERMINATED BY的欄位值將使得LOAD DATA INFILE過早地終止讀取一個欄位或行。這是因為LOAD DATA INFILE不能正確地決定欄位或行值在哪兒結束。
下列例子裝載所有persondata表的行：

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

沒有指定欄位表，所以LOAD DATA INFILE期望輸入行對每個表列包含一個欄位。使用預設FIELDS和LINES值。

如果你希望僅僅裝載一張表的某些列，指定一個欄位表：

mysql> LOAD DATA INFILE 'persondata.txt'
           INTO TABLE persondata (col1,col2,...);

如果在輸入檔案中的欄位順序不同於表中列的順序，你也必須指定一個欄位表。否則，MySQL不能知道如何匹配輸入欄位和表中的列。

如果一個行有很少的欄位，對於不存在輸入欄位的列被設定為預設值。

如果欄位值預設，空欄位值有不同的解釋：

對於字串型別，列被設定為空字串。 
對於數字型別，列被設定為0。 
對於日期和時間型別，列被設定為該型別的適當“零”值。 
如果列有一個NULL，或(只對第一個TIMESTAMP列)在指定一個欄位表時，如果TIMESTAMP列從欄位表省掉，TIMESTAMP列只被設定為當前的日期和時間。

如果輸入行有太多的欄位，多餘的欄位被忽略並且警告數字加1。

LOAD DATA INFILE認為所有的輸入是字串，因此你不能像你能用INSERT語句的ENUM或SET列的方式使用數字值。所有的ENUM和SET值必須作為字串被指定！

如果你正在使用C API，當LOAD DATA INFILE查詢完成時，你可通過呼叫API函式mysql_info()得到有關查詢的資訊。資訊字串的格式顯示在下面：

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
當值通過INSERT語句插入時，在某些情況下出現警告，除了在輸入行中有太少或太多的欄位時，LOAD DATA INFILE也產生警告。警告沒被 儲存在任何地方；警告數字僅能用於表明一切是否順利。如果你得到警告並且想要確切知道你為什麼得到他們，一個方法是使用SELECT ... INTO OUTFILE到另外一個檔案並且把它與你的原版輸入檔案比較




load data語句詳解
1、語法解析
1.1. 必選子句或關鍵字
1.2. 可選子句或關鍵字
1.2.1. LOW_PRIORITY關鍵字
1.2.2. LOCAL關鍵字
1.2.2.1. 使用與不使用local關鍵字的流程
1.2.2.2. 使用local關鍵字的錯誤處理
1.2.3. REPLACE與IGNORE關鍵字
1.2.4. PARTITION子句
1.2.5. CHARACTER SET charset_name子句
1.2.6. FIELDS（與COLUMNS關鍵字相同）和LINES子句
1.2.6.1. FIELDS關鍵字及其子句詳解
1.2.6.2. LINES 關鍵字及其子句詳解
1.2.6.3. FIELDS和LINES注意事項
1.2.7. IGNORE number {LINES | ROWS}子句
1.2.8. (col_name_or_user_var,…)指定欄位名稱的子句
1.2.8. SET col_name = expr,…子句
2、批量匯出和批量匯入
2.1. 使用mysqldump批量匯出
2.2. 使用mysqimport批量匯出
2.2.1. mysqlimport語法及其引數說明
2.2.2. mysqlimport用法演示示例
2.2.2.1. 單表匯入
2.2.2.2.多表匯入
3、總結

load data語句詳解

  • 背景

    • 資料庫版本：MySQL 5.7.18
    • 伺服器資訊：本地到處匯入在10.10.30.241上演示，local遠端匯入在10.10.30.250上演示
    • 資料庫引數配置： 
      * 雙一，secure_file_priv=''，log-bin，binlog_format=row，隔離級別RC，sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
    • 參考資料： https://dev.mysql.com/doc/refman/5.7/en/load-data.html

  • 製造測試資料


  1. admin@localhost : (none) 09:28:29> use xiaoboluo
  2. Database changed
  3. admin@localhost : xiaoboluo 09:28:31> show tables;
  4. +---------------------+
  5. | Tables_in_xiaoboluo |
  6. +---------------------+
  7. | test |
  8. | test2 |
  9. +---------------------+
  10. 2 rows in set (0.00 sec)


  13. admin@localhost : xiaoboluo 09:36:07> create table test3(id int unsigned not null primary key auto_increment,test varchar(100),test2 varchar(100));
  14. Query OK, 0 rows affected (0.01 sec)


  17. admin@localhost : xiaoboluo 09:36:47> insert into test3(test,test2) values('a string','100.20'),('a string containing a , comma','102.20'),('a string containing a " quote','102.20'),\
  18. ('a string containing a ", quote and comma','102.20');


  21. Query OK, 4 rows affected (0.00 sec)
  22. Records: 4 Duplicates: 0 Warnings: 0


  25. admin@localhost : xiaoboluo 09:40:33> select * from test3;
  26. +----+------------------------------------------+--------+
  27. | id | test | test2 |
  28. +----+------------------------------------------+--------+
  29. | 2 | a string | 100.20 |
  30. | 4 | a string containing a , comma | 102.20 |
  31. | 6 | a string containing a " quote | 102.20 |
  32. | 8 | a string containing a ", quote and comma | 102.20 |
  33. +----+------------------------------------------+--------+
  34. 4 rows in set (0.00 sec)

1、語法解析

  • 檢視語法幫助資訊

  1. admin@localhost : xiaoboluo 10:14:21> help load data;
  2. Name: 'LOAD DATA'
  3. Description:
  4. Syntax:
  5. LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
  6. [REPLACE | IGNORE]
  7. INTO TABLE tbl_name
  8. [PARTITION (partition_name,...)]
  9. [CHARACTER SET charset_name]
  10. [{FIELDS | COLUMNS}
  11. [TERMINATED BY 'string']
  12. [[OPTIONALLY] ENCLOSED BY 'char']
  13. [ESCAPED BY 'char']
  14. ]
  15. [LINES
  16. [STARTING BY 'string']
  17. [TERMINATED BY 'string']
  18. ]
  19. [IGNORE number {LINES | ROWS}]
  20. [(col_name_or_user_var,...)]
  21. [SET col_name = expr,...]
  22. .....

  • load data語句載入的資料來源可以是mysqldump匯出的純文字資料檔案，也可以是使用SELECT … INTO OUTFILE '/path/xx.txt';語句生成的單表純文字資料檔案，或者其他的方式生成的txt（只要生成的純文字資料列按指定分隔符分割的純文字資料檔案即可）

  • 從上面的幫助資訊可以看到整個load data語句的語法結構，其中load data infile 'file.txt' into table tb_name; 是最基本的使用語句結構，其餘的都為可選子句

1.1. 必選子句或關鍵字

  • load data語句簡單示例

  • 如果文字檔案中的資料欄位與表結構中的欄位定義順序相同，則直接使用如下語句載入即可


  1. # 執行select ...into outfile語句匯出文字檔案
  2. admin@localhost : xiaoboluo 10:11:19> select * from test3;
  3. +----+------------------------------------------+--------+
  4. | id | test | test2 |
  5. +----+------------------------------------------+--------+
  6. | 2 | a string | 100.20 |
  7. | 4 | a string containing a , comma | 102.20 |
  8. | 6 | a string containing a " quote | 102.20 |
  9. | 8 | a string containing a ", quote and comma | 102.20 |
  10. +----+------------------------------------------+--------+
  11. 4 rows in set (0.00 sec)


  14. admin@localhost : xiaoboluo 10:31:10> select * from test3 into outfile "/tmp/test3.txt";
  15. Query OK, 4 rows affected (0.00 sec)


  18. # 使用python檢視一下資料中的特殊符號，其中\t是製表符，用於欄位分割，\n是換行符，用於行分割
  19. >>> f = open('/tmp/test3.txt','r')

  21. >>> data = f.readlines()
  22. >>> data
  23. ['2\ta string\t100.20\n', '4\ta string containing a , comma\t102.20\n', '6\ta string containing a " quote\t102.20\n', '8\ta string containing a ", quote and comma\t102.20\n']
  24. >>> for i in data:
  25. ... print i,
  26. ...
  27. 2 a string 100.20
  28. 4 a string containing a , comma 102.20
  29. 6 a string containing a " quote 102.20
  30. 8 a string containing a ", quote and comma 102.20


  33. # 現在，truncate掉表test3，執行load data載入資料
  34. admin@localhost : xiaoboluo 10:31:43> truncate test3;
  35. Query OK, 0 rows affected (0.01 sec)


  38. admin@localhost : xiaoboluo 10:36:40> load data infile '/tmp/test3.txt' into table test3;
  39. Query OK, 4 rows affected (0.00 sec)
  40. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  43. admin@localhost : xiaoboluo 10:36:53> select * from test3; #這裡可以看到，資料到處匯入正常
  44. +----+------------------------------------------+--------+
  45. | id | test | test2 |
  46. +----+------------------------------------------+--------+
  47. | 2 | a string | 100.20 |
  48. | 4 | a string containing a , comma | 102.20 |
  49. | 6 | a string containing a " quote | 102.20 |
  50. | 8 | a string containing a ", quote and comma | 102.20 |
  51. +----+------------------------------------------+--------+
  52. 4 rows in set (0.00 sec)


  55. admin@localhost : xiaoboluo 10:36:58>
  • 如果文字檔案中的資料欄位與表結構中的欄位定義順序不同，則使用如下語句指定載入表中的欄位順序

  1. # 匯出文字，匯出文字時不使用select *，而是使用具體的欄位，把順序稍微調整一下
  2. admin@localhost : xiaoboluo 10:36:58> system rm -f /tmp/test3.txt;
  3. admin@localhost : xiaoboluo 10:40:46> desc test3; # 留意表的欄位定義順序，這裡是id, test, test2
  4. +-------+------------------+------+-----+---------+----------------+
  5. | Field | Type | Null | Key | Default | Extra |
  6. +-------+------------------+------+-----+---------+----------------+
  7. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
  8. | test | varchar(100) | YES | | NULL | |
  9. | test2 | varchar(100) | YES | | NULL | |
  10. +-------+------------------+------+-----+---------+----------------+
  11. 3 rows in set (0.00 sec)


  14. admin@localhost : xiaoboluo 10:41:35> select * from test3; # 留個表中各個欄位的值大概是什麼內容
  15. +----+------------------------------------------+--------+
  16. | id | test | test2 |
  17. +----+------------------------------------------+--------+
  18. | 2 | a string | 100.20 |
  19. | 4 | a string containing a , comma | 102.20 |
  20. | 6 | a string containing a " quote | 102.20 |
  21. | 8 | a string containing a ", quote and comma | 102.20 |
  22. +----+------------------------------------------+--------+
  23. 4 rows in set (0.00 sec)


  26. admin@localhost : xiaoboluo 10:40:17> select id,test2,test from test3 into outfile "/tmp/test3.txt";
  27. Query OK, 4 rows affected (0.01 sec)


  30. admin@localhost : xiaoboluo 10:40:41> system cat /tmp/test3.txt; #這裡可以看到文字檔案中的test欄位值放到最後去了
  31. 2 100.20 a string
  32. 4 102.20 a string containing a , comma
  33. 6 102.20 a string containing a " quote
  34. 8 102.20 a string containing a ", quote and comma


  37. # 現在，truncate掉表test3，執行load data載入資料
  38. admin@localhost : xiaoboluo 10:47:31> truncate test3;
  39. Query OK, 0 rows affected (0.01 sec)


  42. admin@localhost : xiaoboluo 10:48:43> load data infile '/tmp/test3.txt' into table test3(id,test2,test);
  43. Query OK, 4 rows affected (0.00 sec)
  44. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  47. admin@localhost : xiaoboluo 10:49:13> select * from test3; #可以看到，使用(id,test2,test)子句指定了與文字檔案中資料的欄位一致的順序，匯入表表中之後資料的順序是正確的
  48. +----+------------------------------------------+--------+
  49. | id | test | test2 |
  50. +----+------------------------------------------+--------+
  51. | 2 | a string | 100.20 |
  52. | 4 | a string containing a , comma | 102.20 |
  53. | 6 | a string containing a " quote | 102.20 |
  54. | 8 | a string containing a ", quote and comma | 102.20 |
  55. +----+------------------------------------------+--------+
  56. 4 rows in set (0.00 sec)

1.2. 可選子句或關鍵字

  • 以下演示部分只針對部分子句或關鍵字做演示，並不是全部，悉知

1.2.1. LOW_PRIORITY關鍵字

  • 如果load data語句使用了LOW_PRIORITY關鍵字，則在碰到其他會話操作相同表時，則會延遲執行LOAD DATA語句，直到其他會話操作表結束為止。這僅影響使用表級鎖定的儲存引擎（如MyISAM，MEMORY和MERGE），對於innodb儲存引擎不起作用，因為innodb引擎是行級鎖，對於load data新插入的不同的資料行之間的操作不會發生衝突。本小節不做演示，更多資訊參考連結： https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.2. LOCAL關鍵字

1.2.2.1. 使用與不使用local關鍵字的流程

  • 如果要載入的文字檔案不在mysql server資料庫本身的本地磁碟，客戶端也不是從mysql server本機登入的，則需要使用local關鍵字，指定mysql server從client host本地載入該檔案，需要mysql server端使用local_infile=true(或者設定為1，不設定時預設為1)啟動，以及客戶端連線mysql server時也使用local_infile=true(或者設定為1，不指定時預設為1)連線才能使用，server和client必須都開啟這個引數才能使用local關鍵字，任意一個關閉都不能使用

  1. # 登入到資料庫，重新匯出表資料到文字，併傳送到10.10.30.250伺服器
  2. admin@localhost : xiaoboluo 10:51:57> system rm -f /tmp/test3.txt;
  3. admin@localhost : xiaoboluo 11:10:19> select * from test3 into outfile "/tmp/test3.txt";
  4. Query OK, 4 rows affected (0.01 sec)


  7. admin@localhost : xiaoboluo 11:10:41> system scp /tmp/test3.txt 10.10.30.250:/tmp/
  8. test3.txt 100% 146 0.1KB/s 00:00
  9. admin@localhost : xiaoboluo 11:11:15>


  12. # 登入到10.10.30.250伺服器，遠端連線10.10.30.241資料庫
  13. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d ~]# mysql -uadmin -pletsg0 -h10.10.30.241
  14. mysql: [Warning] Using a password on the command line interface can be insecure.
  15. Welcome to the MySQL monitor. Commands end with ; or \g.
  16. Your MySQL connection id is 46
  17. Server version: 5.7.18-log MySQL Community Server (GPL)


  20. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


  23. Oracle is a registered trademark of Oracle Corporation and/or its
  24. affiliates. Other names may be trademarks of their respective
  25. owners.


  28. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


  31. mysql> use xiaoboluo
  32. Reading table information for completion of table and column names
  33. You can turn off this feature to get a quicker startup with -A


  36. Database changed
  37. mysql> system ls -lh /tmp/test3.txt;
  38. -rw-r--r-- 1 root root 146 May 3 11:11 /tmp/test3.txt
  39. mysql> system cat /tmp/test3.txt;
  40. 2 a string 100.20
  41. 4 a string containing a , comma 102.20
  42. 6 a string containing a " quote 102.20
  43. 8 a string containing a ", quote and comma 102.20
  44. mysql> show variables like '%local%';
  45. +---------------+-------+
  46. | Variable_name | Value |
  47. +---------------+-------+
  48. | local_infile | ON |
  49. +---------------+-------+
  50. 1 row in set (0.00 sec)


  53. mysql> set global local_infile=OFF; #關閉server端的local_infile引數
  54. Query OK, 0 rows affected (0.00 sec)


  57. mysql> truncate test3;
  58. Query OK, 0 rows affected (0.00 sec)


  61. mysql> load data local infile '/tmp/test3.txt' into table test3; #執行匯入資料時報錯了
  62. ERROR 1148 (42000): The used command is not allowed with this MySQL version
  63. mysql> set global local_infile=ON; #重新開啟server端的local_infile引數
  64. Query OK, 0 rows affected (0.00 sec)


  67. mysql> load data local infile '/tmp/test3.txt' into table test3; #匯入成功
  68. Query OK, 4 rows affected (0.00 sec)
  69. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  72. mysql> select * from test3; #檢視資料，可以看到資料已成功匯入表
  73. +----+------------------------------------------+--------+
  74. | id | test | test2 |
  75. +----+------------------------------------------+--------+
  76. | 2 | a string | 100.20 |
  77. | 4 | a string containing a , comma | 102.20 |
  78. | 6 | a string containing a " quote | 102.20 |
  79. | 8 | a string containing a ", quote and comma | 102.20 |
  80. +----+------------------------------------------+--------+
  81. 4 rows in set (0.00 sec)


  84. # 對於客戶端連線server時使用local_infile=0引數，在執行匯入資料時也會報相同的錯誤，這裡不做演示，示例命令：mysql -uadmin -pletsg0 -h10.10.30.241 --local-infile=0

  • 使用local關鍵字與不使用local關鍵字時load data語句載入文字檔案的流程

    • 如果指定了LOCAL，則該客戶端程式在客戶端主機上讀取load data語句需要的檔案並將其傳送到伺服器。該檔案可以使用完整路徑名稱來指定其位置。也可以使用相對路徑，使用相對路徑時，路徑字首為使用客戶端程式時的工作目錄，當使用帶有LOAD DA他的LOCAL關鍵字時，會在mysql server端的臨時目錄中建立該檔案的副本(注意：這個場景下該檔案的路徑不是由tmpdir或slave_load_tmpdir的值指定的，而是作業系統的臨時目錄/tmp，並且在MySQL server中這個存放副本的路徑是不可配置的（通常，系統臨時目錄是Linux系統上的/tmp，Windows上的C:\WINDOWS\TEMP），要注意，如果在該臨時目錄下建立load檔案的副本時發現磁碟空間不夠，會導致LOAD DATA LOCAL語句執行失敗。在客戶端主機讀取文字檔案定位規則是如果指定的是絕對路徑，則使用絕對路徑，如果是相對路徑，則在登入mysql server時的工作路徑下查詢，找不到就報錯：ERROR 2 (HY000): File 'test3.txt' not found (Errcode: 2 - No such file or directory)
    • 如果未指定LOCAL，則該檔案必須位於mysql server端主機上，並由伺服器直接讀取。伺服器使用以下規則來定位檔案： 
      * 如果檔名是絕對路徑名， mysql server將按照給定的路徑讀取檔案 
      * 如果檔名是一個相對路徑名，則mysql server將在server端的datadir下搜尋該檔案，如果load data語句指定了庫名，則在datadir的指定庫名下搜尋文字檔案，如果沒有指定庫名，則在預設資料庫下搜尋文字檔案（load data語句沒有指定庫名時要正確執行語句必須先使用use db語句切庫，so，這個就是預設庫） 
      * 如果datadir下還找不到就報錯：ERROR 13 (HY000): Can't get stat of '/datadir/xiaoboluo/test3.txt' (Errcode: 2 - No such file or directory)

  • PS：

    • 非LOCAL方式只會載入位於mysql server上的文字檔案。出於安全考慮，此類操作要求您具有FILE特權。而且，非本地載入操作也受到secure_file_priv系統變數的設定。如果變數值是非空目錄名稱，則要載入的檔案必須位於該變數指定的目錄中。如果變數值為空（這是不安全的，這個時候server本地匯入由file許可權控制，client遠端主機的檔案匯入由server和client端的local_infile選項共同控制），則該檔案只能由伺服器讀取。
    • 使用LOCAL比讓伺服器直接訪問檔案要慢一些，因為檔案的內容必須通過客戶端的連線傳送到伺服器。另一方面，您不需要FILE許可權來載入本地檔案。可以使用local子句
    • 使用local關鍵字時碰到唯一鍵值衝突時的處理方式與IGNORE關鍵字相同，忽略衝突的行

1.2.2.2. 使用local關鍵字的錯誤處理

  • 使用LOCAL會影響錯誤處理行為：
  • 使用LOAD DATA INFILE，資料解析碰到重複鍵時預設情況下會終止操作

  1. admin@localhost : xiaoboluo 11:46:09> select * from test3 into outfile '/tmp/test3.txt';
  2. Query OK, 4 rows affected (0.00 sec)


  5. admin@localhost : xiaoboluo 01:40:32> desc test3; #留意id是主鍵
  6. +-------+------------------+------+-----+---------+----------------+
  7. | Field | Type | Null | Key | Default | Extra |
  8. +-------+------------------+------+-----+---------+----------------+
  9. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
  10. | test | varchar(100) | YES | | NULL | |
  11. | test2 | varchar(100) | YES | | NULL | |
  12. +-------+------------------+------+-----+---------+----------------+
  13. 3 rows in set (0.00 sec)


  16. admin@localhost : xiaoboluo 01:40:51> select * from test3; # 留意id列值是2，4，6，8
  17. +----+------------------------------------------+--------+
  18. | id | test | test2 |
  19. +----+------------------------------------------+--------+
  20. | 2 | a string | 100.20 |
  21. | 4 | a string containing a , comma | 102.20 |
  22. | 6 | a string containing a " quote | 102.20 |
  23. | 8 | a string containing a ", quote and comma | 102.20 |
  24. +----+------------------------------------------+--------+
  25. 4 rows in set (0.00 sec)


  28. admin@localhost : xiaoboluo 01:40:57> system cat /tmp/test3.txt; #留意匯出的文字中的第一列數值與表中的id列值相同
  29. 2 a string 100.20
  30. 4 a string containing a , comma 102.20
  31. 6 a string containing a " quote 102.20
  32. 8 a string containing a ", quote and comma 102.20


  35. admin@localhost : xiaoboluo 01:41:21> load data infile '/tmp/test3.txt' into table test3; #執行load data時，不使用local關鍵字直接報主鍵衝突
  36. ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
  37. admin@localhost : xiaoboluo 01:41:32> show warnings;
  38. +-------+------+---------------------------------------+
  39. | Level | Code | Message |
  40. +-------+------+---------------------------------------+
  41. | Error | 1062 | Duplicate entry '2' for key 'PRIMARY' |
  42. +-------+------+---------------------------------------+
  43. 1 row in set (0.00 sec)
  • 使用LOAD DATA LOCAL INFILE，資料解析碰到重複鍵時將發出警告，並且操作繼續進行，因為server端無法在客戶端操作期間停止傳輸檔案。此時處理重複鍵與指定IGNORE關鍵字相同（即忽略主鍵衝突的資料行）

  1. admin@localhost : xiaoboluo 01:46:52> load data local infile '/tmp/test3.txt' into table test3; #加上local關鍵字再次執行，可以發現不報錯了，但是Skipped: 4 Warnings: 4表示跳過了4行資料匯入，\
  2. 注意：如果不使用local關鍵字，在預設的sql_mode下無法匯入，否則就需要去掉嚴格的sql_mode


  5. Query OK, 0 rows affected, 4 warnings (0.00 sec)
  6. Records: 4 Deleted: 0 Skipped: 4 Warnings: 4


  9. Warning (Code 1062): Duplicate entry '2' for key 'PRIMARY'
  10. Warning (Code 1062): Duplicate entry '4' for key 'PRIMARY'
  11. Warning (Code 1062): Duplicate entry '6' for key 'PRIMARY'
  12. Warning (Code 1062): Duplicate entry '8' for key 'PRIMARY'
  13. admin@localhost : xiaoboluo 01:47:01> show warnings;
  14. +---------+------+---------------------------------------+
  15. | Level | Code | Message |
  16. +---------+------+---------------------------------------+
  17. | Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |
  18. | Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' |
  19. | Warning | 1062 | Duplicate entry '6' for key 'PRIMARY' |
  20. | Warning | 1062 | Duplicate entry '8' for key 'PRIMARY' |
  21. +---------+------+---------------------------------------+
  22. 4 rows in set (0.00 sec)


  25. admin@localhost : xiaoboluo 01:47:05> select * from test3;
  26. +----+------------------------------------------+--------+
  27. | id | test | test2 |
  28. +----+------------------------------------------+--------+
  29. | 2 | a string | 100.20 |
  30. | 4 | a string containing a , comma | 102.20 |
  31. | 6 | a string containing a " quote | 102.20 |
  32. | 8 | a string containing a ", quote and comma | 102.20 |
  33. +----+------------------------------------------+--------+
  34. 4 rows in set (0.00 sec)

1.2.3. REPLACE與IGNORE關鍵字

  • REPLACE和IGNORE關鍵字控制對唯一鍵值衝突行的處理：

    • 如果指定了REPLACE關鍵字，則輸入行將覆蓋現有行。換句話說，與主鍵或唯一索引衝突的資料行將被執行覆蓋寫入，如果同時使用了local關鍵字，則與沒有使用local關鍵字行為相同
    • 如果指定了IGNORE關鍵字，則與唯一鍵值衝突的資料行將被丟棄，如果同時使用了local關鍵字，則與沒有使用local關鍵字行為相同
    • 如果不指定REPLACE和IGNORE任一選項，則行為取決於是否指定LOCAL關鍵字。沒有LOCAL，則發現主鍵或唯一索引衝突時就報錯終止load data語句執行，並忽略文字檔案的其餘部分的載入。如果使用了LOCAL關鍵字，則local關鍵字的預設行為與指定IGNORE時相同，這是因為server端無法在操作期間停止客戶端的檔案傳輸（不使用REPLACE與IGNORE關鍵字時的錯誤處理，詳見1.2.2.2小節）
  • 下面對使用REPLACE與IGNORE關鍵字進行演示
  • 如果文字檔案中的資料欄位有與表結構中的唯一索引、主鍵索引衝突的，則使用REPLACE關鍵字，該關鍵字會對衝突的資料進行覆蓋（內部轉換為UPDATE，見後續章節示例）

  1. # 使用replace 覆蓋衝突資料行，不使用local關鍵字
  2. admin@localhost : xiaoboluo 01:47:47> flush logs;
  3. Query OK, 0 rows affected (0.01 sec)


  6. admin@localhost : xiaoboluo 02:16:34> select * from test3;
  7. +----+------------------------------------------+--------+
  8. | id | test | test2 |
  9. +----+------------------------------------------+--------+
  10. | 2 | a string | 100.20 |
  11. | 4 | a string containing a , comma | 102.20 |
  12. | 6 | a string containing a " quote | 102.20 |
  13. | 8 | a string containing a ", quote and comma | 102.20 |
  14. +----+------------------------------------------+--------+
  15. 4 rows in set (0.00 sec)


  18. admin@localhost : xiaoboluo 02:16:48> show variables like '%binlog_format%';
  19. +---------------+-------+
  20. | Variable_name | Value |
  21. +---------------+-------+
  22. | binlog_format | ROW |
  23. +---------------+-------+
  24. 1 row in set (0.00 sec)


  27. admin@localhost : xiaoboluo 02:17:26> show variables like '%tx_isolation%';
  28. +---------------+----------------+
  29. | Variable_name | Value |
  30. +---------------+----------------+
  31. | tx_isolation | READ-COMMITTED |
  32. +---------------+----------------+
  33. 1 row in set (0.01 sec)
  34. admin@localhost : xiaoboluo 02:18:01> update test3 set test2='111111111' where id=2; #修改其中一行資料
  35. Query OK, 1 row affected (0.00 sec)
  36. Rows matched: 1 Changed: 1 Warnings: 0


  39. admin@localhost : xiaoboluo 02:20:39> select * from test3; #檢視錶中的資料，留意id=2的test2欄位值為111111111
  40. +----+------------------------------------------+-----------+
  41. | id | test | test2 |
  42. +----+------------------------------------------+-----------+
  43. | 2 | a string | 111111111 |
  44. | 4 | a string containing a , comma | 102.20 |
  45. | 6 | a string containing a " quote | 102.20 |
  46. | 8 | a string containing a ", quote and comma | 102.20 |
  47. +----+------------------------------------------+-----------+
  48. 4 rows in set (0.00 sec)


  51. admin@localhost : xiaoboluo 02:20:41> load data infile '/tmp/test3.txt' replace into table test3; #執行不帶local關鍵字但帶repalce關鍵字的語句
  52. Query OK, 5 rows affected (0.00 sec)
  53. Records: 4 Deleted: 1 Skipped: 0 Warnings: 0


  56. admin@localhost : xiaoboluo 02:20:50> select * from test3; #查詢表中的資料，可以發現id=2的行的test2欄位被覆蓋為文字檔案中的值100.20 了
  57. +----+------------------------------------------+--------+
  58. | id | test | test2 |
  59. +----+------------------------------------------+--------+
  60. | 2 | a string | 100.20 |
  61. | 4 | a string containing a , comma | 102.20 |
  62. | 6 | a string containing a " quote | 102.20 |
  63. | 8 | a string containing a ", quote and comma | 102.20 |
  64. +----+------------------------------------------+--------+
  65. 4 rows in set (0.00 sec)


  68. # 解析最後一個binlog來檢視一下（這裡僅作參考，主庫內部是否是binlog中記錄的形式處理覆蓋資料有待考證）
  69. $ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000004

  71. ......
  72. BEGIN
  73. /*!*/;
  74. # at 732
  75. #170503 14:20:50 server id 3306241 end_log_pos 814 CRC32 0xb4da8deb Rows_query
  76. # load data infile '/tmp/test3.txt' replace into table test3 #這裡是執行的原始load data 的sql語句，要看到此內容，需要開啟引數：binlog_rows_query_log_events=ON
  77. # at 814
  78. #170503 14:20:50 server id 3306241 end_log_pos 873 CRC32 0xe0066c03 Table_map: `xiaoboluo`.`test3` mapped to number 253
  79. # at 873
  80. #170503 14:20:50 server id 3306241 end_log_pos 958 CRC32 0xe95b171c Update_rows: table id 253 flags: STMT_END_F
  81. ### UPDATE `xiaoboluo`.`test3` #這裡可以看到發生資料衝突的行被執行了update
  82. ### WHERE
  83. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
  84. ### @2='a string' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  85. ### @3='111111111' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  86. ### SET
  87. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
  88. ### @2='a string' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  89. ### @3='100.20' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  90. # at 958
  91. #170503 14:20:50 server id 3306241 end_log_pos 989 CRC32 0x84b1f86e Xid = 364
  92. COMMIT/*!*/;
  93. ......
  • 也可以使用IGNORE關鍵字忽略衝突的行（注意，這裡的ignore與ignore number lines子句中的ignore作用不同，不要搞混淆）

  1. admin@localhost : xiaoboluo 02:26:45> flush logs;
  2. Query OK, 0 rows affected (0.01 sec)


  5. admin@localhost : xiaoboluo 02:29:18> select * from test3;
  6. +----+------------------------------------------+--------+
  7. | id | test | test2 |
  8. +----+------------------------------------------+--------+
  9. | 2 | a string | 100.20 |
  10. | 4 | a string containing a , comma | 102.20 |
  11. | 6 | a string containing a " quote | 102.20 |
  12. | 8 | a string containing a ", quote and comma | 102.20 |
  13. +----+------------------------------------------+--------+
  14. 4 rows in set (0.00 sec)


  17. admin@localhost : xiaoboluo 02:29:23> show variables like '%binlog_format%';
  18. +---------------+-------+
  19. | Variable_name | Value |
  20. +---------------+-------+
  21. | binlog_format | ROW |
  22. +---------------+-------+
  23. 1 row in set (0.00 sec)


  26. admin@localhost : xiaoboluo 02:29:29> show variables like '%tx_isolation%';
  27. +---------------+----------------+
  28. | Variable_name | Value |
  29. +---------------+----------------+
  30. | tx_isolation | READ-COMMITTED |
  31. +---------------+----------------+
  32. 1 row in set (0.00 sec)


  35. admin@localhost : xiaoboluo 02:29:34> update test3 set test2='111111111' where id=2;
  36. Query OK, 1 row affected (0.00 sec)
  37. Rows matched: 1 Changed: 1 Warnings: 0


  40. admin@localhost : xiaoboluo 02:29:41> select * from test3;
  41. +----+------------------------------------------+-----------+
  42. | id | test | test2 |
  43. +----+------------------------------------------+-----------+
  44. | 2 | a string | 111111111 |
  45. | 4 | a string containing a , comma | 102.20 |
  46. | 6 | a string containing a " quote | 102.20 |
  47. | 8 | a string containing a ", quote and comma | 102.20 |
  48. +----+------------------------------------------+-----------+
  49. 4 rows in set (0.00 sec)


  52. admin@localhost : xiaoboluo 02:29:45> load data infile '/tmp/test3.txt' ignore into table test3; #這裡可以看到Skipped: 4 Warnings: 4，表示4行資料都跳過了，注意：這是在sql_mode=''時匯入的，\
  53. 如果不修改sql_mode請使用local關鍵字


  56. Query OK, 0 rows affected, 4 warnings (0.00 sec)
  57. Records: 4 Deleted: 0 Skipped: 4 Warnings: 4


  60. Warning (Code 1062): Duplicate entry '2' for key 'PRIMARY'
  61. Warning (Code 1062): Duplicate entry '4' for key 'PRIMARY'
  62. Warning (Code 1062): Duplicate entry '6' for key 'PRIMARY'
  63. Warning (Code 1062): Duplicate entry '8' for key 'PRIMARY'
  64. admin@localhost : xiaoboluo 02:30:04> select * from test3; #檢視一下執行load data語句之後的表資料，發現id=2的test2列值還是111111111 沒有變化
  65. +----+------------------------------------------+-----------+
  66. | id | test | test2 |
  67. +----+------------------------------------------+-----------+
  68. | 2 | a string | 111111111 |
  69. | 4 | a string containing a , comma | 102.20 |
  70. | 6 | a string containing a " quote | 102.20 |
  71. | 8 | a string containing a ", quote and comma | 102.20 |
  72. +----+------------------------------------------+-----------+
  73. 4 rows in set (0.00 sec)

1.2.4. PARTITION子句

  • LOAD DATA支援使用PARTITION選項顯式分割槽選擇，其中包含一個或多個分割槽，子分割槽或兩者名稱的逗號分隔列表。當使用此選項時，如果檔案中的任何行無法插入到列表中指定的任何分割槽或子分割槽中，則該語句將失敗，並顯示錯誤，找到與給定分割槽集不匹配的行，本小節不做演示，更多資訊參考連結： https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.5. CHARACTER SET charset_name子句

  • 匯入的文字資料檔名必須以文字字串形式給出。在Windows上，在路徑名稱中指定反斜槓為正斜槓或雙倍反斜槓。 character_set_filesystem系統變數控制檔名的解釋

  • 伺服器使用由character_set_database系統變數指定的字符集來解釋檔案中的內容。SET NAMES語句和character_set_client系統變數的設定不影響檔案內容的字符集解析。如果輸入檔案的內容使用的字符集與server的預設值字符集不同，則建議先使用load data的CHARACTER SET子句指定解析文字檔案內容的字符集

  • LOAD DATA INFILE將檔案中的所有欄位以相同的字符集進行解析，而不管載入欄位列的資料型別定義的字符集如何。為了正確解釋檔案內容，您必須確保使用正確的字符集進行匯出資料和匯入資料。例如，如果您使用mysqldump -T或通過在mysql中執行SELECT … INTO OUTFILE語句匯出資料檔案時，建議使用--default-character-set選項指定一個對應資料的字符集，以便使用Load data語句匯入資料時不會發生字符集錯亂（使用mysqlimport的--default-character-set指定匯出資料時的字符集，使用mysql命令列客戶端的--default-character-set指定匯出資料時的字符集，注：不能指定ucs2,utf16,utf16le或utf32字符集來載入資料檔案）
  • 本小節不做演示，更多資訊參考連結： https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.6. FIELDS（與COLUMNS關鍵字相同）和LINES子句

  • 以下示例中的char代表單個字元，string代表字串（即多個字元），load data語句中，轉義字元和欄位引用符只能使用單個字元，欄位分隔符、行分隔符、行字首字元都可以使用多個字元（字串）

  • 對於LOAD DATA INFILE和SELECT … INTO OUTFILE語句中，FIELDS和LINES子句的語法完全相同。兩個子句在LOAD DATA INFILE和SELECT … INTO OUTFILE語句中都是可選的，但如果兩個子句都被指定，則FIELDS必須在LINES之前，否則報語法錯誤

    • FIELDS關鍵字共有三個子句，TERMINATED BY 'string'指定欄位分隔符，[OPTIONALLY] ENCLOSED BY 'char'指定欄位引用符（其中使用了OPTIONALLY關鍵字之後，只在char、varchar和text等字元型欄位上加欄位引用符，數值型的不會加欄位引用符，且OPTIONALLY 關鍵字只在匯出資料時才起作用，匯入資料時用於不用對資料沒有影響 ），ESCAPED BY 'char'指定轉義符，如果您指定了一個FIELDS子句，則它的每個子句也是可選的，但在你指定了FIELDS關鍵字之後，這個關鍵字的子句至少需要指定一個，後續章節會進行舉例說明
    • LINES關鍵字共有兩個子句，STARTING BY 'string'指定行字首字元，TERMINATED BY 'string'指定行分隔符（換行符），如果你指定了LINES關鍵字，則LINES的子句都是可選的，但在你指定了LINES關鍵字之後，這個關鍵字的子句至少需要指定一個，後續章節會進行舉例說明
    • 如果在匯入和匯出時沒有指定FIELDS和LINES子句，則匯入和匯出時兩個子句的預設值相同，預設的欄位分隔符為\t，行分隔符為\n（win上預設為\r\n，記事本程式上預設為\r），欄位引用符為空，行字首字元為空

  • 當mysql server匯出文字資料到檔案時，FIELDS和LINES預設值時SELECT … INTO OUTFILE在輸出文字資料時行為如下：

    • 在文字資料各欄位之間使用製表符來作為欄位分隔符
    • 不使用任何引號來包圍文字資料的各欄位值，即欄位引用符為空
    • 使用\轉義在欄位值中出現的製表符\t，換行符\n或轉義符本身\等特殊字元(即輸出的文字資料中對這些特殊字元前多加一個反斜槓)
    • 在行尾寫上換行符\n，即使用\n作為行分隔符（換行符）
    • 注意：如果您在Windows系統上生成了文字檔案，則可能必須使用LINES TERMINATED BY '\r\n'來正確讀取檔案，因為Windows程式通常使用兩個字元作為行終止符。某些程式（如寫字板）在寫入檔案時可能會使用\r作為行終止符(要讀取這些檔案，請使用LINES TERMINATED BY '\r')
    • FIELDS和LINES子句預設值時生成的純文字資料檔案可以使用python程式碼來讀取檔案檢視檔案中的製表符和換行符（linux下的cat和vim等編輯器預設會解析\t為製表符,\n為換行符，所以使用這些命令可能無法看到這些特殊符號）

  1. >>> f = open('/tmp/test3.txt','r')
  2. >>> data = f.readlines()
  3. >>> data
  4. ['2,"a string","100.20"\n', '4,"a string containing a , comma","102.20"\n', '6,"a string containing a \\" quote","102.20"\n', '8,"a string containing a \\", quote and comma","102.20"\n']
  5. >>> for i in data:
  6. ... print i,
  7. ...
  8. 2,"a string","100.20"
  9. 4,"a string containing a , comma","102.20"
  10. 6,"a string containing a \" quote","102.20"
  11. 8,"a string containing a \", quote and comma","102.20"
  • 當mysql server從文字檔案讀取資料時，FIELDS和LINES預設值會導致LOAD DATA INFILE的行為如下：
    • 尋找換行邊界字元\n來進行換行
    • 不跳過行字首，把行字首也當作資料（發生在如果匯出資料時使用了行字首，匯入時沒有指定正確的行字首或者根本沒有指定行字首選項時）
    • 使用製表符\t來分割一行資料中的各列
    • 要注意：在FIELDS和LINES的預設值下，在解析文字檔案時不會把字串之間的引號當作真正的引號，而是當作資料

1.2.6.1. FIELDS關鍵字及其子句詳解

  • 欄位分隔符，預設是\t，使用子句 fields terminated by 'string' 指定，其中string代表指定的欄位分隔符

  1. admin@localhost : xiaoboluo 03:08:34> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',';
  2. Query OK, 4 rows affected (0.00 sec)


  5. admin@localhost : xiaoboluo 03:08:37> system cat /tmp/test3.txt
  6. 2,a string,100.20
  7. 4,a string containing a \, comma,102.20
  8. 6,a string containing a " quote,102.20
  9. 8,a string containing a "\, quote and comma,102.20
  • 欄位引用符，如果加optionally選項則只用在char、varchar和text等字元型欄位上，數值型別會忽略使用引用符，如果不指定該子句，則預設不使用引用符，使用子句fields [optionally] enclosed by 'char'指定，其中char代表指定的欄位引用符

  1. # 指定欄位引用符為"，不使用optionally關鍵字
  2. admin@localhost : xiaoboluo 03:33:33> system rm -f /tmp/test3.txt;
  3. admin@localhost : xiaoboluo 03:37:21> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '"';
  4. Query OK, 5 rows affected (0.00 sec)


  7. admin@localhost : xiaoboluo 03:37:33> system cat /tmp/test3.txt
  8. "2" "a string" "100.20"
  9. "4" "a string containing a , comma" "102.20"
  10. "6" "a string containing a \" quote" "102.20"
  11. "8" "a string containing a \", quote and comma" "102.20"
  12. "10" "\\t" "102.20"


  15. # 指定欄位引用符為"，使用optionally關鍵字，可以看到id列的欄位引用符去掉了
  16. admin@localhost : xiaoboluo 03:37:41> system rm -f /tmp/test3.txt;
  17. admin@localhost : xiaoboluo 03:40:53> select * from test3 into outfile "/tmp/test3.txt" FIELDS optionally ENCLOSED BY '"';
  18. Query OK, 5 rows affected (0.00 sec)


  21. admin@localhost : xiaoboluo 03:41:03> system cat /tmp/test3.txt
  22. 2 "a string" "100.20"
  23. 4 "a string containing a , comma" "102.20"
  24. 6 "a string containing a \" quote" "102.20"
  25. 8 "a string containing a \", quote and comma" "102.20"
  26. 10 "\\t" "102.20"
  • 轉義字元，預設為\，使用子句fields escaped by 'char' 指定，其中char代表指定的轉義字元

  1. admin@localhost : xiaoboluo 03:42:41> system rm -f /tmp/test3.txt;
  2. admin@localhost : xiaoboluo 03:44:18> select * from test3 into outfile "/tmp/test3.txt" fields escaped by '.';
  3. Query OK, 5 rows affected (0.00 sec)


  6. admin@localhost : xiaoboluo 03:44:25> system cat /tmp/test3.txt # 可以看到資料中指定的轉義符.號被轉義了，而資料\t沒有被轉義
  7. 2 a string 100..20
  8. 4 a string containing a , comma 102..20
  9. 6 a string containing a " quote 102..20
  10. 8 a string containing a ", quote and comma 102..20
  11. 10 \t 102..20
  12. admin@localhost : xiaoboluo 03:44:28> truncate test3; #清空表
  13. Query OK, 0 rows affected (0.01 sec)


  16. admin@localhost : xiaoboluo 03:45:19> load data infile "/tmp/test3.txt" into table test3 fields escaped by '.'; #匯入資料時指定轉義符為.號
  17. Query OK, 5 rows affected (0.00 sec)
  18. Records: 5 Deleted: 0 Skipped: 0 Warnings: 0


  21. admin@localhost : xiaoboluo 03:45:40> select * from test3; #校驗資料，可以看到匯入資料正常
  22. +----+------------------------------------------+--------+
  23. | id | test | test2 |
  24. +----+------------------------------------------+--------+
  25. | 2 | a string | 100.20 |
  26. | 4 | a string containing a , comma | 102.20 |
  27. | 6 | a string containing a " quote | 102.20 |
  28. | 8 | a string containing a ", quote and comma | 102.20 |
  29. | 10 | \t | 102.20 |
  30. +----+------------------------------------------+--------+
  31. 5 rows in set (0.00 sec)

1.2.6.2. LINES 關鍵字及其子句詳解

  • 行字首字串，使用子句lines starting by 'string' 指定，其中string代表指定的行字首字串，行字首字串在匯出文字資料時使用該子句指定，在匯入文字時在一行資料中如果發現了行字首字串，則只匯入從字首字串開始之後的資料部分，字首字元本身及其之前的資料被忽略掉，如果某行資料不包含行字首字串，則整行資料都會被忽略

如果您想要讀取的純文字檔案中所有行都有一個您想要忽略的公用字首，則可以使用LINES STARTING BY'prefix_string'來跳過這個字首，以及字首字元前面的任何內容。如果某行資料不包含字首字元，則跳過整行內容，例如：


  1. # load data語句如下
  2. admin@localhost : xiaoboluo 03:48:04> system rm -f /tmp/test3.txt;
  3. admin@localhost : xiaoboluo 03:54:54> select * from test3 into outfile "/tmp/test3.txt" LINES STARTING BY 'xxx';
  4. Query OK, 5 rows affected (0.00 sec)


  7. admin@localhost : xiaoboluo 03:55:03> system cat /tmp/test3.txt #可以看到每行資料前面多了個行字首字串xxx
  8. xxx2 a string 100.20
  9. xxx4 a string containing a , comma 102.20
  10. xxx6 a string containing a " quote 102.20
  11. xxx8 a string containing a ", quote and comma 102.20
  12. xxx10 \\t 102.20


  15. # 現在，到shell命令列去修改一下，增加兩行
  16. admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最後要載入的純文字資料內容如下
  17. xxx2 a string 100.20
  18. xxx4 a string containing a , comma 102.20
  19. xxx6 a string containing a " quote 102.20
  20. xxx8 a string containing a ", quote and comma 102.20
  21. xxx10 \\t 102.20
  22. 12 \\t 102.20
  23. dfadsfasxxx14 \\t 102.20
  24. admin@localhost : xiaoboluo 03:59:03> truncate test3; #清空表
  25. Query OK, 0 rows affected (0.01 sec)


  28. admin@localhost : xiaoboluo 03:59:38> load data infile "/tmp/test3.txt" into table test3 LINES STARTING BY 'xxx'; #匯入資料，指定行字首字元為xxx
  29. Query OK, 6 rows affected (0.00 sec)
  30. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  33. admin@localhost : xiaoboluo 03:59:44> select * from test3; #校驗表資料，可以看到沒有xxx行字首的行被忽略了，而包含xxx的最後一行，從xxx開始截斷，xxx字元本身及其之前的內容被忽略，\
  34. xxx之後的內容被解析為行資料匯入了


  37. +----+------------------------------------------+--------+
  38. | id | test | test2 |
  39. +----+------------------------------------------+--------+
  40. | 2 | a string | 100.20 |
  41. | 4 | a string containing a , comma | 102.20 |
  42. | 6 | a string containing a " quote | 102.20 |
  43. | 8 | a string containing a ", quote and comma | 102.20 |
  44. | 10 | \t | 102.20 |
  45. | 14 | \t | 102.20 |
  46. +----+------------------------------------------+--------+
  47. 6 rows in set (0.00 sec)
  • 行結束符（換行符），linux下預設為\n，使用子句lines terminated by 'string' 指定，其中string代表指定的換行符

  1. # 指定換行符為\r\n匯出資料
  2. admin@localhost : xiaoboluo 03:59:49> system rm -f /tmp/test3.txt;
  3. admin@localhost : xiaoboluo 04:02:22> select * from test3 into outfile "/tmp/test3.txt" lines terminated by '\r\n';
  4. Query OK, 6 rows affected (0.00 sec)


  7. # 由於linux的一些命令本身會解析掉這些特殊字元，所以使用python來檢視這個文字檔案中的換行符，從下面的結果中可以看到，列表的每一個元素代表一行資料，每一個元素的\
  8. 末尾的\r\n就是這行資料的換行符


  11. >>> f = open('/tmp/test3.txt','r')
  12. >>> data = f.readlines()
  13. >>> data
  14. ['2\ta string\t100.20\r\n', '4\ta string containing a , comma\t102.20\r\n', '6\ta string containing a " quote\t102.20\r\n', '8\ta string containing a ", quote and comma\t102.20\r\n', '10\t\\\\t\t102.20\r\n', \
  15. '14\t\\\\t\t102.20\r\n']
  16. >>>


  19. # 現在，把資料重新匯入表，從下面的結果中可以看到，匯入表中的資料正確
  20. admin@localhost : xiaoboluo 04:02:39> truncate test3;
  21. Query OK, 0 rows affected (0.01 sec)


  24. admin@localhost : xiaoboluo 04:04:55> load data infile "/tmp/test3.txt" into table test3 lines terminated by '\r\n';
  25. Query OK, 6 rows affected (0.00 sec)
  26. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  29. admin@localhost : xiaoboluo 04:05:11> select * from test3;
  30. +----+------------------------------------------+--------+
  31. | id | test | test2 |
  32. +----+------------------------------------------+--------+
  33. | 2 | a string | 100.20 |
  34. | 4 | a string containing a , comma | 102.20 |
  35. | 6 | a string containing a " quote | 102.20 |
  36. | 8 | a string containing a ", quote and comma | 102.20 |
  37. | 10 | \t | 102.20 |
  38. | 14 | \t | 102.20 |
  39. +----+------------------------------------------+--------+
  40. 6 rows in set (0.00 sec)

1.2.6.3. FIELDS和LINES注意事項

  • 眾所周知，MySQL中反斜槓是SQL語句中特殊字元的轉義字元，因此在sql語句中碰到特殊字元時，您必須指定一個或者兩個反斜槓來為特殊字元轉義（如在mysql中或者一些其他程式中，\n代表換行符，\t代表製表符，\代表轉義符，那麼需要使用\t來轉義製表符，\n來轉義換行符，\來轉義轉義符本身，這樣才能正確寫入資料庫或者生成匯出的資料文字,使用FIELDS ESCAPED BY子句指定轉義符

  • 特殊字元列表如下


  1. \0 ASCII NUL (X'00') 字元
  2. \b 退格字元
  3. \n 換行符
  4. \r 回車符
  5. \t 製表符
  6. \Z ASCII 26 (Control+Z)
  7. \N NULL值，如果轉義符值為空，則會直接匯出null字串作為資料，這在匯入時將把null作為資料匯入，而不是null符號
  • 如果資料中包含了ENCLOSED BY '"'子句指定欄位引用符號，則與欄位引用符號相同資料字元也會被自動新增一個反斜槓進行轉義（如果轉義符指定為空，則可能會導致資料在匯入時無法正確解析）。如果資料中包含了FIELDS TERMINATED BY 子句指定的欄位分隔符，則以FIELDS ENCLOSED BY子句指定的欄位引用符號為準，被引起來的整個部分作為一整列的資料，列值之間的資料包含欄位分隔符不會被轉義，而是作為資料處理，但資料中包含的欄位引用符會被轉義(在資料中包含了欄位分隔符的情況下，如果欄位引用符號沒有指定或者指定為空值，則可能在匯入資料時無法正確解析）。如果資料中包含了FIELDS ESCAPED BY子句指定的轉義符，欄位引用符和行分隔符使用預設值，則在資料中的轉義符會被轉義（只要不為空，則不管欄位分隔符和轉義字元定義為什麼值，都會被轉義），預設情況下，不建議隨意更改換行符和轉義符，除非必須且你需要校驗修改之後資料能夠正確匯入

  1. # 欄位引用符為"，資料中包含"，轉義符和換行符保持預設，匯入資料時不會有任何問題
  2. admin@localhost : xiaoboluo 09:46:14> select * from test3;
  3. +----+------------------------------------------+--------+
  4. | id | test | test2 |
  5. +----+------------------------------------------+--------+
  6. | 2 | a string | 100.20 |
  7. | 4 | a string containing a , comma | 102.20 |
  8. | 6 | a string containing a " quote | 102.20 |
  9. | 8 | a string containing a ", quote and comma | 102.20 |
  10. +----+------------------------------------------+--------+
  11. 4 rows in set (0.00 sec)


  14. admin@localhost : xiaoboluo 09:46:17> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"';
  15. Query OK, 4 rows affected (0.00 sec)


  18. admin@localhost : xiaoboluo 09:46:23> system cat /tmp/test3.txt;
  19. 2 "a string" "100.20"
  20. 4 "a string containing a , comma" "102.20"
  21. 6 "a string containing a \" quote" "102.20"
  22. 8 "a string containing a \", quote and comma" "102.20" # 可以看到與欄位引用符相同的符號資料被轉義了


  25. admin@localhost : xiaoboluo 09:54:41> truncate test3;
  26. Query OK, 0 rows affected (0.01 sec)


  29. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';
  30. Query OK, 4 rows affected (0.00 sec)
  31. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  34. admin@localhost : xiaoboluo 09:58:45> select * from test3;
  35. +----+------------------------------------------+--------+
  36. | id | test | test2 |
  37. +----+------------------------------------------+--------+
  38. | 2 | a string | 100.20 |
  39. | 4 | a string containing a , comma | 102.20 |
  40. | 6 | a string containing a " quote | 102.20 |
  41. | 8 | a string containing a ", quote and comma | 102.20 |
  42. +----+------------------------------------------+--------+
  43. 4 rows in set (0.00 sec)


  46. # 如果欄位引用符為"，欄位分隔符為,且資料中包含欄位引用符"和欄位分隔符,，轉義符和換行符保持預設，這在匯入資料時不會有任何問題
  47. admin@localhost : xiaoboluo 09:53:45> select * from test3 into outfile "/tmp/test3.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';
  48. Query OK, 4 rows affected (0.00 sec)


  51. admin@localhost : xiaoboluo 09:54:29> system cat /tmp/test3.txt;
  52. 2,"a string","100.20"
  53. 4,"a string containing a , comma","102.20"
  54. 6,"a string containing a \" quote","102.20"
  55. 8,"a string containing a \", quote and comma","102.20"


  58. admin@localhost : xiaoboluo 09:54:41> truncate test3;
  59. Query OK, 0 rows affected (0.01 sec)


  62. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';
  63. Query OK, 4 rows affected (0.00 sec)
  64. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  67. admin@localhost : xiaoboluo 09:58:45> select * from test3;
  68. +----+------------------------------------------+--------+
  69. | id | test | test2 |
  70. +----+------------------------------------------+--------+
  71. | 2 | a string | 100.20 |
  72. | 4 | a string containing a , comma | 102.20 |
  73. | 6 | a string containing a " quote | 102.20 |
  74. | 8 | a string containing a ", quote and comma | 102.20 |
  75. +----+------------------------------------------+--------+
  76. 4 rows in set (0.00 sec)


  79. # 但是，如果在欄位引用符為"，資料中包含"，欄位分隔符使用逗號，換行符保持預設的情況下，轉義符使用了空串，這會導致在匯入資料時，第四行無法正確解析，報錯
  80. admin@localhost : xiaoboluo 09:58:01> load data infile '/tmp/test3.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',';
  81. Query OK, 4 rows affected (0.00 sec)
  82. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0


  85. admin@localhost : xiaoboluo 09:58:45> select * from test3;
  86. +----+------------------------------------------+--------+
  87. | id | test | test2 |
  88. +----+------------------------------------------+--------+
  89. | 2 | a string | 100.20 |
  90. | 4 | a string containing a , comma | 102.20 |
  91. | 6 | a string containing a " quote | 102.20 |
  92. | 8 | a string containing a ", quote and comma | 102.20 |
  93. +----+------------------------------------------+--------+
  94. 4 rows in set (0.00 sec)


  97. admin@localhost : xiaoboluo 09:58:49> select * from test3 into outfile "/tmp/test3_test.txt" FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by '';
  98. Query OK, 4 rows affected (0.00 sec)


  101. admin@localhost : xiaoboluo 10:00:42> system cat /tmp/test3_test.txt;
  102. 2,"a string","100.20"
  103. 4,"a string containing a , comma","102.20"
  104. 6,"a string containing a " quote","102.20" #關於這一行資料，需要說明一下ENCLOSED BY子句，該子句指定的引用符號從一個FIELDS TERMINATED BY子句指定的分隔符開始，直到碰到下一個\
  105. 分隔符之間且這個分隔符前面一個字元必須是欄位引用符號（如果這個分隔符前面一個字元不是欄位引用符，則繼續往後匹配，如第二行資料），在這之間的內容都會被當作整個列字串處理，\
  106. 所以這一行資料在匯入時不會發生解析錯誤


  109. 8,"a string containing a ", quote and comma","102.20" #這一行因為無法正確識別的欄位結束位置，所以無法匯入，報錯終止，前面正確的行也被回滾掉（binlog_format=row）
  110. admin@localhost : xiaoboluo 10:00:49> truncate test3;
  111. Query OK, 0 rows affected (0.01 sec)


  114. admin@localhost : xiaoboluo 10:01:03> load data infile '/tmp/test3_test.txt' into table test3 FIELDS OPTIONALLY enclosed BY '"' TERMINATED by ',' escaped by '';
  115. ERROR 1262 (01000): Row 4 was truncated; it contained more data than there were input columns
  116. admin@localhost : xiaoboluo 10:01:33> select * from test3;
  117. Empty set (0.00 sec)


  120. # 資料中包含了預設的轉義符和指定的欄位分隔符，欄位引用符和行分隔符使用預設值，則在資料中的轉義符和欄位分隔符會被轉義（只要不為空，則不管欄位分隔符和轉義字元定義為什麼值，\
  121. 都會被轉義）



  125. admin@localhost : xiaoboluo 03:08:45> insert into test3(test,test2) values('\\t','102.20');
  126. Query OK, 1 row affected (0.00 sec)


  129. admin@localhost : xiaoboluo 03:17:29> select * from test3;
  130. +----+------------------------------------------+--------+
  131. | id | test | test2 |
  132. +----+------------------------------------------+--------+
  133. | 2 | a string | 100.20 |
  134. | 4 | a string containing a , comma | 102.20 |
  135. | 6 | a string containing a " quote | 102.20 |
  136. | 8 | a string containing a ", quote and comma | 102.20 |
  137. | 10 | \t | 102.20 |
  138. +----+------------------------------------------+--------+
  139. 5 rows in set (0.00 sec)


  142. admin@localhost : xiaoboluo 03:17:32> system rm -f /tmp/test3.txt;
  143. admin@localhost : xiaoboluo 03:17:39> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',';
  144. Query OK, 5 rows affected (0.01 sec)


  147. admin@localhost : xiaoboluo 03:17:42> system cat /tmp/test3.txt
  148. 2,a string,100.20
  149. 4,a string containing a \, comma,102.20
  150. 6,a string containing a " quote,102.20
  151. 8,a string containing a "\, quote and comma,102.20
  152. 10,\\t,102.20
  • 當您使用SELECT … INTO OUTFILE與LOAD DATA INFILE一起將資料從資料庫寫入檔案，然後再將該檔案讀回資料庫時，兩個語句的FIELDS和LINES處理選項必須匹配。否則，LOAD DATA INFILE將解析錯誤的檔案內容，示例

  1. # 假設您執行SELECT ... INTO OUTFILE語句時使用了逗號作為列分隔符：
  2. SELECT * INTO OUTFILE 'data.txt'
  3. FIELDS TERMINATED BY ','
  4. FROM table2;


  7. # 如果您嘗試使用\t作為列分隔符，則它將無法正常工作，因為它會指示LOAD DATA INFILE在欄位之間查詢製表符，可能導致每個資料行整行解析時被當作單個欄位：

  9. LOAD DATA INFILE 'data.txt' INTO TABLE table2

  11. FIELDS TERMINATED BY '\t';


  14. # 要正確讀取逗號分隔各列的檔案，正確的語句是
  15. LOAD DATA INFILE 'data.txt' INTO TABLE table2
  16. FIELDS TERMINATED BY ',';
  • 任何FIELDS和LINES處理選項都可以指定一個空字串（''），但強烈不建議在FIELDS TERMINATED BY、FIELDS ESCAPED BY 和LINES TERMINATED BY子句中使用空串（空格不算空串）作為轉義符和換行符，可能導致許多意外的問題，除非你確定使用空串不會出現問題。如果不為空，注意FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY子句指定的值只能指定單個字元（即欄位引用符號和轉義符只能使用單個字元）。但 FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY子句的值可以是多個字元（即欄位分隔符和換行符、行字首字元可以使用多個字元）。例如，指定一個LINES TERMINATED BY'\r\ n'子句，表示指定行換行符為\r\n，這個也是WIN下的換行符

  1. # 如果LINES TERMINATED BY換行符指定了一個空字元，並且FIELDS TERMINATED BY欄位分隔符指定的是非空的一個字元(或者使用預設值\t)，則行也會以欄位分隔符作為行的結束符\
  2. （表現行為就是文字中最後一個字元就是欄位分隔符），即整個文字看上去就是一整行資料了


  5. admin@localhost : xiaoboluo 04:48:35> system rm -f /tmp/test3.txt;


  8. admin@localhost : xiaoboluo 04:53:59> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY ',' lines terminated by '';
  9. Query OK, 6 rows affected (0.00 sec)


  12. # 使用python檢視文字內容，從下面的結果中可以看到，整個表的資料由於換行符為空，所以導致都拼接為一行了，最後行結束符使用了欄位分隔符逗號
  13. >>> f = open('/tmp/test3.txt','r')
  14. >>> data = f.readlines()
  15. >>> data
  16. ['2,a string,100.20,4,a string containing a \\, comma,102.20,6,a string containing a " quote,102.20,8,a string containing a "\\, quote and comma,102.20,10,\\\\t,102.20,14,\\\\t,102.20,']
  17. >>>


  20. # 匯入資料到表，這裡新建一張表來進行匯入測試，預防清理掉了表資料之後，文字內容又無法正確匯入的情況發生
  21. admin@localhost : xiaoboluo 04:57:52> create table test4 like test3;
  22. Query OK, 0 rows affected (0.01 sec)


  25. admin@localhost : xiaoboluo 04:57:59> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY ',' lines terminated by '';
  26. Query OK, 6 rows affected (0.00 sec)
  27. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  30. admin@localhost : xiaoboluo 04:58:26> select * from test4; #從查詢結果上看，資料正確匯入表test4中了
  31. +----+------------------------------------------+--------+
  32. | id | test | test2 |
  33. +----+------------------------------------------+--------+
  34. | 2 | a string | 100.20 |
  35. | 4 | a string containing a , comma | 102.20 |
  36. | 6 | a string containing a " quote | 102.20 |
  37. | 8 | a string containing a ", quote and comma | 102.20 |
  38. | 10 | \t | 102.20 |
  39. | 14 | \t | 102.20 |
  40. +----+------------------------------------------+--------+
  41. 6 rows in set (0.00 sec)


  44. # 如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都為空（''），則使用固定行（非限制）格式。使用固定行格式時，欄位之間使用足夠寬的空格來分割各欄位。對於資料型別\
  45. 是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，欄位寬度分別為4,6,8,11和20個空格(無論資料型別宣告的顯示寬度如何)，對於varchar型別使用大約298個空格（這個空格數量是自己\
  46. 數的。。。，猜想這個空格數量可能與字符集，varchar定義長度有關，因為我在嘗試把varchar定義為50個字元的時候，空格少了156個左右）


  49. admin@localhost : xiaoboluo 04:58:31> system rm -f /tmp/test3.txt;
  50. admin@localhost : xiaoboluo 05:04:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS TERMINATED BY '' lines terminated by '';
  51. Query OK, 6 rows affected (0.00 sec)


  54. admin@localhost : xiaoboluo 05:04:17> system cat /tmp/test3.txt #下面展示內容中把打斷空格使用...代替
  55. 2 a string ... 100.20 ... 4 a string containing a , comma ... 102.20 ... 6 a string containing a " quote ...102.20 ... 8 a string containing a ", quote and comma ... 102.20 \
  56. ... 10 \\t ... 102.20 ... 14 \\t ... 102.20 ... admin@localhost : xiaoboluo 05:04:35>


  59. # 現在，清理掉test4表，並載入資料，從下面的結果中可以看到，匯入表中之後，雖然資料是對的，但是多了非常多的空格，那麼也就意味著你需要使用程式正確地處理一下這些多餘的空格之後，\
  60. 再執行匯入


  63. admin@localhost : xiaoboluo 05:06:19> truncate test4;
  64. Query OK, 0 rows affected (0.01 sec)


  67. admin@localhost : xiaoboluo 05:06:47> load data infile "/tmp/test3.txt" into table test4 FIELDS TERMINATED BY '' lines terminated by ''; # 注意：這是在sql_mode=''時匯入的，如果不修改\
  68. sql_mode請使用local關鍵字


  71. Query OK, 6 rows affected, 12 warnings (0.01 sec)
  72. Records: 6 Deleted: 0 Skipped: 0 Warnings: 12


  75. Note (Code 1265): Data truncated for column 'test' at row 1
  76. Note (Code 1265): Data truncated for column 'test2' at row 1
  77. Note (Code 1265): Data truncated for column 'test' at row 2
  78. Note (Code 1265): Data truncated for column 'test2' at row 2
  79. Note (Code 1265): Data truncated for column 'test' at row 3
  80. Note (Code 1265): Data truncated for column 'test2' at row 3
  81. Note (Code 1265): Data truncated for column 'test' at row 4
  82. Note (Code 1265): Data truncated for column 'test2' at row 4
  83. Note (Code 1265): Data truncated for column 'test' at row 5
  84. Note (Code 1265): Data truncated for column 'test2' at row 5
  85. Note (Code 1265): Data truncated for column 'test' at row 6
  86. Note (Code 1265): Data truncated for column 'test2' at row 6
  87. admin@localhost : xiaoboluo 05:07:09> select * from test4;
  88. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
  89. | id | test | test2 |
  90. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
  91. | 2 | a string | 100.20 |
  92. | 4 | a string containing a , comma | 102.20 |
  93. | 6 | a string containing a " quote | 102.20 |
  94. | 8 | a string containing a ", quote and comma | 102.20 |
  95. | 10 | \t | 102.20 |
  96. | 14 | \t | 102.20 |
  97. +----+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
  98. 6 rows in set (0.00 sec)
  • NULL值的處理根據使用的FIELDS和LINES選項而有所不同

  1. # 對於預設的FIELDS和LINES值，NULL值被轉義為\N輸出，欄位值\N讀取時使用NULL替換並輸入（假設ESCAPED BY字元為\）
  2. admin@localhost : xiaoboluo 05:17:07> alter table test3 add column test3 varchar(10); #新增一個欄位test3，預設值會被填充為null
  3. Query OK, 0 rows affected (0.04 sec)
  4. Records: 0 Duplicates: 0 Warnings: 0


  7. admin@localhost : xiaoboluo 05:17:33> select * from test3; #檢視錶中的test3列資料
  8. +----+------------------------------------------+--------+-------+
  9. | id | test | test2 | test3 |
  10. +----+------------------------------------------+--------+-------+
  11. | 2 | a string | 100.20 | NULL |
  12. | 4 | a string containing a , comma | 102.20 | NULL |
  13. | 6 | a string containing a " quote | 102.20 | NULL |
  14. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  15. | 10 | \t | 102.20 | NULL |
  16. | 14 | \t | 102.20 | NULL |
  17. +----+------------------------------------------+--------+-------+
  18. 6 rows in set (0.00 sec)


  21. admin@localhost : xiaoboluo 05:17:37> select * from test3 into outfile "/tmp/test3.txt"; #執行匯出
  22. Query OK, 6 rows affected (0.00 sec)


  25. admin@localhost : xiaoboluo 05:18:02> system cat /tmp/test3.txt #檢視匯出的文字檔案，可以發現null被轉義為\N了，這是為了避免資料字串本身包含null值時無法正確區分資料型別的null值
  26. 2 a string 100.20 \N
  27. 4 a string containing a , comma 102.20 \N
  28. 6 a string containing a " quote 102.20 \N
  29. 8 a string containing a ", quote and comma 102.20 \N
  30. 10 \\t 102.20 \N
  31. 14 \\t 102.20 \N


  34. # 匯入資料，從結果中可以看到\N被正確解析為了資料型別的null值
  35. admin@localhost : xiaoboluo 05:18:06> truncate test3;
  36. Query OK, 0 rows affected (0.01 sec)


  39. admin@localhost : xiaoboluo 05:20:36> load data infile '/tmp/test3.txt' into table test3;
  40. Query OK, 6 rows affected (0.01 sec)
  41. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  44. admin@localhost : xiaoboluo 05:20:52> select * from test3;
  45. +----+------------------------------------------+--------+-------+
  46. | id | test | test2 | test3 |
  47. +----+------------------------------------------+--------+-------+
  48. | 2 | a string | 100.20 | NULL |
  49. | 4 | a string containing a , comma | 102.20 | NULL |
  50. | 6 | a string containing a " quote | 102.20 | NULL |
  51. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  52. | 10 | \t | 102.20 | NULL |
  53. | 14 | \t | 102.20 | NULL |
  54. +----+------------------------------------------+--------+-------+
  55. 6 rows in set (0.00 sec)


  58. # 如果FIELDS ENCLOSED BY不為空，FIELDS escaped BY為空時，則將NULL值的字面字串作為輸出字元值。這與FIELDS ENCLOSED BY引用的字串中包含的null值不同，\
  59. 後者讀取為字串'null'，而前者讀取到資料庫中時被當作資料型別的null值，而不是資料的字串null


  62. admin@localhost : xiaoboluo 05:20:57> update test3 set test3='null' where id=2; #更新id=2的test3列值為資料字串的null
  63. Query OK, 1 row affected (0.00 sec)
  64. Rows matched: 1 Changed: 1 Warnings: 0


  67. admin@localhost : xiaoboluo 05:23:14> select * from test3;
  68. +----+------------------------------------------+--------+-------+
  69. | id | test | test2 | test3 |
  70. +----+------------------------------------------+--------+-------+
  71. | 2 | a string | 100.20 | null |
  72. | 4 | a string containing a , comma | 102.20 | NULL |
  73. | 6 | a string containing a " quote | 102.20 | NULL |
  74. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  75. | 10 | \t | 102.20 | NULL |
  76. | 14 | \t | 102.20 | NULL |
  77. +----+------------------------------------------+--------+-------+
  78. 6 rows in set (0.00 sec)


  81. admin@localhost : xiaoboluo 05:23:16> system rm -f /tmp/test3.txt;
  82. admin@localhost : xiaoboluo 05:24:05> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '"' escaped BY ''; #指定行引用符號為雙引號"，轉義符為空匯出資料
  83. Query OK, 6 rows affected (0.00 sec)


  86. admin@localhost : xiaoboluo 05:24:51> system cat /tmp/test3.txt #檢視匯出的文字檔案，可以看到資料字串的null被加了雙引號，而資料型別的null沒有加雙引號
  87. "2" "a string" "100.20" "null"
  88. "4" "a string containing a , comma" "102.20" NULL
  89. "6" "a string containing a " quote" "102.20" NULL
  90. "8" "a string containing a ", quote and comma" "102.20" NULL
  91. "10" "\t" "102.20" NULL
  92. "14" "\t" "102.20" NULL


  95. admin@localhost : xiaoboluo 05:24:57> alter table test4 add column test3 varchar(10);
  96. Query OK, 0 rows affected (0.04 sec)
  97. Records: 0 Duplicates: 0 Warnings: 0


  100. admin@localhost : xiaoboluo 05:26:40> truncate test4; #這裡使用test4表做測試，避免無法匯入的情況發生
  101. Query OK, 0 rows affected (0.00 sec)


  104. admin@localhost : xiaoboluo 05:26:44> load data infile '/tmp/test3.txt' into table test4 FIELDS ENCLOSED BY '"' escaped BY ''; #指定欄位引用符為雙引號"，轉義符為空匯入資料
  105. Query OK, 6 rows affected (0.00 sec)
  106. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  109. admin@localhost : xiaoboluo 05:27:02> select * from test4; #檢視錶test4中的資料，從結果中可以看到，資料匯入正確
  110. +----+------------------------------------------+--------+-------+
  111. | id | test | test2 | test3 |
  112. +----+------------------------------------------+--------+-------+
  113. | 2 | a string | 100.20 | null |
  114. | 4 | a string containing a , comma | 102.20 | NULL |
  115. | 6 | a string containing a " quote | 102.20 | NULL |
  116. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  117. | 10 | \t | 102.20 | NULL |
  118. | 14 | \t | 102.20 | NULL |
  119. +----+------------------------------------------+--------+-------+
  120. 6 rows in set (0.00 sec)


  123. # 使用固定行格式（當FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空時使用），將NULL寫為空字串。這將導致表中的NULL值和空字串在寫入檔案時無法區分，\
  124. 因為它們都以空字串形式寫入文字檔案。如果您需要能夠在讀取檔案時將其分開，則不應使用固定行格式（即不應該使用FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空）


  127. admin@localhost : xiaoboluo 05:29:11> system rm -f /tmp/test3.txt;
  128. admin@localhost : xiaoboluo 05:29:22> select * from test3 into outfile "/tmp/test3.txt" FIELDS ENCLOSED BY '' TERMINATED BY '';
  129. Query OK, 6 rows affected (0.00 sec)


  132. admin@localhost : xiaoboluo 05:29:43> system cat /tmp/test3.txt #從結果中看，是不是有點似曾相識呢？沒錯，前面演示過FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空的情況，\
  133. 使用了固定格式來匯出文字，但是這裡多了資料型別的null值處理，從下面的結果中已經看不到資料型別的null了，被轉換為了空值(下面展示時把大段空格使用...代替)


  136. 2 a string ... 100.20 ... null
  137. 4 a string containing a , comma ... 102.20 ...
  138. 6 a string containing a " quote ... 102.20 ...
  139. 8 a string containing a ", quote and comma ... 102.20 ...
  140. 10 \\t ... 102.20 ...
  141. 14 \\t ... 102.20 ...
  142. admin@localhost : xiaoboluo 05:29:46> truncate test4; #清空test4
  143. Query OK, 0 rows affected (0.01 sec)


  146. admin@localhost : xiaoboluo 05:34:15> load data infile "/tmp/test3.txt" into table test4 FIELDS ENCLOSED BY '' TERMINATED BY ''; #執行匯入文字到test4表，注意：這是在sql_mode=''時匯入的，\
  147. 如果不修改sql_mode請使用local關鍵字


  150. Query OK, 6 rows affected, 24 warnings (0.01 sec)
  151. Records: 6 Deleted: 0 Skipped: 0 Warnings: 24


  154. Note (Code 1265): Data truncated for column 'test' at row 1
  155. Note (Code 1265): Data truncated for column 'test2' at row 1
  156. Note (Code 1265): Data truncated for column 'test3' at row 1
  157. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
  158. Note (Code 1265): Data truncated for column 'test' at row 2
  159. Note (Code 1265): Data truncated for column 'test2' at row 2
  160. Note (Code 1265): Data truncated for column 'test3' at row 2
  161. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
  162. Note (Code 1265): Data truncated for column 'test' at row 3
  163. Note (Code 1265): Data truncated for column 'test2' at row 3
  164. Note (Code 1265): Data truncated for column 'test3' at row 3
  165. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
  166. Note (Code 1265): Data truncated for column 'test' at row 4
  167. Note (Code 1265): Data truncated for column 'test2' at row 4
  168. Note (Code 1265): Data truncated for column 'test3' at row 4
  169. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
  170. Note (Code 1265): Data truncated for column 'test' at row 5
  171. Note (Code 1265): Data truncated for column 'test2' at row 5
  172. Note (Code 1265): Data truncated for column 'test3' at row 5
  173. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
  174. Note (Code 1265): Data truncated for column 'test' at row 6
  175. Note (Code 1265): Data truncated for column 'test2' at row 6
  176. Note (Code 1265): Data truncated for column 'test3' at row 6
  177. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns
  178. admin@localhost : xiaoboluo 05:34:35> select * from test4; #查詢test4表，從下面的結果中可以看到，原本test2欄位的資料被匯入到了test3欄位，而test3欄位的內容被截斷了。。
  179. +----+----------------------------------------------------+----------------------------------------------------+------------+
  180. | id | test | test2 | test3 |
  181. +----+----------------------------------------------------+----------------------------------------------------+------------+
  182. | 2 | a string | | 100.20 |
  183. | 4 | a string containing a , comma | | 102.20 |
  184. | 6 | a string containing a " quote | | 102.20 |
  185. | 8 | a string containing a ", quote and comma | | 102.20 |
  186. | 10 | \t | | 102.20 |
  187. | 14 | \t | | 102.20 |
  188. +----+----------------------------------------------------+----------------------------------------------------+------------+
  189. 6 rows in set (0.00 sec)


  192. # 注意：如果使用多位元組字符集，固定大小格式可能不起作用（我在測試時使用的字符集是utf8，沒有測試出來這裡說不起作用是啥意思）

  • load data執行時如果表中有外來鍵、輔助索引、唯一索引，那麼會導致載入資料的時間變慢，因為索引也需要一同更新，可以使用對應引數關閉外來鍵檢查、唯一索引檢查甚至關閉索引

    • 要在載入操作期間忽略外來鍵約束，可以在執行load data語句之前執行SET foreign_key_checks = 0語句，執行完畢之後執行SET foreign_key_checks = 1或斷開會話重連
    • 要在載入操作期間忽略唯一索引約束，可以在執行load data語句之前執行set unique_checks=0語句，執行完畢之後執行set unique_checks=1或斷開會話重連
    • 在某些極端情況下（比如表中索引過多），您可以在執行load data語句之前通過執行ALTER TABLE … DISABLE KEYS語句關閉建立索引，在執行完load data語句之後執行ALTER TABLE … ENABLE KEYS來重新建立索引，注意該語句不能關閉主鍵索引

  • 如果在sql_mode設定為嚴格模式下，且不使用local和ignore關鍵字時，碰到缺少欄位值會直接報錯終止，但在sql_mode設定為嚴格模式下，使用了local和ignore關鍵字時，則行為與不使用嚴格模式類似 
    LOAD DATA INFILE將所有輸入視為字串，因此您不能認為load data語句會像INSERT語句那樣插入ENUM或SET列的數值。所有ENUM和SET值必須指定為字串

  • LOAD DATA INFILE不支援的場景

    • 固定大小的行（FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空）不支援BLOB或TEXT列
    • 如果FIELDS TERMINATED BY和LINES STARTING BY指定相同的字元，則LOAD DATA INFILE無法正確解析
    • 如果FIELDS ESCAPED BY為空，則欄位中包含了FIELDS ENCLOSED BY或LINES TERMINATED BY或FIELDS TERMINATED BY的字元時會導致LOAD DATA INFILE語句拒絕讀取欄位並報錯。這是因為LOAD DATA INFILE無法正確確定欄位或行在哪裡結束

  • PS：在Unix上，如果需要LOAD DATA從管道讀取資料，可以使用以下方法（該示例將/目錄的列表載入到表db1.t1中，find命令掛後臺持續查詢內容並生成ls.dat檔案，mysql 客戶端使用-e選項來執行load data這個檔案到表）：


  1. mkfifo /mysql/data/db1/ls.dat
  2. chmod 666 /mysql/data/db1/ls.dat
  3. find / -ls> /mysql/data/db1/ls.dat＆
  4. mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

1.2.7. IGNORE number {LINES | ROWS}子句

  • 忽略輸入檔案中的前number行資料，使用子句ignore number lines指定忽略文字的前number行，在某些情況下生成的文字（如：mysql -e "select …." > xx.txt中）帶有欄位名稱，在匯入時會把這一行欄位名稱也當作資料，所以需要忽略掉這行欄位名稱

  1. admin@localhost : xiaoboluo 05:34:41> system cat /tmp/test3.txt
  2. id test test2 test3
  3. 2 a string 100.20 null
  4. 4 a string containing a , comma 102.20 NULL
  5. 6 a string containing a " quote 102.20 NULL
  6. 8 a string containing a ", quote and comma 102.20 NULL
  7. 10 \\t 102.20 NULL
  8. 14 \\t 102.20 NULL
  9. admin@localhost : xiaoboluo 05:41:35> truncate test4;
  10. Query OK, 0 rows affected (0.01 sec)


  13. admin@localhost : xiaoboluo 05:41:41> load data infile "/tmp/test3.txt" into table test4 ignore 1 lines; #載入文字時指定ignore 1 lines子句忽略文字中的前1行資料
  14. Query OK, 6 rows affected (0.00 sec)
  15. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  18. admin@localhost : xiaoboluo 05:42:22> select * from test4; #查詢表test4中的資料，從下面的結果中可以看到資料正確
  19. +----+------------------------------------------+--------+-------+
  20. | id | test | test2 | test3 |
  21. +----+------------------------------------------+--------+-------+
  22. | 2 | a string | 100.20 | null |
  23. | 4 | a string containing a , comma | 102.20 | NULL |
  24. | 6 | a string containing a " quote | 102.20 | NULL |
  25. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  26. | 10 | \t | 102.20 | NULL |
  27. | 14 | \t | 102.20 | NULL |
  28. +----+------------------------------------------+--------+-------+
  29. 6 rows in set (0.00 sec)
  • LOAD DATA INFILE可用於讀取外部資料來源檔案。例如，許多程式可以以逗號分隔的值（CSV）格式匯出資料，欄位用逗號分隔，幷包含在雙引號內，並帶有一個欄位列名的初始行。如果這樣一個檔案中的資料行的換行符再使用回車符，則load data語句可以這樣編寫：

  1. LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  2. FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  3. LINES TERMINATED BY '\r\n'
  4. IGNORE 1 LINES;
  5. # 如果輸入值不一定包含在引號內，請在ENCLOSED BY關鍵字之前使用OPTIONALLY，如：OPTIONALLY ENCLOSED BY '"'，加上OPTIONALLY 可能會忽略數值型別的欄位的引用符號，\
  6. 另外，如果你的csv檔案第一行是資料而不是列名，那就不能使用IGNORE 1 LINES子句

1.2.8. (col_name_or_user_var,…)指定欄位名稱的子句

  • 預設情況下，如果使用load data語句時表名後邊不帶欄位，那麼會把整個表的欄位資料都匯入到資料庫中，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata;

  • 如果只想載入某些列，請指定列列表，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata（col1，col2，…）; ，要注意：如果輸入檔案中的欄位值順序與表中列的順序不同，你需要對load data語句中的tb_name後跟的欄位順序做一下調整以對應文字檔案中的欄位順序。否則，MySQL不能判斷如何與表中的順序對齊，列出列名時可以在tb_name後指定具體的列名，也可以使用表示式生成值指定給某個列名(使用set語句指定一個表示式，複製給一個變數，詳見1.2.9小節)，如果沒有set語句，建議列名寫在tb_name表名後邊，方便理解，有set語句時就跟set語句寫在一起

    • 如果發現檔案中的列順序和表中的列順序不符，或者只想載入部分列，在命令中加上列的順序時指定的欄位名也不一定非要放在緊跟著表名，可以放在語句最後面也可以，如：load data infile "/tmp/filename.txt" into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);如果只需要匯入一個欄位id，則把 (id,content,name)換做(id)即可

    • 使用示例參考1.1小節的“如果文字檔案中的資料欄位與表結構中的欄位定義順序不同，則使用如下語句指定載入表中的欄位順序”演示部分

1.2.8. SET col_name = expr,…子句

  • 將列做一定的數值轉換後再載入，使用子句set col_name = expr,.. 指定，要注意：col_name必須為表中真實的列名，expr可以是任意的表示式或者子查詢，只要返回的資料結果值能對應上表中的欄位資料定義型別即可，注意，非set語句生成的列名，必須使用括號括起來，否則報語法錯誤。

  1. # 如果系統將id列的文字資料加上10以後再載入到表的test3列中，可以如下操作：

  3. admin@localhost : xiaoboluo 06:05:42> system rm -f /tmp/test3.txt;
  4. admin@localhost : xiaoboluo 06:06:00> select * from test3 into outfile "/tmp/test3.txt";
  5. Query OK, 6 rows affected (0.00 sec)


  8. admin@localhost : xiaoboluo 06:06:04> system cat /tmp/test3.txt
  9. 2 a string 100.20 null
  10. 4 a string containing a , comma 102.20 \N
  11. 6 a string containing a " quote 102.20 \N
  12. 8 a string containing a ", quote and comma 102.20 \N
  13. 10 \\t 102.20 \N
  14. 14 \\t 102.20 \N


  17. admin@localhost : xiaoboluo 06:07:49> truncate test4;
  18. Query OK, 0 rows affected (0.01 sec)


  21. admin@localhost : xiaoboluo 06:07:53> load data infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+10 ;
  22. ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
  23. admin@localhost : xiaoboluo 06:08:02> select * from test4; #嚴格模式下因為文字中多了一個欄位被截斷了，所以拒絕匯入
  24. Empty set (0.00 sec)


  27. admin@localhost : xiaoboluo 06:08:08> load data local infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+10 ; #可以使用local關鍵字強制進行截斷最後一個欄位的null值列進行匯入，\
  28. 注意，如果不使用local關鍵字，那就需要修改sql_mode才能匯入


  31. Query OK, 6 rows affected, 6 warnings (0.01 sec)
  32. Records: 6 Deleted: 0 Skipped: 0 Warnings: 6


  35. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
  36. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
  37. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
  38. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
  39. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
  40. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns
  41. admin@localhost : xiaoboluo 06:10:45> select * from test4;
  42. +----+------------------------------------------+--------+-------+
  43. | id | test | test2 | test3 |
  44. +----+------------------------------------------+--------+-------+
  45. | 2 | a string | 100.20 | 12 |
  46. | 4 | a string containing a , comma | 102.20 | 14 |
  47. | 6 | a string containing a " quote | 102.20 | 16 |
  48. | 8 | a string containing a ", quote and comma | 102.20 | 18 |
  49. | 10 | \t | 102.20 | 20 |
  50. | 14 | \t | 102.20 | 24 |
  51. +----+------------------------------------------+--------+-------+
  52. 6 rows in set (0.00 sec)




  57. # 或者使用txt檔案中的某些列進行計算後生成新的列插入，這裡演示兩個欄位進行相加後匯入另外一個欄位中：
  58. admin@localhost : xiaoboluo 06:18:37> load data local infile "/tmp/test3.txt" into table test4 (id,test,test2) set test3=id+test2 ; # 注意，如果不使用local關鍵字，那就需要修改sql_mode才能匯入
  59. Query OK, 6 rows affected, 6 warnings (0.00 sec)
  60. Records: 6 Deleted: 0 Skipped: 0 Warnings: 6


  63. Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
  64. Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
  65. Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
  66. Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
  67. Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
  68. Warning (Code 1262): Row 6 was truncated; it contained more data than there were input columns
  69. admin@localhost : xiaoboluo 06:19:07> select * from test4;
  70. +----+------------------------------------------+--------+-------+
  71. | id | test | test2 | test3 |
  72. +----+------------------------------------------+--------+-------+
  73. | 2 | a string | 100.20 | 102.2 |
  74. | 4 | a string containing a , comma | 102.20 | 106.2 |
  75. | 6 | a string containing a " quote | 102.20 | 108.2 |
  76. | 8 | a string containing a ", quote and comma | 102.20 | 110.2 |
  77. | 10 | \t | 102.20 | 112.2 |
  78. | 14 | \t | 102.20 | 116.2 |
  79. +----+------------------------------------------+--------+-------+
  80. 6 rows in set (0.00 sec)
  • SET子句中使用使用者變數，使用者變數可以以多種方式使用

  1. # 可以直接使用一個使用者變數並進行計算(計算表示式可以使用函式、運算子、子查詢等都允許)，然後賦值給test4列直接匯入，而不需要從檔案中讀取test4列資料，該列資料也允許在檔案中不存在
  2. admin@localhost : xiaoboluo 06:27:06> alter table test4 add column test4 varchar(20); #新建一個欄位test4，用於匯入set子句計算的值
  3. Query OK, 0 rows affected (0.01 sec)
  4. Records: 0 Duplicates: 0 Warnings: 0


  7. admin@localhost : xiaoboluo 06:27:56> truncate test4;
  8. Query OK, 0 rows affected (0.01 sec)


  11. admin@localhost : xiaoboluo 06:28:02> set @test=200; #設定一個使用者變數
  12. Query OK, 0 rows affected (0.00 sec)


  15. admin@localhost : xiaoboluo 06:30:32> load data infile "/tmp/test3.txt" into table test4 (id,test,test2,test3) set test4=round(@test/100,0) ; #執行匯入，使用set子句匯入test4列通過表示式\
  16. round(@test/100,0)計算之後的值


  19. Query OK, 6 rows affected (0.00 sec)
  20. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  23. admin@localhost : xiaoboluo 06:30:52> select * from test4; #檢視test4表中匯入的資料，從以下結果中來看，匯入資料正確
  24. +----+------------------------------------------+--------+-------+-------+
  25. | id | test | test2 | test3 | test4 |
  26. +----+------------------------------------------+--------+-------+-------+
  27. | 2 | a string | 100.20 | null | 2 |
  28. | 4 | a string containing a , comma | 102.20 | NULL | 2 |
  29. | 6 | a string containing a " quote | 102.20 | NULL | 2 |
  30. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2 |
  31. | 10 | \t | 102.20 | NULL | 2 |
  32. | 14 | \t | 102.20 | NULL | 2 |
  33. +----+------------------------------------------+--------+-------+-------+
  34. 6 rows in set (0.00 sec)


  37. # SET子句可以將一個內部函式返回的值直接匯入到一個指定列
  38. admin@localhost : xiaoboluo 06:31:22> truncate test4;
  39. Query OK, 0 rows affected (0.01 sec)


  42. admin@localhost : xiaoboluo 06:40:58> load data infile "/tmp/test3.txt" into table test4 (id,test,test2,test3) set test4=now() ;

  44. Query OK, 6 rows affected (0.00 sec)
  45. Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  48. admin@localhost : xiaoboluo 06:41:02> select * from test4;
  49. +----+------------------------------------------+--------+-------+---------------------+
  50. | id | test | test2 | test3 | test4 |
  51. +----+------------------------------------------+--------+-------+---------------------+
  52. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |
  53. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  54. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |
  55. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  56. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  57. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  58. +----+------------------------------------------+--------+-------+---------------------+
  59. 6 rows in set (0.00 sec)

  • 使用指定列名或者變數列表時SET子句的使用受以下限制：

    • SET子句中的賦值表示式賦值運算子的左側只能使用資料庫表中的真實列名
    • 您可以在SET子句中的右側使用子查詢。返回要分配給列的值的子查詢可能僅是標量子查詢。此外，在這個子查詢中您不能使用load data語句正在操作的表
    • SET子句不會處理IGNORE子句忽略的行。
    • 用固定行格式載入資料時，不能使用使用者變數，因為使用者變數值之間沒有顯示寬度

  • 如果輸入行的欄位太多（多過表中的欄位數量），則會忽略額外的欄位，並增加警告數。如果輸入行的欄位太少，那麼輸入欄位缺少的表列被設定為其預設值，在解析文字檔案時，空串欄位資料與缺少欄位值不同（空串會直接作為資料插入，而缺少欄位時，會根據欄位定義的預設值進行填充），如下：

    • 對於字串型別，列設定為空字串
    • 對於數字型別，列設定為0
    • 對於日期和時間型別，列將該型別設定為適當的“零”值

2、批量匯出和批量匯入

  • 使用mysqldump批量匯出生成表的txt檔案，並使用mysqlimport批量匯入表的txt檔案到資料庫中

2.1. 使用mysqldump批量匯出

  • 使用mysqldump匯出資料為文字的語法如下：

  1. mysqldump -u username -p'xxx' -T target_dir db_name tb_name [option];

  • 其中option引數是以下幾種可選引數： 
    --fields-terminated-by 'string' 欄位分隔符 
    --fields-enclosed-by 'char' 欄位引用符 
    --fields-optionally-enclosed-by 'char' 欄位引用符，只在char,varchar,text等欄位型別上生效 
    --fields-escaped-by 'char' 轉義字元 
    --lines-terminated-by 'string' 記錄結束符，即換行符

  • 示例


  1. $ mkdir /data/backup/
  2. $ chown mysql.mysql /data/backup -R
  3. $ mysqldump -uadmin -pletsg0 -h 10.10.30.241 --single-transaction --master-data=2 --triggers --routines --events xiaoboluo -T /data/backup/
  4. mysqldump: [Warning] Using a password on the command line interface can be insecure.
  5. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.\
  6. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
  7. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
  8. SET @@SESSION.SQL_LOG_BIN= 0;


  11. --
  12. -- GTID state at the beginning of the backup
  13. --


  16. SET @@GLOBAL.GTID_PURGED='2016f827-2d98-11e7-bb1e-00163e407cfb:1-114';


  19. --
  20. -- Position to start replication or point-in-time recovery from
  21. --


  24. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=21737; #使用-T選項時，--master-data=2列印的binlog pos資訊會直接列印在標準輸出上


  27. --
  28. -- Dumping events for database 'xiaoboluo'
  29. --


  32. --
  33. -- Dumping routines for database 'xiaoboluo'
  34. --
  35. SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;


  38. $ ll /data/backup/ #可以看到mysqldump -T選項批量匯出的表資料除了文字檔案之外，還同時備份了表結構
  39. total 32
  40. -rw-r--r-- 1 root root 1526 May 3 22:45 test2.sql
  41. -rw-rw-rw- 1 mysql mysql 286 May 3 22:45 test2.txt
  42. -rw-r--r-- 1 root root 1549 May 3 22:45 test3.sql
  43. -rw-rw-rw- 1 mysql mysql 194 May 3 22:45 test3.txt
  44. -rw-r--r-- 1 root root 1600 May 3 22:45 test4.sql
  45. -rw-rw-rw- 1 mysql mysql 314 May 3 22:45 test4.txt
  46. -rw-r--r-- 1 mysql mysql 1493 May 3 22:45 test.sql
  47. -rw-rw-rw- 1 mysql mysql 95 May 3 22:45 test.txt

2.2. 使用mysqimport批量匯出

2.2.1. mysqlimport語法及其引數說明

  • mysqlimport實用程式載入資料檔案時，它通過向伺服器傳送LOAD DATA INFILE語句來實現(它實際是客戶端提供了load data infile語句的一個命令列介面)，可以使用--local選項使mysqlimport從客戶端主機（而不是mysql server主機）讀取資料檔案。如果客戶端和伺服器支援壓縮協議，則可以指定--compress選項以在慢速網路中獲得更好的效能。

  • 使用mysqlimport命令，語法如下：


  1. mysqlimport -uroot -p 'xxx' [--local] db_name order_tab.txt [iption]
  • 其中，option引數可以是如下選項

  1. --fields-terminated-by=name 指定欄位分隔符
  2. --fields-enclosed-by=name 指定欄位引用符
  3. --fields-optionally-enclosed-by=name 指定欄位引用符，但只在char、varchar、text欄位上使用引用符
  4. --fields-escaped-by=name 指定轉義字元
  5. --lines-terminated-by=name 指定行記錄結束符（換行符）
  6. --ignore-liens=number 忽略前幾行
  7. --low-priority 碰到有其他執行緒update操作操作的表與匯入操作表相同時，延遲執行匯入操作
  8. -i, --ignore 如果碰到唯一鍵衝突就忽略衝突行匯入

  10. -r, --replace 如果碰到唯一鍵衝突就覆蓋衝突行匯入

  12. -L, --local 從客戶端主機載入資料文字檔案

  14. -C, --compress 在C/S模型之間使用壓縮傳輸資料

  16. -c, --columns=name 指定需要匯入哪些列，與load data語句中一樣需要指定表定義中真實的列名，有多個列名時使用逗號分隔

  18. --default-character-set=name 設定使用該選項指定的字符集來解析文字檔案中的內容

  20. -h, --host 指定匯入server的主機IP

  22. -p, --password[=name] 指定匯入server的使用者密碼

  24. -P, --port=# 指定匯入server的監聽埠

  26. --use-threads=# 指定多少個執行緒併發執行load data語句（實測單表時指定多執行緒時要比單執行緒要快，由於資料量小，測試出來的差別並不大，官方並沒有說明是基於什麼級別的併發，\
  27. 只寫了一句：Load files in parallel using N threads，推測可能是基於類似mydumper的併發，但是多表匯入時指定多執行緒就明顯比單執行緒要快很多）



  31. -u, --user=name 指定匯入server的使用者名稱

  33. -d, --delete 指定匯入操作之前先把表清空（實測重複匯入時加了這個選項之後可以正常執行，，通過解析binlog發現，發現binlog中記錄的第二次和第一次匯入的語句完全相同是，\
  34. 第二次匯入時如果發現表中有衝突資料，就先執行的不帶where條件的delete，所有表先delete掉，然後再執行load data語句匯入資料，另外，當與replace一起使用時，忽略replace選項）

2.2.2. mysqlimport用法演示示例

  • 先執行清理server中表的資料


  2. admin@localhost : (none) 11:08:58> use xiaoboluo
  3. Database changed
  4. admin@localhost : xiaoboluo 11:09:00> show tables;
  5. +---------------------+
  6. | Tables_in_xiaoboluo |
  7. +---------------------+
  8. | test |
  9. | test2 |
  10. | test3 |
  11. | test4 |
  12. +---------------------+
  13. 4 rows in set (0.00 sec)


  16. admin@localhost : xiaoboluo 11:09:01> select * from test;
  17. +----+------+-------+
  18. | id | test | test2 |
  19. +----+------+-------+
  20. | 2 | 1 | 2 |
  21. | 4 | 2 | NULL |
  22. | 6 | null | NULL |
  23. | 8 | 4 | NULL |
  24. | 10 | | NULL |
  25. | 12 | \\t | NULL |
  26. | 14 | t | NULL |
  27. | 16 | \t | NULL |
  28. | 18 | t | NULL |
  29. | 20 | NULL | NULL |
  30. | 22 | "t | NULL |
  31. +----+------+-------+
  32. 11 rows in set (0.00 sec)


  35. admin@localhost : xiaoboluo 11:09:11> system cat /data/backup/test.txt;
  36. 2 1 2
  37. 4 2 \N
  38. 6 null \N
  39. 8 4 \N
  40. 10 \ \N
  41. 12 \\\\t \N
  42. 14 t \N
  43. 16 \\t \N
  44. 18 t \N
  45. 20 \N \N
  46. 22 "t \N


  49. admin@localhost : xiaoboluo 11:12:08> select * from test2;
  50. +----+------+-------+---------------------+
  51. | id | test | test2 | dt |
  52. +----+------+-------+---------------------+
  53. | 2 | 1 | 2 | 2017-05-02 18:47:03 |
  54. | 4 | 2 | NULL | 2017-05-02 18:47:03 |
  55. | 6 | null | NULL | 2017-05-02 18:47:03 |
  56. | 8 | 4 | NULL | 2017-05-02 18:47:03 |
  57. | 10 | | NULL | 2017-05-02 18:47:03 |
  58. | 12 | \\t | NULL | 2017-05-02 18:47:03 |
  59. | 14 | t | NULL | 2017-05-02 18:47:03 |
  60. | 16 | \t | NULL | 2017-05-02 18:47:03 |
  61. | 18 | t | NULL | 2017-05-02 18:47:03 |
  62. | 20 | NULL | NULL | 2017-05-02 18:47:03 |
  63. +----+------+-------+---------------------+
  64. 10 rows in set (0.00 sec)


  67. admin@localhost : xiaoboluo 11:12:15> system cat /data/backup/test2.txt;
  68. 2 1 2 2017-05-02 18:47:03
  69. 4 2 \N 2017-05-02 18:47:03
  70. 6 null \N 2017-05-02 18:47:03
  71. 8 4 \N 2017-05-02 18:47:03
  72. 10 \ \N 2017-05-02 18:47:03
  73. 12 \\\\t \N 2017-05-02 18:47:03
  74. 14 t \N 2017-05-02 18:47:03
  75. 16 \\t \N 2017-05-02 18:47:03
  76. 18 t \N 2017-05-02 18:47:03
  77. 20 \N \N 2017-05-02 18:47:03
  78. admin@localhost : xiaoboluo 11:12:27> truncate test2;
  79. Query OK, 0 rows affected (0.00 sec)


  82. admin@localhost : xiaoboluo 11:12:32> select * from test3;
  83. +----+------------------------------------------+--------+-------+
  84. | id | test | test2 | test3 |
  85. +----+------------------------------------------+--------+-------+
  86. | 2 | a string | 100.20 | null |
  87. | 4 | a string containing a , comma | 102.20 | NULL |
  88. | 6 | a string containing a " quote | 102.20 | NULL |
  89. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  90. | 10 | \t | 102.20 | NULL |
  91. | 14 | \t | 102.20 | NULL |
  92. +----+------------------------------------------+--------+-------+
  93. 6 rows in set (0.00 sec)


  96. admin@localhost : xiaoboluo 11:12:44> system cat /data/backup/test3.txt;
  97. 2 a string 100.20 null
  98. 4 a string containing a , comma 102.20 \N
  99. 6 a string containing a " quote 102.20 \N
  100. 8 a string containing a ", quote and comma 102.20 \N
  101. 10 \\t 102.20 \N
  102. 14 \\t 102.20 \N
  103. admin@localhost : xiaoboluo 11:12:59> truncate test3;
  104. Query OK, 0 rows affected (0.01 sec)


  107. admin@localhost : xiaoboluo 11:13:03> select * from test4;
  108. +----+------------------------------------------+--------+-------+---------------------+
  109. | id | test | test2 | test3 | test4 |
  110. +----+------------------------------------------+--------+-------+---------------------+
  111. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |
  112. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  113. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |
  114. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  115. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  116. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  117. +----+------------------------------------------+--------+-------+---------------------+
  118. 6 rows in set (0.00 sec)


  121. admin@localhost : xiaoboluo 11:13:15> system cat /data/backup/test4.txt;
  122. 2 a string 100.20 null 2017-05-03 18:41:02
  123. 4 a string containing a , comma 102.20 \N 2017-05-03 18:41:02
  124. 6 a string containing a " quote 102.20 \N 2017-05-03 18:41:02
  125. 8 a string containing a ", quote and comma 102.20 \N 2017-05-03 18:41:02
  126. 10 \\t 102.20 \N 2017-05-03 18:41:02
  127. 14 \\t 102.20 \N 2017-05-03 18:41:02
  128. admin@localhost : xiaoboluo 11:13:24> truncate test4;
  129. Query OK, 0 rows affected (0.01 sec)


  132. admin@localhost : xiaoboluo 11:13:28> flush logs;
  133. Query OK, 0 rows affected (0.01 sec)

2.2.2.1. 單表匯入

  • 使用mysqlimport命令匯入單張表

  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 ~]# mysqlimport -uadmin -pletsg0 -h10.10.30.241 xiaoboluo /data/backup/test.txt
  2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.
  3. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0


  6. # 檢視資料庫中的資料
  7. admin@localhost : xiaoboluo 11:13:42> select * from test;
  8. +----+------+-------+
  9. | id | test | test2 |
  10. +----+------+-------+
  11. | 2 | 1 | 2 |
  12. | 4 | 2 | NULL |
  13. | 6 | null | NULL |
  14. | 8 | 4 | NULL |
  15. | 10 | | NULL |
  16. | 12 | \\t | NULL |
  17. | 14 | t | NULL |
  18. | 16 | \t | NULL |
  19. | 18 | t | NULL |
  20. | 20 | NULL | NULL |
  21. | 22 | "t | NULL |
  22. +----+------+-------+
  23. 11 rows in set (0.00 sec)
  • 解析binlog檢視裡邊如何記錄的

  1. $ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000006
  2. .....
  3. BEGIN
  4. /*!*/;
  5. # at 344
  6. #170503 23:15:29 server id 3306241 end_log_pos 443 CRC32 0x4c1c8e8a Rows_query
  7. # LOAD DATA INFILE '/data/backup/test.txt' INTO TABLE `test` IGNORE 0 LINES #mysqlimport內部呼叫的load data語句在這裡
  8. # at 443
  9. #170503 23:15:29 server id 3306241 end_log_pos 501 CRC32 0x1ddc6d53 Table_map: `xiaoboluo`.`test` mapped to number 304
  10. # at 501
  11. #170503 23:15:29 server id 3306241 end_log_pos 631 CRC32 0xa8c4beab Write_rows: table id 304 flags: STMT_END_F
  12. ### INSERT INTO `xiaoboluo`.`test` #由於binlog_format=row，所以寫到binlog中時內部把load data語句轉換為了row格式
  13. ### SET
  14. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
  15. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  16. ### @3='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  17. ### INSERT INTO `xiaoboluo`.`test`
  18. ### SET
  19. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
  20. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  21. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  22. ### INSERT INTO `xiaoboluo`.`test`
  23. ### SET
  24. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
  25. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  26. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  27. ### INSERT INTO `xiaoboluo`.`test`
  28. ### SET
  29. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
  30. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  31. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  32. ### INSERT INTO `xiaoboluo`.`test`
  33. ### SET
  34. ### @1=10 /* INT meta=0 nullable=0 is_null=0 */
  35. ### @2='\x09' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  36. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  37. ### INSERT INTO `xiaoboluo`.`test`
  38. ### SET
  39. ### @1=12 /* INT meta=0 nullable=0 is_null=0 */
  40. ### @2='\x5c\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  41. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  42. ### INSERT INTO `xiaoboluo`.`test`
  43. ### SET
  44. ### @1=14 /* INT meta=0 nullable=0 is_null=0 */
  45. ### @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  46. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  47. ### INSERT INTO `xiaoboluo`.`test`
  48. ### SET
  49. ### @1=16 /* INT meta=0 nullable=0 is_null=0 */
  50. ### @2='\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  51. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  52. ### INSERT INTO `xiaoboluo`.`test`
  53. ### SET
  54. ### @1=18 /* INT meta=0 nullable=0 is_null=0 */
  55. ### @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  56. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  57. ### INSERT INTO `xiaoboluo`.`test`
  58. ### SET
  59. ### @1=20 /* INT meta=0 nullable=0 is_null=0 */
  60. ### @2=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  61. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  62. ### INSERT INTO `xiaoboluo`.`test`
  63. ### SET
  64. ### @1=22 /* INT meta=0 nullable=0 is_null=0 */
  65. ### @2='"t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
  66. ### @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */
  67. # at 631
  68. #170503 23:15:29 server id 3306241 end_log_pos 662 CRC32 0x0cd1a6ae Xid = 756
  69. COMMIT/*!*/;
  70. ......

2.2.2.2.多表匯入

  • 清理掉test表，並重新整理一下binlog

  1. admin@localhost : xiaoboluo 11:32:19> truncate test;
  2. Query OK, 0 rows affected (0.01 sec)


  5. admin@localhost : xiaoboluo 11:35:09> flush logs;
  6. Query OK, 0 rows affected (0.01 sec)
  • 使用mysqlimport匯入多表

  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h10.10.30.241 --replace xiaoboluo /data/backup/*.txt
  2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.
  3. xiaoboluo.test2: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
  4. xiaoboluo.test3: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
  5. xiaoboluo.test4: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
  6. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0


  9. real 0m0.014s
  10. user 0m0.002s
  11. sys 0m0.002s


  14. # 多表匯入時可以使用引數--use-threads指定多個執行緒，明顯比單執行緒匯入速度要快
  15. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h10.10.30.241 --replace --use-threads=8 xiaoboluo /data/backup/*.txt
  16. mysqlimport: [Warning] Using a password on the command line interface can be insecure.
  17. xiaoboluo.test3: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
  18. xiaoboluo.test2: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
  19. xiaoboluo.test: Records: 11 Deleted: 0 Skipped: 0 Warnings: 0
  20. xiaoboluo.test4: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


  23. real 0m0.007s
  24. user 0m0.006s
  25. sys 0m0.002s
  • 校驗資料

  1. admin@localhost : xiaoboluo 11:35:15> select * from test;
  2. +----+------+-------+
  3. | id | test | test2 |
  4. +----+------+-------+
  5. | 2 | 1 | 2 |
  6. | 4 | 2 | NULL |
  7. | 6 | null | NULL |
  8. | 8 | 4 | NULL |
  9. | 10 | | NULL |
  10. | 12 | \\t | NULL |
  11. | 14 | t | NULL |
  12. | 16 | \t | NULL |
  13. | 18 | t | NULL |
  14. | 20 | NULL | NULL |
  15. | 22 | "t | NULL |
  16. +----+------+-------+
  17. 11 rows in set (0.00 sec)


  20. admin@localhost : xiaoboluo 11:40:31> select * from test2;
  21. +----+------+-------+---------------------+
  22. | id | test | test2 | dt |
  23. +----+------+-------+---------------------+
  24. | 2 | 1 | 2 | 2017-05-02 18:47:03 |
  25. | 4 | 2 | NULL | 2017-05-02 18:47:03 |
  26. | 6 | null | NULL | 2017-05-02 18:47:03 |
  27. | 8 | 4 | NULL | 2017-05-02 18:47:03 |
  28. | 10 | | NULL | 2017-05-02 18:47:03 |
  29. | 12 | \\t | NULL | 2017-05-02 18:47:03 |
  30. | 14 | t | NULL | 2017-05-02 18:47:03 |
  31. | 16 | \t | NULL | 2017-05-02 18:47:03 |
  32. | 18 | t | NULL | 2017-05-02 18:47:03 |
  33. | 20 | NULL | NULL | 2017-05-02 18:47:03 |
  34. +----+------+-------+---------------------+
  35. 10 rows in set (0.00 sec)


  38. admin@localhost : xiaoboluo 11:40:33> select * from test3;
  39. +----+------------------------------------------+--------+-------+
  40. | id | test | test2 | test3 |
  41. +----+------------------------------------------+--------+-------+
  42. | 2 | a string | 100.20 | null |
  43. | 4 | a string containing a , comma | 102.20 | NULL |
  44. | 6 | a string containing a " quote | 102.20 | NULL |
  45. | 8 | a string containing a ", quote and comma | 102.20 | NULL |
  46. | 10 | \t | 102.20 | NULL |
  47. | 14 | \t | 102.20 | NULL |
  48. +----+------------------------------------------+--------+-------+
  49. 6 rows in set (0.00 sec)


  52. admin@localhost : xiaoboluo 11:40:34> select * from test4;
  53. +----+------------------------------------------+--------+-------+---------------------+
  54. | id | test | test2 | test3 | test4 |
  55. +----+------------------------------------------+--------+-------+---------------------+
  56. | 2 | a string | 100.20 | null | 2017-05-03 18:41:02 |
  57. | 4 | a string containing a , comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  58. | 6 | a string containing a " quote | 102.20 | NULL | 2017-05-03 18:41:02 |
  59. | 8 | a string containing a ", quote and comma | 102.20 | NULL | 2017-05-03 18:41:02 |
  60. | 10 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  61. | 14 | \t | 102.20 | NULL | 2017-05-03 18:41:02 |
  62. +----+------------------------------------------+--------+-------+---------------------+
  63. 6 rows in set (0.00 sec)
  • 解析binlog檢視（由於內容較多，這裡就不貼出來了，binlog解析的多表匯入操作在binlog中記錄的就是一個表一個load data語句）

3、總結

  • 關於使用local子句與不使用local子句的時候的差異

    • 如果load data語句使用了local子句，則客戶端使用TCP遠端連線mysql server時，沒有file許可權仍然能夠匯入文字檔案，這個時候是非常危險的，因為local子句的內部原理是從客戶端的主機讀取文字檔案並傳送到server端的/tmp目錄並儲存為一個臨時檔案，再執行load data語句的。另外，要使用local子句，還需要看server端啟動是否關閉了local_infile選項（如果不指定該選項，則服務端預設為ON），mysql client連線時是否關閉了local_infile選項（如果不指定該選項，則客戶端預設為ON），local_infile在server或client端任意一端關閉都不能使用local子句，會報錯誤：ERROR 1148 (42000): The used command is not allowed with this MySQL version

    • 如果load data語句不使用local子句，則這個時候使用者必須要有file許可權才能夠執行匯入文字檔案（並且只能夠匯入server端的本地文字檔案），如果沒有file許可權，可能報沒有file許可權的錯誤，也可能報錯：ERROR 1045 (28000): Access denied for user 'test'@'%' (using password: YES)

    • 如果不想這麼麻煩（因為要限制客戶端使用local子句在沒有file許可權的時候使用load data語句，需要在server端使用local_infile=OFF來關閉，不使用local子句時，如果使用者沒有file許可權，那很顯然不能夠使用load data語句，但是如果還想限制由具有file許可權的使用者怎麼辦？），可以使用引數secure_file_priv=null，設定為null時，全面禁止使用load data語句（不管使用local子句還是不使用都不允許執行load data語句）
  • 強調一點：在mysql的主備複製架構中，load data語句被認為是不安全的，要使得load data語句安全地進行復制，在binlog_format=mixed格式下會轉為row格式記錄，在binlog_format=statement時執行load data語句不會發出警告，而是內部通過一些列的流程來處理。具體是如何處理的呢，請看下回分解《load data語句如何保證主備複製資料一致性》



About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文部落格園地址： http://www.cnblogs.com/lhrbest

● 本文CSDN地址： https://blog.csdn.net/lihuarongaini

● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群號： 230161599 、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由

● 於 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

● 最新修改時間：2019-09-01 06:00 ~ 2019-09-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。

........................................................................................................................

歡迎與我聯絡

 

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2656011/，如需轉載，請註明出處，否則將追究法律責任。

相關文章