溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL的文本導(dǎo)入之load data local

發(fā)布時(shí)間：2020-07-07 22:29:52 來(lái)源：網(wǎng)絡(luò) 閱讀：736 作者：艾弗森哇 欄目：MySQL數(shù)據(jù)庫(kù)

數(shù)據(jù)文件格式：

1
2
3
4
5
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，其它是空格

導(dǎo)入命令：?

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

過(guò)程：?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)


幫助：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
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


官網(wǎng)：?

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


基本語(yǔ)法：

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?語(yǔ)句從一個(gè)文本文件中以很高的速度讀入一個(gè)表中。?使用這個(gè)命令之前，mysqld進(jìn)程（服務(wù)）必須已經(jīng)在運(yùn)行。?為了安全原因，當(dāng)讀取位于服務(wù)器上的 文本文件時(shí)，文件必須處于數(shù)據(jù)庫(kù)目錄或可被所有人讀取。另外，為了對(duì)服務(wù)器上文件使用?load data infile?，在服務(wù)器主機(jī)上你必須有?file?的權(quán)限。

1 ??如果你指定關(guān)鍵詞?low_priority?，?那么MySQL將會(huì)等到?jīng)]有其他人讀這個(gè)表的時(shí)候，?
才把插入數(shù)據(jù)?？梢允褂萌缦碌拿睿?/span>??
load data ?low_priority infile "/home/mark/data sql" into table Orders;

?

2 ??如果指定?local?關(guān)鍵詞，則表明從客戶主機(jī)讀文件。如果?local?沒(méi)指定，文件必須位于服務(wù)器上。

?

3 ?replace?和?ignore?關(guān)鍵詞控制對(duì)現(xiàn)有的唯一鍵記錄的重復(fù)的處理。如果你指定?replace?，?
新行將代替有相同的 唯一鍵值的現(xiàn)有行。如果你指定?ignore?，跳過(guò)有唯一鍵的現(xiàn)有行的?
重復(fù)行的輸入。如果你不指定任何一個(gè)選項(xiàng)，當(dāng)找到重復(fù)鍵時(shí)，出現(xiàn)一個(gè)錯(cuò)誤，?
并且文本 文件的余下部分被忽略。例如：

load data ?low_priority infile "/home/mark/data sql" replace into table Orders;

?

4??分隔符

（1） fields關(guān)鍵字指定了文件記段的分割格式，如果用到這個(gè)關(guān)鍵字，MySQL剖析器希望?
看到至少有下面的一個(gè)選項(xiàng)：??
terminated by?分隔符：意思是以什么字符作為分隔符?
enclosed by?字段括起字符?
escaped by?轉(zhuǎn)義字符

terminated by?描述字段的分隔符，默認(rèn) 情況下是tab字符（\t）??
enclosed by描述的是字段的括起字符。?
escaped by?描述的轉(zhuǎn)義字符。默認(rèn)的是反斜杠 （backslash：\ ） ?

例如：load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';

（2）lines 關(guān)鍵字指定了每條記錄的分隔符默認(rèn)為?'\n'?即為換行符

如果兩個(gè)字段都指定了 那?fields?必須在?lines?之前。?如果不指定?fields?關(guān)鍵字缺省值 與如果你這樣?
寫的相同：??fields terminated by'\t' enclosed by ’ '' ‘ escaped by'\\'

如果你不指定一個(gè)?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??可以按指定的列把文件導(dǎo)入到數(shù)據(jù)庫(kù)中。 當(dāng)我們要把數(shù)據(jù)的一部分內(nèi)容?
導(dǎo)入的時(shí)候，，需要加入一些欄目（列/字段/field）到MySQL數(shù)據(jù)庫(kù)中，以適應(yīng)一些額外的?
需要。比方說(shuō)，我們要從 Access數(shù)據(jù)庫(kù)升級(jí)到MySQL數(shù)據(jù)庫(kù)的時(shí)候

下面的例子顯示了如何向指 定的欄目(field)中導(dǎo)入數(shù)據(jù)：??
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);

6????當(dāng)在服務(wù)器主機(jī)上尋找文件時(shí)，服 務(wù)器使用下列規(guī)則：??
（?1?）如果給出一個(gè)絕對(duì)路徑名，服務(wù)器使用該路徑名。??
（?2?）如果給出一個(gè)有一個(gè)或多個(gè)前置部件的相對(duì)路徑名，服務(wù)器相對(duì)服務(wù)器的數(shù)據(jù)目錄?
搜索文件。????
（?3?）如果給出一個(gè)沒(méi)有前置部件的一個(gè) 文件名，服務(wù)器在當(dāng)前數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄尋找文件。??
例如：??/myfile txt”?="color: red">給出的文件是從服務(wù)器的數(shù)據(jù)目錄讀取，而作為?“myfile txt”?給出的一個(gè)文 件?
是從當(dāng)前數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄下讀取。

??

注意：字段中的空值用?\N?表示

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

LOAD DATA INFILE 一直被認(rèn)為是MySQL很強(qiáng)大的一個(gè)數(shù)據(jù)導(dǎo)入工具，因?yàn)樗俣确浅５目臁?
不過(guò)有幾個(gè)問(wèn)題一定要注意?
1、編碼。?
2、靈活導(dǎo)入導(dǎo)出。


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語(yǔ)句從一個(gè)文本文件中以很高的速度讀入一個(gè)表中。如果指定LOCAL關(guān)鍵詞，從客戶主機(jī)讀文件。如果LOCAL沒(méi)指定，文件必須位于?服務(wù)器上。?(LOCAL在MySQL3.22.6或以后版本中可用。）

為了?安全原因，當(dāng)讀取位于服務(wù)器上的文本文件時(shí)，文件必須處于數(shù)據(jù)庫(kù)目錄或可被所有人讀取。另外，為了對(duì)服務(wù)器上文件使用LOAD DATA INFILE，?在服務(wù)器主機(jī)上你必須有file的權(quán)限。見6.5 由MySQL提供的權(quán)限。

如果你指定關(guān)鍵詞LOW_PRIORITY，LOAD DATA語(yǔ)句的執(zhí)行被推遲到?jīng)]有其他客戶讀取表后。

使用LOCAL將比讓服務(wù)器直接存取文件慢些，因?yàn)槲募膬?nèi)容必須從客戶主機(jī)傳送到服務(wù)器主機(jī)。在另一方面，你不需要file權(quán)限裝載本地文件。

你也可以使用mysqlimport實(shí)用程序裝載數(shù)據(jù)文件；它由發(fā)送一個(gè)LOAD DATA INFILE命令到服務(wù)器來(lái)運(yùn)作。 --local選項(xiàng)使得mysqlimport從客戶主機(jī)上讀取數(shù)據(jù)。如果客戶和服務(wù)器支持壓縮協(xié)議，你能指定--compress在較慢的?網(wǎng)絡(luò)上獲得更好的性能。

當(dāng)在服務(wù)器主機(jī)上尋找文件時(shí)，服務(wù)器使用下列規(guī)則：

如果給出一個(gè)絕對(duì)路徑名，服務(wù)器使用該路徑名。??
如果給出一個(gè)有一個(gè)或多個(gè)前置部件的相對(duì)路徑名，服務(wù)器相對(duì)服務(wù)器的數(shù)據(jù)目錄搜索文件。??
如果給出一個(gè)沒(méi)有前置部件的一個(gè)文件名，服務(wù)器在當(dāng)前數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄尋找文件。??
注意這些規(guī)則意味著一個(gè)像“./myfile.txt”給出的文件是從服務(wù)器的數(shù)據(jù)目錄讀取，而作為“myfile.txt”給出的一個(gè)文件是從當(dāng)前數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄下讀取。也要注意，對(duì)于下列哪些語(yǔ)句，對(duì)db1文件從數(shù)據(jù)庫(kù)目錄讀取，而不是db2：

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

REPLACE和IGNORE關(guān)鍵詞控制對(duì)現(xiàn)有的唯一鍵記錄的重復(fù)的處理。如果你指定REPLACE，新行將代替有相同的唯一鍵值的現(xiàn)有行。如果你指定IGNORE，跳過(guò)有唯一鍵的現(xiàn)有行的重復(fù)行的輸入。如果你不指定任何一個(gè)選項(xiàng)，當(dāng)找到重復(fù)鍵鍵時(shí)，出現(xiàn)一個(gè)錯(cuò)誤，并且文本文件的余下部分被忽略時(shí)。

如果你使用LOCAL關(guān)鍵詞從一個(gè)本地文件裝載數(shù)據(jù)，服務(wù)器沒(méi)有辦法在操作的當(dāng)中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，?
SELECT句法。為了將一個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)寫入一個(gè)文件，使用SELECT ... INTO OUTFILE，為了將文件讀回?cái)?shù)據(jù)庫(kù)，使用LOAD DATA INFILE。兩個(gè)命令的FIELDS和LINES子句的語(yǔ)法是相同的。兩個(gè)子句是可選的，但是如果指定兩個(gè)，F(xiàn)IELDS必須在LINES之前。

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

如果你不指定一個(gè)FIELDS子句，缺省值與如果你這樣寫的相同：

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

如果你不指定一個(gè)LINES子句，缺省值與如果你這樣寫的相同：

LINES TERMINATED BY '\n'??
換句話說(shuō)，缺省值導(dǎo)致讀取輸入時(shí)，LOAD DATA INFILE表現(xiàn)如下：

在換行符處尋找行邊界??
在定位符處將行分進(jìn)字段??
不要期望字段由任何引號(hào)字符封裝??
將由“\”開頭的定位符、換行符或“\”解釋是字段值的部分字面字符??
相反，缺省值導(dǎo)致在寫入輸出時(shí)，SELECT ... INTO OUTFILE表現(xiàn)如下：

在字段之間寫定位符??
不用任何引號(hào)字符封裝字段??
使用“\”轉(zhuǎn)義出現(xiàn)在字段中的定位符、換行符或“\”字符??
在行尾處寫換行符??
注意，為了寫入FIELDS ESCAPED BY '\\'，對(duì)作為一條單個(gè)的反斜線被讀取的值，你必須指定2條反斜線值。

IGNORE number LINES選項(xiàng)可被用來(lái)忽略在文件開始的一個(gè)列名字的頭：

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

當(dāng)你與LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE將一個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)寫進(jìn)一個(gè)文件并且隨后馬上將文件讀回?cái)?shù)據(jù)庫(kù)時(shí)，兩個(gè)命令的字段和處理選項(xiàng)必須匹配，否則，LOAD DATA INFILE將不能正確解釋文件的內(nèi)容。假定你使用SELECT ... INTO OUTFILE將由逗號(hào)分隔的字段寫入一個(gè)文件：

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

為了將由逗號(hào)分隔的文件讀回來(lái)，正確的語(yǔ)句將是：

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

相反，如果你試圖用下面顯示的語(yǔ)句讀取文件，它不會(huì)工作，因?yàn)樗頛OAD DATA INFILE在字段之間尋找定位符：

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

可能的結(jié)果是每個(gè)輸入行將被解釋為單個(gè)的字段。

LOAD DATA INFILE能被用來(lái)讀取從外部來(lái)源獲得的文件。例如，以dBASE格式的文件將有由逗號(hào)分隔并用雙引號(hào)包圍的字段。如果文件中的行由換行符終止，下面顯示的命令說(shuō)明你將用來(lái)裝載文件的字段和行處理選項(xiàng)：

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

任何字段或行處理選項(xiàng)可以指定一個(gè)空字符串('')。如果不是空，F(xiàn)IELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必須是一個(gè)單個(gè)字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超過(guò)一個(gè)字符。例如，寫入由回車換行符對(duì)（CR+LF）終止的行，或讀取包含這樣行的一個(gè)文件，指定一個(gè)LINES TERMINATED BY '\r\n'子句。

FIELDS [OPTIONALLY] ENCLOSED BY控制字段的包圍字符。對(duì)于輸出(SELECT ... INTO OUTFILE)，如果你省略O(shè)PTIONALLY，所有的字段由ENCLOSED BY字符包圍。對(duì)于這樣的輸出的一個(gè)例子(使用一個(gè)逗號(hào)作為字段分隔符)顯示在下面：

"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

注意，一個(gè)字段值中的ENCLOSED BY字符的出現(xiàn)通過(guò)用ESCAPED BY字符作為其前綴來(lái)轉(zhuǎn)義。也要注意，如果你指定一個(gè)空ESCAPED BY值，可能產(chǎn)生不能被LOAD DATA INFILE正確讀出的輸出。例如，如果轉(zhuǎn)義字符為空，上面顯示的輸出顯示如下。注意到在第四行的第二個(gè)字段包含跟隨引號(hào)的一個(gè)逗號(hào)，它(錯(cuò)誤地)好象要終止字段：

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

對(duì)于輸入，ENCLOSED BY字符如果存在，它從字段值的尾部被剝?nèi)ァ＃ú还苁欠裰付∣PTIONALLY都是這樣；OPTIONALLY對(duì)于輸入解釋不起作用)由ENCLOSED BY字符領(lǐng)先的ESCAPED BY字符出現(xiàn)被解釋為當(dāng)前字段值的一部分。另外，出現(xiàn)在字段中重復(fù)的ENCLOSED BY被解釋為單個(gè)ENCLOSED BY字符，如果字段本身以該字符開始。例如，如果ENCLOSED BY '"'被指定，引號(hào)如下處理：

"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值的第一個(gè)字符??
ASCII 0（實(shí)際上將后續(xù)轉(zhuǎn)義字符寫成 ASCII'0'，而不是一個(gè)零值字節(jié)）??
如果FIELDS ESCAPED BY字符是空的，沒(méi)有字符被轉(zhuǎn)義。指定一個(gè)空轉(zhuǎn)義字符可能不是一個(gè)好主意，特別是如果在你數(shù)據(jù)中的字段值包含剛才給出的表中的任何字符。

對(duì)于輸入，如果FIELDS ESCAPED BY字符不是空的，該字符的出現(xiàn)被剝?nèi)ゲ⑶液罄m(xù)字符在字面上作為字段值的一個(gè)部分。例外是一個(gè)轉(zhuǎn)義的“0”或“N”（即，\0或\N，如果轉(zhuǎn)義字符是“\”)。這些序列被解釋為ASCII 0（一個(gè)零值字節(jié)）和NULL。見下面關(guān)于NULL處理的規(guī)則。

對(duì)于更多關(guān)于“\”- 轉(zhuǎn)義句法的信息，在某些情況下，字段和行處理選項(xiàng)相互作用：

如果LINES TERMINATED BY是一個(gè)空字符串并且FIELDS TERMINATED BY是非空的，行也用FIELDS TERMINATED BY終止。?
如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都是空的('')，一個(gè)固定行(非限定的)格式被使用。用固定行格式，在字段之間不使用分隔符。相反，列值只用列的“顯示”寬度被寫入和讀出。例如，如果列被聲明為INT(7)，列的值使用7個(gè)字符的字段被寫入。對(duì)于輸入，列值通過(guò)讀取7個(gè)字符獲得。固定行格式也影響NULL值的處理；見下面。注意如果你正在使用一個(gè)多字節(jié)字符集，固定長(zhǎng)度格式將不工作。?
NULL值的處理有多種，取決于你使用的FIELDS和LINES選項(xiàng)：

對(duì)于缺省FIELDS和LINES值，對(duì)輸出，NULL被寫成\N，對(duì)輸入，\N被作為NULL讀入(假定ESCAPED BY字符是“\”)。?
如果FIELDS ENCLOSED BY不是空的，包含以文字詞的NULL作為它的值的字段作為一個(gè)NULL值被讀入(這不同于包圍在FIELDS ENCLOSED BY字符中的字NULL，它作為字符串'NULL'讀入)。?
如果FIELDS ESCAPED BY是空的，NULL作為字NULL被寫入。??
用固定行格式(它發(fā)生在FIELDS TERMINATED BY和FIELDS ENCLOSED BY都是空的時(shí)候)，NULL作為一個(gè)空字符串被寫入。注意，在寫入文件時(shí)，這導(dǎo)致NULL和空字符串在表中不能區(qū)分，因?yàn)樗麄兌甲鳛榭兆址粚懭?。如果在讀回文件時(shí)需要能區(qū)分這兩者，你應(yīng)該不使用固定行格式。?
一些不被LOAD DATA INFILE支持的情況：

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

如果FIELDS ESCAPED BY是空的，一個(gè)包含跟隨FIELDS TERMINATED BY值之后的FIELDS ENCLOSED BY或LINES TERMINATED BY的字段值將使得LOAD DATA INFILE過(guò)早地終止讀取一個(gè)字段或行。這是因?yàn)長(zhǎng)OAD DATA INFILE不能正確地決定字段或行值在哪兒結(jié)束。?
下列例子裝載所有persondata表的行：

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

沒(méi)有指定字段表，所以LOAD DATA INFILE期望輸入行對(duì)每個(gè)表列包含一個(gè)字段。使用缺省FIELDS和LINES值。

如果你希望僅僅裝載一張表的某些列，指定一個(gè)字段表：

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

如果在輸入文件中的字段順序不同于表中列的順序，你也必須指定一個(gè)字段表。否則，MySQL不能知道如何匹配輸入字段和表中的列。

如果一個(gè)行有很少的字段，對(duì)于不存在輸入字段的列被設(shè)置為缺省值。

如果字段值缺省，空字段值有不同的解釋：

對(duì)于字符串類型，列被設(shè)置為空字符串。??
對(duì)于數(shù)字類型，列被設(shè)置為0。??
對(duì)于日期和時(shí)間類型，列被設(shè)置為該類型的適當(dāng)“零”值。??
如果列有一個(gè)NULL，或(只對(duì)第一個(gè)TIMESTAMP列)在指定一個(gè)字段表時(shí)，如果TIMESTAMP列從字段表省掉，TIMESTAMP列只被設(shè)置為當(dāng)前的日期和時(shí)間。

如果輸入行有太多的字段，多余的字段被忽略并且警告數(shù)字加1。

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

如果你正在使用C API，當(dāng)LOAD DATA INFILE查詢完成時(shí)，你可通過(guò)調(diào)用API函數(shù)mysql_info()得到有關(guān)查詢的信息。信息字符串的格式顯示在下面：

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0?
當(dāng)值通過(guò)INSERT語(yǔ)句插入時(shí)，在某些情況下出現(xiàn)警告，除了在輸入行中有太少或太多的字段時(shí)，LOAD DATA INFILE也產(chǎn)生警告。警告沒(méi)被?存儲(chǔ)在任何地方；警告數(shù)字僅能用于表明一切是否順利。如果你得到警告并且想要確切知道你為什么得到他們，一個(gè)方法是使用SELECT ... INTO OUTFILE到另外一個(gè)文件并且把它與你的原版輸入文件比較




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

load data語(yǔ)句詳解

  • 背景

    • 數(shù)據(jù)庫(kù)版本：MySQL 5.7.18

    • 服務(wù)器信息：本地到處導(dǎo)入在10.10.30.241上演示，local遠(yuǎn)程導(dǎo)入在10.10.30.250上演示

    • 數(shù)據(jù)庫(kù)參數(shù)配置：??
      * 雙一，secure_file_priv=''，log-bin，binlog_format=row，隔離級(jí)別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

  • 制造測(cè)試數(shù)據(jù)


  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、語(yǔ)法解析

  • 查看語(yǔ)法幫助信息


  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語(yǔ)句加載的數(shù)據(jù)源可以是mysqldump導(dǎo)出的純文本數(shù)據(jù)文件，也可以是使用SELECT … INTO OUTFILE '/path/xx.txt';語(yǔ)句生成的單表純文本數(shù)據(jù)文件，或者其他的方式生成的txt（只要生成的純文本數(shù)據(jù)列按指定分隔符分割的純文本數(shù)據(jù)文件即可）

  • 從上面的幫助信息可以看到整個(gè)load data語(yǔ)句的語(yǔ)法結(jié)構(gòu)，其中l(wèi)oad data infile 'file.txt' into table tb_name; 是最基本的使用語(yǔ)句結(jié)構(gòu)，其余的都為可選子句

1.1. 必選子句或關(guān)鍵字

  • load data語(yǔ)句簡(jiǎn)單示例

  • 如果文本文件中的數(shù)據(jù)字段與表結(jié)構(gòu)中的字段定義順序相同，則直接使用如下語(yǔ)句載入即可


  1. # 執(zhí)行select ...into outfile語(yǔ)句導(dǎo)出文本文件

  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查看一下數(shù)據(jù)中的特殊符號(hào)，其中\(zhòng)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. # 現(xiàn)在，truncate掉表test3，執(zhí)行l(wèi)oad data載入數(shù)據(jù)

  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; #這里可以看到，數(shù)據(jù)到處導(dǎo)入正常

  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>

  • 如果文本文件中的數(shù)據(jù)字段與表結(jié)構(gòu)中的字段定義順序不同，則使用如下語(yǔ)句指定載入表中的字段順序


  1. # 導(dǎo)出文本，導(dǎo)出文本時(shí)不使用select *，而是使用具體的字段，把順序稍微調(diào)整一下

  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; # 留個(gè)表中各個(gè)字段的值大概是什么內(nèi)容

  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. # 現(xiàn)在，truncate掉表test3，執(zhí)行l(wèi)oad data載入數(shù)據(jù)

  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)子句指定了與文本文件中數(shù)據(jù)的字段一致的順序，導(dǎo)入表表中之后數(shù)據(jù)的順序是正確的

  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. 可選子句或關(guān)鍵字

  • 以下演示部分只針對(duì)部分子句或關(guān)鍵字做演示，并不是全部，悉知

1.2.1. LOW_PRIORITY關(guān)鍵字

  • 如果load data語(yǔ)句使用了LOW_PRIORITY關(guān)鍵字，則在碰到其他會(huì)話操作相同表時(shí)，則會(huì)延遲執(zhí)行LOAD DATA語(yǔ)句，直到其他會(huì)話操作表結(jié)束為止。這僅影響使用表級(jí)鎖定的存儲(chǔ)引擎（如MyISAM，MEMORY和MERGE），對(duì)于innodb存儲(chǔ)引擎不起作用，因?yàn)閕nnodb引擎是行級(jí)鎖，對(duì)于load data新插入的不同的數(shù)據(jù)行之間的操作不會(huì)發(fā)生沖突。本小節(jié)不做演示，更多信息參考鏈接：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.2. LOCAL關(guān)鍵字

1.2.2.1. 使用與不使用local關(guān)鍵字的流程

  • 如果要載入的文本文件不在mysql server數(shù)據(jù)庫(kù)本身的本地磁盤，客戶端也不是從mysql server本機(jī)登錄的，則需要使用local關(guān)鍵字，指定mysql server從client host本地加載該文件，需要mysql server端使用local_infile=true(或者設(shè)置為1，不設(shè)置時(shí)默認(rèn)為1)啟動(dòng)，以及客戶端連接mysql server時(shí)也使用local_infile=true(或者設(shè)置為1，不指定時(shí)默認(rèn)為1)連接才能使用，server和client必須都開啟這個(gè)參數(shù)才能使用local關(guān)鍵字，任意一個(gè)關(guān)閉都不能使用


  1. # 登錄到數(shù)據(jù)庫(kù)，重新導(dǎo)出表數(shù)據(jù)到文本，并發(fā)送到10.10.30.250服務(wù)器

  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服務(wù)器，遠(yuǎn)程連接10.10.30.241數(shù)據(jù)庫(kù)

  13. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d ~]# mysql -uadmin -pletsg0 -h20.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; #關(guān)閉server端的local_infile參數(shù)

  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; #執(zhí)行導(dǎo)入數(shù)據(jù)時(shí)報(bào)錯(cuò)了

  62. ERROR 1148 (42000): The used command is not allowed with this MySQL version

  63. mysql> set global local_infile=ON; #重新打開server端的local_infile參數(shù)

  64. Query OK, 0 rows affected (0.00 sec)



  67. mysql> load data local infile '/tmp/test3.txt' into table test3; #導(dǎo)入成功

  68. Query OK, 4 rows affected (0.00 sec)

  69. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0



  72. mysql> select * from test3; #查看數(shù)據(jù)，可以看到數(shù)據(jù)已成功導(dǎo)入表

  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. # 對(duì)于客戶端連接server時(shí)使用local_infile=0參數(shù)，在執(zhí)行導(dǎo)入數(shù)據(jù)時(shí)也會(huì)報(bào)相同的錯(cuò)誤，這里不做演示，示例命令：mysql -uadmin -pletsg0 -h20.10.30.241 --local-infile=0

  • 使用local關(guān)鍵字與不使用local關(guān)鍵字時(shí)load data語(yǔ)句加載文本文件的流程

    • 如果指定了LOCAL，則該客戶端程序在客戶端主機(jī)上讀取load data語(yǔ)句需要的文件并將其發(fā)送到服務(wù)器。該文件可以使用完整路徑名稱來(lái)指定其位置。也可以使用相對(duì)路徑，使用相對(duì)路徑時(shí)，路徑前綴為使用客戶端程序時(shí)的工作目錄，當(dāng)使用帶有LOAD DATA的LOCAL關(guān)鍵字時(shí)，會(huì)在mysql server端的臨時(shí)目錄中創(chuàng)建該文件的副本(注意：這個(gè)場(chǎng)景下該文件的路徑不是由tmpdir或slave_load_tmpdir的值指定的，而是操作系統(tǒng)的臨時(shí)目錄/tmp，并且在MySQL server中這個(gè)存放副本的路徑是不可配置的（通常，系統(tǒng)臨時(shí)目錄是Linux系統(tǒng)上的/tmp，Windows上的C:\WINDOWS\TEMP），要注意，如果在該臨時(shí)目錄下創(chuàng)建load文件的副本時(shí)發(fā)現(xiàn)磁盤空間不夠，會(huì)導(dǎo)致LOAD DATA LOCAL語(yǔ)句執(zhí)行失敗。在客戶端主機(jī)讀取文本文件定位規(guī)則是如果指定的是絕對(duì)路徑，則使用絕對(duì)路徑，如果是相對(duì)路徑，則在登錄mysql server時(shí)的工作路徑下查找，找不到就報(bào)錯(cuò)：ERROR 2 (HY000): File 'test3.txt' not found (Errcode: 2 - No such file or directory)

    • 如果未指定LOCAL，則該文件必須位于mysql server端主機(jī)上，并由服務(wù)器直接讀取。服務(wù)器使用以下規(guī)則來(lái)定位文件：??
      * 如果文件名是絕對(duì)路徑名， mysql server將按照給定的路徑讀取文件??
      * 如果文件名是一個(gè)相對(duì)路徑名，則mysql server將在server端的datadir下搜索該文件，如果load data語(yǔ)句指定了庫(kù)名，則在datadir的指定庫(kù)名下搜索文本文件，如果沒(méi)有指定庫(kù)名，則在默認(rèn)數(shù)據(jù)庫(kù)下搜索文本文件（load data語(yǔ)句沒(méi)有指定庫(kù)名時(shí)要正確執(zhí)行語(yǔ)句必須先使用use db語(yǔ)句切庫(kù)，so，這個(gè)就是默認(rèn)庫(kù)）??
      * 如果datadir下還找不到就報(bào)錯(cuò)：ERROR 13 (HY000): Can't get stat of '/datadir/xiaoboluo/test3.txt' (Errcode: 2 - No such file or directory)

  • PS：

    • 非LOCAL方式只會(huì)加載位于mysql server上的文本文件。出于安全考慮，此類操作要求您具有FILE特權(quán)。而且，非本地加載操作也受到secure_file_priv系統(tǒng)變量的設(shè)置。如果變量值是非空目錄名稱，則要加載的文件必須位于該變量指定的目錄中。如果變量值為空（這是不安全的，這個(gè)時(shí)候server本地導(dǎo)入由file權(quán)限控制，client遠(yuǎn)程主機(jī)的文件導(dǎo)入由server和client端的local_infile選項(xiàng)共同控制），則該文件只能由服務(wù)器讀取。

    • 使用LOCAL比讓服務(wù)器直接訪問(wèn)文件要慢一些，因?yàn)槲募膬?nèi)容必須通過(guò)客戶端的連接發(fā)送到服務(wù)器。另一方面，您不需要FILE權(quán)限來(lái)加載本地文件?？梢允褂胠ocal子句

    • 使用local關(guān)鍵字時(shí)碰到唯一鍵值沖突時(shí)的處理方式與IGNORE關(guān)鍵字相同，忽略沖突的行

1.2.2.2. 使用local關(guān)鍵字的錯(cuò)誤處理

  • 使用LOCAL會(huì)影響錯(cuò)誤處理行為：

  • 使用LOAD DATA INFILE，數(shù)據(jù)解析碰到重復(fù)鍵時(shí)默認(rèn)情況下會(huì)終止操作


  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; #留意導(dǎo)出的文本中的第一列數(shù)值與表中的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; #執(zhí)行l(wèi)oad data時(shí)，不使用local關(guān)鍵字直接報(bào)主鍵沖突

  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，數(shù)據(jù)解析碰到重復(fù)鍵時(shí)將發(fā)出警告，并且操作繼續(xù)進(jìn)行，因?yàn)閟erver端無(wú)法在客戶端操作期間停止傳輸文件。此時(shí)處理重復(fù)鍵與指定IGNORE關(guān)鍵字相同（即忽略主鍵沖突的數(shù)據(jù)行）


  1. admin@localhost : xiaoboluo 01:46:52> load data local infile '/tmp/test3.txt' into table test3; #加上local關(guān)鍵字再次執(zhí)行，可以發(fā)現(xiàn)不報(bào)錯(cuò)了，但是Skipped: 4 Warnings: 4表示跳過(guò)了4行數(shù)據(jù)導(dǎo)入，\

  2. 注意：如果不使用local關(guān)鍵字，在默認(rèn)的sql_mode下無(wú)法導(dǎo)入，否則就需要去掉嚴(yán)格的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關(guān)鍵字

  • REPLACE和IGNORE關(guān)鍵字控制對(duì)唯一鍵值沖突行的處理：

    • 如果指定了REPLACE關(guān)鍵字，則輸入行將覆蓋現(xiàn)有行。換句話說(shuō)，與主鍵或唯一索引沖突的數(shù)據(jù)行將被執(zhí)行覆蓋寫入，如果同時(shí)使用了local關(guān)鍵字，則與沒(méi)有使用local關(guān)鍵字行為相同

    • 如果指定了IGNORE關(guān)鍵字，則與唯一鍵值沖突的數(shù)據(jù)行將被丟棄，如果同時(shí)使用了local關(guān)鍵字，則與沒(méi)有使用local關(guān)鍵字行為相同

    • 如果不指定REPLACE和IGNORE任一選項(xiàng)，則行為取決于是否指定LOCAL關(guān)鍵字。沒(méi)有LOCAL，則發(fā)現(xiàn)主鍵或唯一索引沖突時(shí)就報(bào)錯(cuò)終止load data語(yǔ)句執(zhí)行，并忽略文本文件的其余部分的載入。如果使用了LOCAL關(guān)鍵字，則local關(guān)鍵字的默認(rèn)行為與指定IGNORE時(shí)相同，這是因?yàn)閟erver端無(wú)法在操作期間停止客戶端的文件傳輸（不使用REPLACE與IGNORE關(guān)鍵字時(shí)的錯(cuò)誤處理，詳見1.2.2.2小節(jié)）

  • 下面對(duì)使用REPLACE與IGNORE關(guān)鍵字進(jìn)行演示

  • 如果文本文件中的數(shù)據(jù)字段有與表結(jié)構(gòu)中的唯一索引、主鍵索引沖突的，則使用REPLACE關(guān)鍵字，該關(guān)鍵字會(huì)對(duì)沖突的數(shù)據(jù)進(jìn)行覆蓋（內(nèi)部轉(zhuǎn)換為UPDATE，見后續(xù)章節(jié)示例）


  1. # 使用replace 覆蓋沖突數(shù)據(jù)行，不使用local關(guān)鍵字

  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; #修改其中一行數(shù)據(jù)

  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; #查看表中的數(shù)據(jù)，留意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; #執(zhí)行不帶local關(guān)鍵字但帶repalce關(guān)鍵字的語(yǔ)句

  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; #查詢表中的數(shù)據(jù)，可以發(fā)現(xiàn)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. # 解析最后一個(gè)binlog來(lái)查看一下（這里僅作參考，主庫(kù)內(nèi)部是否是binlog中記錄的形式處理覆蓋數(shù)據(jù)有待考證）

  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 #這里是執(zhí)行的原始load data 的sql語(yǔ)句，要看到此內(nèi)容，需要打開參數(shù)：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` #這里可以看到發(fā)生數(shù)據(jù)沖突的行被執(zhí)行了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關(guān)鍵字忽略沖突的行（注意，這里的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行數(shù)據(jù)都跳過(guò)了，注意：這是在sql_mode=''時(shí)導(dǎo)入的，\

  53. 如果不修改sql_mode請(qǐng)使用local關(guān)鍵字



  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; #查看一下執(zhí)行l(wèi)oad data語(yǔ)句之后的表數(shù)據(jù)，發(fā)現(xiàn)id=2的test2列值還是111111111 沒(méi)有變化

  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選項(xiàng)顯式分區(qū)選擇，其中包含一個(gè)或多個(gè)分區(qū)，子分區(qū)或兩者名稱的逗號(hào)分隔列表。當(dāng)使用此選項(xiàng)時(shí)，如果文件中的任何行無(wú)法插入到列表中指定的任何分區(qū)或子分區(qū)中，則該語(yǔ)句將失敗，并顯示錯(cuò)誤，找到與給定分區(qū)集不匹配的行，本小節(jié)不做演示，更多信息參考鏈接：?https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.5. CHARACTER SET charset_name子句

  • 導(dǎo)入的文本數(shù)據(jù)文件名必須以文字字符串形式給出。在Windows上，在路徑名稱中指定反斜杠為正斜杠或雙倍反斜杠。 character_set_filesystem系統(tǒng)變量控制文件名的解釋

  • 服務(wù)器使用由character_set_database系統(tǒng)變量指定的字符集來(lái)解釋文件中的內(nèi)容。SET NAMES語(yǔ)句和character_set_client系統(tǒng)變量的設(shè)置不影響文件內(nèi)容的字符集解析。如果輸入文件的內(nèi)容使用的字符集與server的默認(rèn)值字符集不同，則建議先使用load data的CHARACTER SET子句指定解析文本文件內(nèi)容的字符集

  • LOAD DATA INFILE將文件中的所有字段以相同的字符集進(jìn)行解析，而不管加載字段列的數(shù)據(jù)類型定義的字符集如何。為了正確解釋文件內(nèi)容，您必須確保使用正確的字符集進(jìn)行導(dǎo)出數(shù)據(jù)和導(dǎo)入數(shù)據(jù)。例如，如果您使用mysqldump -T或通過(guò)在mysql中執(zhí)行SELECT … INTO OUTFILE語(yǔ)句導(dǎo)出數(shù)據(jù)文件時(shí)，建議使用--default-character-set選項(xiàng)指定一個(gè)對(duì)應(yīng)數(shù)據(jù)的字符集，以便使用Load data語(yǔ)句導(dǎo)入數(shù)據(jù)時(shí)不會(huì)發(fā)生字符集錯(cuò)亂（使用mysqlimport的--default-character-set指定導(dǎo)出數(shù)據(jù)時(shí)的字符集，使用mysql命令行客戶端的--default-character-set指定導(dǎo)出數(shù)據(jù)時(shí)的字符集，注：不能指定ucs2,utf16,utf16le或utf32字符集來(lái)加載數(shù)據(jù)文件）

  • 本小節(jié)不做演示，更多信息參考鏈接：?https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1.2.6. FIELDS（與COLUMNS關(guān)鍵字相同）和LINES子句

  • 以下示例中的char代表單個(gè)字符，string代表字符串（即多個(gè)字符），load data語(yǔ)句中，轉(zhuǎn)義字符和字段引用符只能使用單個(gè)字符，字段分隔符、行分隔符、行前綴字符都可以使用多個(gè)字符（字符串）

  • 對(duì)于LOAD DATA INFILE和SELECT … INTO OUTFILE語(yǔ)句中，F(xiàn)IELDS和LINES子句的語(yǔ)法完全相同。兩個(gè)子句在LOAD DATA INFILE和SELECT … INTO OUTFILE語(yǔ)句中都是可選的，但如果兩個(gè)子句都被指定，則FIELDS必須在LINES之前，否則報(bào)語(yǔ)法錯(cuò)誤

    • FIELDS關(guān)鍵字共有三個(gè)子句，TERMINATED BY 'string'指定字段分隔符，[OPTIONALLY] ENCLOSED BY 'char'指定字段引用符（其中使用了OPTIONALLY關(guān)鍵字之后，只在char、varchar和text等字符型字段上加字段引用符，數(shù)值型的不會(huì)加字段引用符，且OPTIONALLY 關(guān)鍵字只在導(dǎo)出數(shù)據(jù)時(shí)才起作用，導(dǎo)入數(shù)據(jù)時(shí)用于不用對(duì)數(shù)據(jù)沒(méi)有影響 ），ESCAPED BY 'char'指定轉(zhuǎn)義符，如果您指定了一個(gè)FIELDS子句，則它的每個(gè)子句也是可選的，但在你指定了FIELDS關(guān)鍵字之后，這個(gè)關(guān)鍵字的子句至少需要指定一個(gè)，后續(xù)章節(jié)會(huì)進(jìn)行舉例說(shuō)明

    • LINES關(guān)鍵字共有兩個(gè)子句，STARTING BY 'string'指定行前綴字符，TERMINATED BY 'string'指定行分隔符（換行符），如果你指定了LINES關(guān)鍵字，則LINES的子句都是可選的，但在你指定了LINES關(guān)鍵字之后，這個(gè)關(guān)鍵字的子句至少需要指定一個(gè)，后續(xù)章節(jié)會(huì)進(jìn)行舉例說(shuō)明

    • 如果在導(dǎo)入和導(dǎo)出時(shí)沒(méi)有指定FIELDS和LINES子句，則導(dǎo)入和導(dǎo)出時(shí)兩個(gè)子句的默認(rèn)值相同，默認(rèn)的字段分隔符為\t，行分隔符為\n（win上默認(rèn)為\r\n，記事本程序上默認(rèn)為\r），字段引用符為空，行前綴字符為空

  • 當(dāng)mysql server導(dǎo)出文本數(shù)據(jù)到文件時(shí)，F(xiàn)IELDS和LINES默認(rèn)值時(shí)SELECT … INTO OUTFILE在輸出文本數(shù)據(jù)時(shí)行為如下：

    • 在文本數(shù)據(jù)各字段之間使用制表符來(lái)作為字段分隔符

    • 不使用任何引號(hào)來(lái)包圍文本數(shù)據(jù)的各字段值，即字段引用符為空

    • 使用\轉(zhuǎn)義在字段值中出現(xiàn)的制表符\t，換行符\n或轉(zhuǎn)義符本身\等特殊字符(即輸出的文本數(shù)據(jù)中對(duì)這些特殊字符前多加一個(gè)反斜杠)

    • 在行尾寫上換行符\n，即使用\n作為行分隔符（換行符）

    • 注意：如果您在Windows系統(tǒng)上生成了文本文件，則可能必須使用LINES TERMINATED BY '\r\n'來(lái)正確讀取文件，因?yàn)閃indows程序通常使用兩個(gè)字符作為行終止符。某些程序（如寫字板）在寫入文件時(shí)可能會(huì)使用\r作為行終止符(要讀取這些文件，請(qǐng)使用LINES TERMINATED BY '\r')

    • FIELDS和LINES子句默認(rèn)值時(shí)生成的純文本數(shù)據(jù)文件可以使用python代碼來(lái)讀取文件查看文件中的制表符和換行符（linux下的cat和vim等編輯器默認(rèn)會(huì)解析\t為制表符,\n為換行符，所以使用這些命令可能無(wú)法看到這些特殊符號(hào)）


  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"

  • 當(dāng)mysql server從文本文件讀取數(shù)據(jù)時(shí)，F(xiàn)IELDS和LINES默認(rèn)值會(huì)導(dǎo)致LOAD DATA INFILE的行為如下：

    • 尋找換行邊界字符\n來(lái)進(jìn)行換行

    • 不跳過(guò)行前綴，把行前綴也當(dāng)作數(shù)據(jù)（發(fā)生在如果導(dǎo)出數(shù)據(jù)時(shí)使用了行前綴，導(dǎo)入時(shí)沒(méi)有指定正確的行前綴或者根本沒(méi)有指定行前綴選項(xiàng)時(shí)）

    • 使用制表符\t來(lái)分割一行數(shù)據(jù)中的各列

    • 要注意：在FIELDS和LINES的默認(rèn)值下，在解析文本文件時(shí)不會(huì)把字符串之間的引號(hào)當(dāng)作真正的引號(hào)，而是當(dāng)作數(shù)據(jù)

1.2.6.1. FIELDS關(guān)鍵字及其子句詳解

  • 字段分隔符，默認(rèn)是\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選項(xiàng)則只用在char、varchar和text等字符型字段上，數(shù)值類型會(huì)忽略使用引用符，如果不指定該子句，則默認(rèn)不使用引用符，使用子句fields [optionally] enclosed by 'char'指定，其中char代表指定的字段引用符


  1. # 指定字段引用符為"，不使用optionally關(guān)鍵字

  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關(guān)鍵字，可以看到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"

  • 轉(zhuǎn)義字符，默認(rèn)為\，使用子句fields escaped by 'char' 指定，其中char代表指定的轉(zhuǎn)義字符


  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 # 可以看到數(shù)據(jù)中指定的轉(zhuǎn)義符.號(hào)被轉(zhuǎn)義了，而數(shù)據(jù)\t沒(méi)有被轉(zhuǎn)義

  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 '.'; #導(dǎo)入數(shù)據(jù)時(shí)指定轉(zhuǎn)義符為.號(hào)

  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; #校驗(yàn)數(shù)據(jù)，可以看到導(dǎo)入數(shù)據(jù)正常

  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 關(guān)鍵字及其子句詳解

  • 行前綴字符串，使用子句lines starting by 'string' 指定，其中string代表指定的行前綴字符串，行前綴字符串在導(dǎo)出文本數(shù)據(jù)時(shí)使用該子句指定，在導(dǎo)入文本時(shí)在一行數(shù)據(jù)中如果發(fā)現(xiàn)了行前綴字符串，則只導(dǎo)入從前綴字符串開始之后的數(shù)據(jù)部分，前綴字符本身及其之前的數(shù)據(jù)被忽略掉，如果某行數(shù)據(jù)不包含行前綴字符串，則整行數(shù)據(jù)都會(huì)被忽略

如果您想要讀取的純文本文件中所有行都有一個(gè)您想要忽略的公用前綴，則可以使用LINES STARTING BY'prefix_string'來(lái)跳過(guò)這個(gè)前綴，以及前綴字符前面的任何內(nèi)容。如果某行數(shù)據(jù)不包含前綴字符，則跳過(guò)整行內(nèi)容，例如：


  1. # load data語(yǔ)句如下

  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 #可以看到每行數(shù)據(jù)前面多了個(gè)行前綴字符串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. # 現(xiàn)在，到shell命令行去修改一下，增加兩行

  16. admin@localhost : xiaoboluo 03:55:50> system cat /tmp/test3.txt # 最后要加載的純文本數(shù)據(jù)內(nèi)容如下

  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'; #導(dǎo)入數(shù)據(jù)，指定行前綴字符為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; #校驗(yàn)表數(shù)據(jù)，可以看到?jīng)]有xxx行前綴的行被忽略了，而包含xxx的最后一行，從xxx開始截?cái)?，xxx字符本身及其之前的內(nèi)容被忽略，\

  34. xxx之后的內(nèi)容被解析為行數(shù)據(jù)導(dǎo)入了



  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)

  • 行結(jié)束符（換行符），linux下默認(rèn)為\n，使用子句lines terminated by 'string' 指定，其中string代表指定的換行符


  1. # 指定換行符為\r\n導(dǎo)出數(shù)據(jù)

  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的一些命令本身會(huì)解析掉這些特殊字符，所以使用python來(lái)查看這個(gè)文本文件中的換行符，從下面的結(jié)果中可以看到，列表的每一個(gè)元素代表一行數(shù)據(jù)，每一個(gè)元素的\

  8. 末尾的\r\n就是這行數(shù)據(jù)的換行符



  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. # 現(xiàn)在，把數(shù)據(jù)重新導(dǎo)入表，從下面的結(jié)果中可以看到，導(dǎo)入表中的數(shù)據(jù)正確

  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注意事項(xiàng)

  • 眾所周知，MySQL中反斜杠是SQL語(yǔ)句中特殊字符的轉(zhuǎn)義字符，因此在sql語(yǔ)句中碰到特殊字符時(shí)，您必須指定一個(gè)或者兩個(gè)反斜杠來(lái)為特殊字符轉(zhuǎn)義（如在mysql中或者一些其他程序中，\n代表?yè)Q行符，\t代表制表符，\代表轉(zhuǎn)義符，那么需要使用\t來(lái)轉(zhuǎn)義制表符，\n來(lái)轉(zhuǎn)義換行符，\來(lái)轉(zhuǎn)義轉(zhuǎn)義符本身，這樣才能正確寫入數(shù)據(jù)庫(kù)或者生成導(dǎo)出的數(shù)據(jù)文本,使用FIELDS ESCAPED BY子句指定轉(zhuǎn)義符

  • 特殊字符列表如下


  1. \0 ASCII NUL (X'00') 字符

  2. \b 退格字符

  3. \n 換行符

  4. \r 回車符

  5. \t 制表符

  6. \Z ASCII 26 (Control+Z)

  7. \N NULL值，如果轉(zhuǎn)義符值為空，則會(huì)直接導(dǎo)出null字符串作為數(shù)據(jù)，這在導(dǎo)入時(shí)將把null作為數(shù)據(jù)導(dǎo)入，而不是null符號(hào)

  • 如果數(shù)據(jù)中包含了ENCLOSED BY '"'子句指定字段引用符號(hào)，則與字段引用符號(hào)相同數(shù)據(jù)字符也會(huì)被自動(dòng)添加一個(gè)反斜杠進(jìn)行轉(zhuǎn)義（如果轉(zhuǎn)義符指定為空，則可能會(huì)導(dǎo)致數(shù)據(jù)在導(dǎo)入時(shí)無(wú)法正確解析）。如果數(shù)據(jù)中包含了FIELDS TERMINATED BY 子句指定的字段分隔符，則以FIELDS ENCLOSED BY子句指定的字段引用符號(hào)為準(zhǔn)，被引起來(lái)的整個(gè)部分作為一整列的數(shù)據(jù)，列值之間的數(shù)據(jù)包含字段分隔符不會(huì)被轉(zhuǎn)義，而是作為數(shù)據(jù)處理，但數(shù)據(jù)中包含的字段引用符會(huì)被轉(zhuǎn)義(在數(shù)據(jù)中包含了字段分隔符的情況下，如果字段引用符號(hào)沒(méi)有指定或者指定為空值，則可能在導(dǎo)入數(shù)據(jù)時(shí)無(wú)法正確解析）。如果數(shù)據(jù)中包含了FIELDS ESCAPED BY子句指定的轉(zhuǎn)義符，字段引用符和行分隔符使用默認(rèn)值，則在數(shù)據(jù)中的轉(zhuǎn)義符會(huì)被轉(zhuǎn)義（只要不為空，則不管字段分隔符和轉(zhuǎn)義字符定義為什么值，都會(huì)被轉(zhuǎn)義），默認(rèn)情況下，不建議隨意更改換行符和轉(zhuǎn)義符，除非必須且你需要校驗(yàn)修改之后數(shù)據(jù)能夠正確導(dǎo)入


  1. # 字段引用符為"，數(shù)據(jù)中包含"，轉(zhuǎn)義符和換行符保持默認(rèn)，導(dǎo)入數(shù)據(jù)時(shí)不會(huì)有任何問(wèn)題

  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" # 可以看到與字段引用符相同的符號(hào)數(shù)據(jù)被轉(zhuǎn)義了



  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. # 如果字段引用符為"，字段分隔符為,且數(shù)據(jù)中包含字段引用符"和字段分隔符,，轉(zhuǎn)義符和換行符保持默認(rèn)，這在導(dǎo)入數(shù)據(jù)時(shí)不會(huì)有任何問(wèn)題

  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. # 但是，如果在字段引用符為"，數(shù)據(jù)中包含"，字段分隔符使用逗號(hào)，換行符保持默認(rèn)的情況下，轉(zhuǎn)義符使用了空串，這會(huì)導(dǎo)致在導(dǎo)入數(shù)據(jù)時(shí)，第四行無(wú)法正確解析，報(bào)錯(cuò)

  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" #關(guān)于這一行數(shù)據(jù)，需要說(shuō)明一下ENCLOSED BY子句，該子句指定的引用符號(hào)從一個(gè)FIELDS TERMINATED BY子句指定的分隔符開始，直到碰到下一個(gè)\

  105. 分隔符之間且這個(gè)分隔符前面一個(gè)字符必須是字段引用符號(hào)（如果這個(gè)分隔符前面一個(gè)字符不是字段引用符，則繼續(xù)往后匹配，如第二行數(shù)據(jù)），在這之間的內(nèi)容都會(huì)被當(dāng)作整個(gè)列字符串處理，\

  106. 所以這一行數(shù)據(jù)在導(dǎo)入時(shí)不會(huì)發(fā)生解析錯(cuò)誤



  109. 8,"a string containing a ", quote and comma","102.20" #這一行因?yàn)闊o(wú)法正確識(shí)別的字段結(jié)束位置，所以無(wú)法導(dǎo)入，報(bào)錯(cuò)終止，前面正確的行也被回滾掉（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. # 數(shù)據(jù)中包含了默認(rèn)的轉(zhuǎn)義符和指定的字段分隔符，字段引用符和行分隔符使用默認(rèn)值，則在數(shù)據(jù)中的轉(zhuǎn)義符和字段分隔符會(huì)被轉(zhuǎn)義（只要不為空，則不管字段分隔符和轉(zhuǎn)義字符定義為什么值，\

  121. 都會(huì)被轉(zhuǎn)義）




  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

  • 當(dāng)您使用SELECT … INTO OUTFILE與LOAD DATA INFILE一起將數(shù)據(jù)從數(shù)據(jù)庫(kù)寫入文件，然后再將該文件讀回?cái)?shù)據(jù)庫(kù)時(shí)，兩個(gè)語(yǔ)句的FIELDS和LINES處理選項(xiàng)必須匹配。否則，LOAD DATA INFILE將解析錯(cuò)誤的文件內(nèi)容，示例


  1. # 假設(shè)您執(zhí)行SELECT ... INTO OUTFILE語(yǔ)句時(shí)使用了逗號(hào)作為列分隔符：

  2. SELECT * INTO OUTFILE 'data.txt'

  3. FIELDS TERMINATED BY ','

  4. FROM table2;



  7. # 如果您嘗試使用\t作為列分隔符，則它將無(wú)法正常工作，因?yàn)樗鼤?huì)指示LOAD DATA INFILE在字段之間查找制表符，可能導(dǎo)致每個(gè)數(shù)據(jù)行整行解析時(shí)被當(dāng)作單個(gè)字段：


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


  11. FIELDS TERMINATED BY '\t';



  14. # 要正確讀取逗號(hào)分隔各列的文件，正確的語(yǔ)句是

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

  16. FIELDS TERMINATED BY ',';

  • 任何FIELDS和LINES處理選項(xiàng)都可以指定一個(gè)空字符串（''），但強(qiáng)烈不建議在FIELDS TERMINATED BY、FIELDS ESCAPED BY 和LINES TERMINATED BY子句中使用空串（空格不算空串）作為轉(zhuǎn)義符和換行符，可能導(dǎo)致許多意外的問(wèn)題，除非你確定使用空串不會(huì)出現(xiàn)問(wèn)題。如果不為空，注意FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY子句指定的值只能指定單個(gè)字符（即字段引用符號(hào)和轉(zhuǎn)義符只能使用單個(gè)字符）。但 FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY子句的值可以是多個(gè)字符（即字段分隔符和換行符、行前綴字符可以使用多個(gè)字符）。例如，指定一個(gè)LINES TERMINATED BY'\r\ n'子句，表示指定行換行符為\r\n，這個(gè)也是WIN下的換行符


  1. # 如果LINES TERMINATED BY換行符指定了一個(gè)空字符，并且FIELDS TERMINATED BY字段分隔符指定的是非空的一個(gè)字符(或者使用默認(rèn)值\t)，則行也會(huì)以字段分隔符作為行的結(jié)束符\

  2. （表現(xiàn)行為就是文本中最后一個(gè)字符就是字段分隔符），即整個(gè)文本看上去就是一整行數(shù)據(jù)了



  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查看文本內(nèi)容，從下面的結(jié)果中可以看到，整個(gè)表的數(shù)據(jù)由于換行符為空，所以導(dǎo)致都拼接為一行了，最后行結(jié)束符使用了字段分隔符逗號(hào)

  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. # 導(dǎo)入數(shù)據(jù)到表，這里新建一張表來(lái)進(jìn)行導(dǎo)入測(cè)試，預(yù)防清理掉了表數(shù)據(jù)之后，文本內(nèi)容又無(wú)法正確導(dǎo)入的情況發(fā)生

  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; #從查詢結(jié)果上看，數(shù)據(jù)正確導(dǎo)入表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值都為空（''），則使用固定行（非限制）格式。使用固定行格式時(shí)，字段之間使用足夠?qū)挼目崭駚?lái)分割各字段。對(duì)于數(shù)據(jù)類型\

  45. 是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，字段寬度分別為4,6,8,11和20個(gè)空格(無(wú)論數(shù)據(jù)類型聲明的顯示寬度如何)，對(duì)于varchar類型使用大約298個(gè)空格（這個(gè)空格數(shù)量是自己\

  46. 數(shù)的。。。，猜想這個(gè)空格數(shù)量可能與字符集，varchar定義長(zhǎng)度有關(guān)，因?yàn)槲以趪L試把varchar定義為50個(gè)字符的時(shí)候，空格少了156個(gè)左右）



  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 #下面展示內(nèi)容中把打斷空格使用...代替

  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. # 現(xiàn)在，清理掉test4表，并載入數(shù)據(jù)，從下面的結(jié)果中可以看到，導(dǎo)入表中之后，雖然數(shù)據(jù)是對(duì)的，但是多了非常多的空格，那么也就意味著你需要使用程序正確地處理一下這些多余的空格之后，\

  60. 再執(zhí)行導(dǎo)入



  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=''時(shí)導(dǎo)入的，如果不修改\

  68. sql_mode請(qǐng)使用local關(guān)鍵字



  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值的處理根據(jù)使用的FIELDS和LINES選項(xiàng)而有所不同


  1. # 對(duì)于默認(rèn)的FIELDS和LINES值，NULL值被轉(zhuǎn)義為\N輸出，字段值\N讀取時(shí)使用NULL替換并輸入（假設(shè)ESCAPED BY字符為\）

  2. admin@localhost : xiaoboluo 05:17:07> alter table test3 add column test3 varchar(10); #添加一個(gè)字段test3，默認(rèn)值會(huì)被填充為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列數(shù)據(jù)

  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"; #執(zhí)行導(dǎo)出

  22. Query OK, 6 rows affected (0.00 sec)



  25. admin@localhost : xiaoboluo 05:18:02> system cat /tmp/test3.txt #查看導(dǎo)出的文本文件，可以發(fā)現(xiàn)null被轉(zhuǎn)義為\N了，這是為了避免數(shù)據(jù)字符串本身包含null值時(shí)無(wú)法正確區(qū)分?jǐn)?shù)據(jù)類型的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. # 導(dǎo)入數(shù)據(jù)，從結(jié)果中可以看到\N被正確解析為了數(shù)據(jù)類型的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不為空，F(xiàn)IELDS escaped BY為空時(shí)，則將NULL值的字面字符串作為輸出字符值。這與FIELDS ENCLOSED BY引用的字符串中包含的null值不同，\

  59. 后者讀取為字符串'null'，而前者讀取到數(shù)據(jù)庫(kù)中時(shí)被當(dāng)作數(shù)據(jù)類型的null值，而不是數(shù)據(jù)的字符串null



  62. admin@localhost : xiaoboluo 05:20:57> update test3 set test3='null' where id=2; #更新id=2的test3列值為數(shù)據(jù)字符串的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 ''; #指定行引用符號(hào)為雙引號(hào)"，轉(zhuǎn)義符為空導(dǎo)出數(shù)據(jù)

  83. Query OK, 6 rows affected (0.00 sec)



  86. admin@localhost : xiaoboluo 05:24:51> system cat /tmp/test3.txt #查看導(dǎo)出的文本文件，可以看到數(shù)據(jù)字符串的null被加了雙引號(hào)，而數(shù)據(jù)類型的null沒(méi)有加雙引號(hào)

  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表做測(cè)試，避免無(wú)法導(dǎo)入的情況發(fā)生

  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 ''; #指定字段引用符為雙引號(hào)"，轉(zhuǎn)義符為空導(dǎo)入數(shù)據(jù)

  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中的數(shù)據(jù)，從結(jié)果中可以看到，數(shù)據(jù)導(dǎo)入正確

  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. # 使用固定行格式（當(dāng)FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空時(shí)使用），將NULL寫為空字符串。這將導(dǎo)致表中的NULL值和空字符串在寫入文件時(shí)無(wú)法區(qū)分，\

  124. 因?yàn)樗鼈兌家钥兆址问綄懭胛谋疚募Ｈ绻枰軌蛟谧x取文件時(shí)將其分開，則不應(yīng)使用固定行格式（即不應(yīng)該使用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 #從結(jié)果中看，是不是有點(diǎn)似曾相識(shí)呢？沒(méi)錯(cuò)，前面演示過(guò)FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空的情況，\

  133. 使用了固定格式來(lái)導(dǎo)出文本，但是這里多了數(shù)據(jù)類型的null值處理，從下面的結(jié)果中已經(jīng)看不到數(shù)據(jù)類型的null了，被轉(zhuǎn)換為了空值(下面展示時(shí)把大段空格使用...代替)



  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 ''; #執(zhí)行導(dǎo)入文本到test4表，注意：這是在sql_mode=''時(shí)導(dǎo)入的，\

  147. 如果不修改sql_mode請(qǐng)使用local關(guān)鍵字



  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表，從下面的結(jié)果中可以看到，原本test2字段的數(shù)據(jù)被導(dǎo)入到了test3字段，而test3字段的內(nèi)容被截?cái)嗔??！?/code>

  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. # 注意：如果使用多字節(jié)字符集，固定大小格式可能不起作用（我在測(cè)試時(shí)使用的字符集是utf8，沒(méi)有測(cè)試出來(lái)這里說(shuō)不起作用是啥意思）

  • load data執(zhí)行時(shí)如果表中有外鍵、輔助索引、唯一索引，那么會(huì)導(dǎo)致加載數(shù)據(jù)的時(shí)間變慢，因?yàn)樗饕残枰煌?，可以使用?duì)應(yīng)參數(shù)關(guān)閉外鍵檢查、唯一索引檢查甚至關(guān)閉索引

    • 要在加載操作期間忽略外鍵約束，可以在執(zhí)行l(wèi)oad data語(yǔ)句之前執(zhí)行SET foreign_key_checks = 0語(yǔ)句，執(zhí)行完畢之后執(zhí)行SET foreign_key_checks = 1或斷開會(huì)話重連

    • 要在加載操作期間忽略唯一索引約束，可以在執(zhí)行l(wèi)oad data語(yǔ)句之前執(zhí)行set unique_checks=0語(yǔ)句，執(zhí)行完畢之后執(zhí)行set unique_checks=1或斷開會(huì)話重連

    • 在某些極端情況下（比如表中索引過(guò)多），您可以在執(zhí)行l(wèi)oad data語(yǔ)句之前通過(guò)執(zhí)行ALTER TABLE … DISABLE KEYS語(yǔ)句關(guān)閉創(chuàng)建索引，在執(zhí)行完load data語(yǔ)句之后執(zhí)行ALTER TABLE … ENABLE KEYS來(lái)重新創(chuàng)建索引，注意該語(yǔ)句不能關(guān)閉主鍵索引

  • 如果在sql_mode設(shè)置為嚴(yán)格模式下，且不使用local和ignore關(guān)鍵字時(shí)，碰到缺少字段值會(huì)直接報(bào)錯(cuò)終止，但在sql_mode設(shè)置為嚴(yán)格模式下，使用了local和ignore關(guān)鍵字時(shí)，則行為與不使用嚴(yán)格模式類似??
    LOAD DATA INFILE將所有輸入視為字符串，因此您不能認(rèn)為load data語(yǔ)句會(huì)像INSERT語(yǔ)句那樣插入ENUM或SET列的數(shù)值。所有ENUM和SET值必須指定為字符串

  • LOAD DATA INFILE不支持的場(chǎng)景

    • 固定大小的行（FIELDS TERMINATED BY和FIELDS ENCLOSED BY都為空）不支持BLOB或TEXT列

    • 如果FIELDS TERMINATED BY和LINES STARTING BY指定相同的字符，則LOAD DATA INFILE無(wú)法正確解析

    • 如果FIELDS ESCAPED BY為空，則字段中包含了FIELDS ENCLOSED BY或LINES TERMINATED BY或FIELDS TERMINATED BY的字符時(shí)會(huì)導(dǎo)致LOAD DATA INFILE語(yǔ)句拒絕讀取字段并報(bào)錯(cuò)。這是因?yàn)長(zhǎng)OAD DATA INFILE無(wú)法正確確定字段或行在哪里結(jié)束

  • PS：在Unix上，如果需要LOAD DATA從管道讀取數(shù)據(jù)，可以使用以下方法（該示例將/目錄的列表加載到表db1.t1中，find命令掛后臺(tái)持續(xù)查找內(nèi)容并生成ls.dat文件，mysql 客戶端使用-e選項(xiàng)來(lái)執(zhí)行l(wèi)oad data這個(gè)文件到表）：


  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行數(shù)據(jù)，使用子句ignore number lines指定忽略文本的前number行，在某些情況下生成的文本（如：mysql -e "select …." > xx.txt中）帶有字段名稱，在導(dǎo)入時(shí)會(huì)把這一行字段名稱也當(dāng)作數(shù)據(jù)，所以需要忽略掉這行字段名稱


  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; #載入文本時(shí)指定ignore 1 lines子句忽略文本中的前1行數(shù)據(jù)

  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中的數(shù)據(jù)，從下面的結(jié)果中可以看到數(shù)據(jù)正確

  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可用于讀取外部數(shù)據(jù)源文件。例如，許多程序可以以逗號(hào)分隔的值（CSV）格式導(dǎo)出數(shù)據(jù)，字段用逗號(hào)分隔，并包含在雙引號(hào)內(nèi)，并帶有一個(gè)字段列名的初始行。如果這樣一個(gè)文件中的數(shù)據(jù)行的換行符再使用回車符，則load data語(yǔ)句可以這樣編寫：


  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. # 如果輸入值不一定包含在引號(hào)內(nèi)，請(qǐng)?jiān)贓NCLOSED BY關(guān)鍵字之前使用OPTIONALLY，如：OPTIONALLY ENCLOSED BY '"'，加上OPTIONALLY 可能會(huì)忽略數(shù)值類型的字段的引用符號(hào)，\

  6. 另外，如果你的csv文件第一行是數(shù)據(jù)而不是列名，那就不能使用IGNORE 1 LINES子句

1.2.8. (col_name_or_user_var,…)指定字段名稱的子句

  • 默認(rèn)情況下，如果使用load data語(yǔ)句時(shí)表名后邊不帶字段，那么會(huì)把整個(gè)表的字段數(shù)據(jù)都導(dǎo)入到數(shù)據(jù)庫(kù)中，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata;

  • 如果只想加載某些列，請(qǐng)指定列列表，如：LOAD DATA INFILE'persondata.txt'INTO TABLE persondata（col1，col2，…）; ，要注意：如果輸入文件中的字段值順序與表中列的順序不同，你需要對(duì)load data語(yǔ)句中的tb_name后跟的字段順序做一下調(diào)整以對(duì)應(yīng)文本文件中的字段順序。否則，MySQL不能判斷如何與表中的順序?qū)R，列出列名時(shí)可以在tb_name后指定具體的列名，也可以使用表達(dá)式生成值指定給某個(gè)列名(使用set語(yǔ)句指定一個(gè)表達(dá)式，復(fù)制給一個(gè)變量，詳見1.2.9小節(jié))，如果沒(méi)有set語(yǔ)句，建議列名寫在tb_name表名后邊，方便理解，有set語(yǔ)句時(shí)就跟set語(yǔ)句寫在一起

  • 鄭州不孕不育醫(yī)院：http://yyk.39.net/zz3/zonghe/1d427.html

    • 如果發(fā)現(xiàn)文件中的列順序和表中的列順序不符，或者只想加載部分列，在命令中加上列的順序時(shí)指定的字段名也不一定非要放在緊跟著表名，可以放在語(yǔ)句最后面也可以，如：load data infile "/tmp/filename.txt" into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);如果只需要導(dǎo)入一個(gè)字段id，則把 (id,content,name)換做(id)即可

    • 使用示例參考1.1小節(jié)的“如果文本文件中的數(shù)據(jù)字段與表結(jié)構(gòu)中的字段定義順序不同，則使用如下語(yǔ)句指定載入表中的字段順序”演示部分

1.2.8. SET col_name = expr,…子句

  • 將列做一定的數(shù)值轉(zhuǎn)換后再加載，使用子句set col_name = expr,.. 指定，要注意：col_name必須為表中真實(shí)的列名，expr可以是任意的表達(dá)式或者子查詢，只要返回的數(shù)據(jù)結(jié)果值能對(duì)應(yīng)上表中的字段數(shù)據(jù)定義類型即可，注意，非set語(yǔ)句生成的列名，必須使用括號(hào)括起來(lái)，否則報(bào)語(yǔ)法錯(cuò)誤。


  1. # 如果系統(tǒng)將id列的文本數(shù)據(jù)加上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; #嚴(yán)格模式下因?yàn)槲谋局卸嗔艘粋€(gè)字段被截?cái)嗔耍跃芙^導(dǎo)入

  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關(guān)鍵字強(qiáng)制進(jìn)行截?cái)嘧詈笠粋€(gè)字段的null值列進(jìn)行導(dǎo)入，\

  28. 注意，如果不使用local關(guān)鍵字，那就需要修改sql_mode才能導(dǎo)入



  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文件中的某些列進(jìn)行計(jì)算后生成新的列插入，這里演示兩個(gè)字段進(jìn)行相加后導(dǎo)入另外一個(gè)字段中：

  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關(guān)鍵字，那就需要修改sql_mode才能導(dǎo)入

  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. # 可以直接使用一個(gè)用戶變量并進(jìn)行計(jì)算(計(jì)算表達(dá)式可以使用函數(shù)、運(yùn)算符、子查詢等都允許)，然后賦值給test4列直接導(dǎo)入，而不需要從文件中讀取test4列數(shù)據(jù)，該列數(shù)據(jù)也允許在文件中不存在

  2. admin@localhost : xiaoboluo 06:27:06> alter table test4 add column test4 varchar(20); #新建一個(gè)字段test4，用于導(dǎo)入set子句計(jì)算的值

  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; #設(shè)置一個(gè)用戶變量

  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) ; #執(zhí)行導(dǎo)入，使用set子句導(dǎo)入test4列通過(guò)表達(dá)式\

  16. round(@test/100,0)計(jì)算之后的值



  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表中導(dǎo)入的數(shù)據(jù)，從以下結(jié)果中來(lái)看，導(dǎo)入數(shù)據(jù)正確

  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子句可以將一個(gè)內(nèi)部函數(shù)返回的值直接導(dǎo)入到一個(gè)指定列

  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)

  • 使用指定列名或者變量列表時(shí)SET子句的使用受以下限制：

    • SET子句中的賦值表達(dá)式賦值運(yùn)算符的左側(cè)只能使用數(shù)據(jù)庫(kù)表中的真實(shí)列名

    • 您可以在SET子句中的右側(cè)使用子查詢。返回要分配給列的值的子查詢可能僅是標(biāo)量子查詢。此外，在這個(gè)子查詢中您不能使用load data語(yǔ)句正在操作的表

    • SET子句不會(huì)處理IGNORE子句忽略的行。

    • 用固定行格式加載數(shù)據(jù)時(shí)，不能使用用戶變量，因?yàn)橛脩糇兞恐抵g沒(méi)有顯示寬度

  • 如果輸入行的字段太多（多過(guò)表中的字段數(shù)量），則會(huì)忽略額外的字段，并增加警告數(shù)。如果輸入行的字段太少，那么輸入字段缺少的表列被設(shè)置為其默認(rèn)值，在解析文本文件時(shí)，空串字段數(shù)據(jù)與缺少字段值不同（空串會(huì)直接作為數(shù)據(jù)插入，而缺少字段時(shí)，會(huì)根據(jù)字段定義的默認(rèn)值進(jìn)行填充），如下：

    • 對(duì)于字符串類型，列設(shè)置為空字符串

    • 對(duì)于數(shù)字類型，列設(shè)置為0

    • 對(duì)于日期和時(shí)間類型，列將該類型設(shè)置為適當(dāng)?shù)摹傲恪敝?/p>

2、批量導(dǎo)出和批量導(dǎo)入

  • 使用mysqldump批量導(dǎo)出生成表的txt文件，并使用mysqlimport批量導(dǎo)入表的txt文件到數(shù)據(jù)庫(kù)中

2.1. 使用mysqldump批量導(dǎo)出

  • 使用mysqldump導(dǎo)出數(shù)據(jù)為文本的語(yǔ)法如下：


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

  • 其中option參數(shù)是以下幾種可選參數(shù)：??
    --fields-terminated-by 'string' 字段分隔符??
    --fields-enclosed-by 'char' 字段引用符??
    --fields-optionally-enclosed-by 'char' 字段引用符，只在char,varchar,text等字段類型上生效??
    --fields-escaped-by 'char' 轉(zhuǎn)義字符??
    --lines-terminated-by 'string' 記錄結(jié)束符，即換行符

  • 示例


  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選項(xiàng)時(shí)，--master-data=2打印的binlog pos信息會(huì)直接打印在標(biāo)準(zhǔn)輸出上



  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選項(xiàng)批量導(dǎo)出的表數(shù)據(jù)除了文本文件之外，還同時(shí)備份了表結(jié)構(gòu)

  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批量導(dǎo)出

2.2.1. mysqlimport語(yǔ)法及其參數(shù)說(shuō)明

  • mysqlimport實(shí)用程序加載數(shù)據(jù)文件時(shí)，它通過(guò)向服務(wù)器發(fā)送LOAD DATA INFILE語(yǔ)句來(lái)實(shí)現(xiàn)(它實(shí)際是客戶端提供了load data infile語(yǔ)句的一個(gè)命令行接口)，可以使用--local選項(xiàng)使mysqlimport從客戶端主機(jī)（而不是mysql server主機(jī)）讀取數(shù)據(jù)文件。如果客戶端和服務(wù)器支持壓縮協(xié)議，則可以指定--compress選項(xiàng)以在慢速網(wǎng)絡(luò)中獲得更好的性能。

  • 使用mysqlimport命令，語(yǔ)法如下：


  1. mysqlimport -uroot -p 'xxx' [--local] db_name order_tab.txt [iption]

  • 其中，option參數(shù)可以是如下選項(xiàng)


  1. --fields-terminated-by=name 指定字段分隔符

  2. --fields-enclosed-by=name 指定字段引用符

  3. --fields-optionally-enclosed-by=name 指定字段引用符，但只在char、varchar、text字段上使用引用符

  4. --fields-escaped-by=name 指定轉(zhuǎn)義字符

  5. --lines-terminated-by=name 指定行記錄結(jié)束符（換行符）

  6. --ignore-liens=number 忽略前幾行

  7. --low-priority 碰到有其他線程update操作操作的表與導(dǎo)入操作表相同時(shí)，延遲執(zhí)行導(dǎo)入操作

  8. -i, --ignore 如果碰到唯一鍵沖突就忽略沖突行導(dǎo)入


  10. -r, --replace 如果碰到唯一鍵沖突就覆蓋沖突行導(dǎo)入


  12. -L, --local 從客戶端主機(jī)加載數(shù)據(jù)文本文件


  14. -C, --compress 在C/S模型之間使用壓縮傳輸數(shù)據(jù)


  16. -c, --columns=name 指定需要導(dǎo)入哪些列，與load data語(yǔ)句中一樣需要指定表定義中真實(shí)的列名，有多個(gè)列名時(shí)使用逗號(hào)分隔


  18. --default-character-set=name 設(shè)置使用該選項(xiàng)指定的字符集來(lái)解析文本文件中的內(nèi)容


  20. -h, --host 指定導(dǎo)入server的主機(jī)IP


  22. -p, --password[=name] 指定導(dǎo)入server的用戶密碼


  24. -P, --port=# 指定導(dǎo)入server的監(jiān)聽端口


  26. --use-threads=# 指定多少個(gè)線程并發(fā)執(zhí)行l(wèi)oad data語(yǔ)句（實(shí)測(cè)單表時(shí)指定多線程時(shí)要比單線程要快，由于數(shù)據(jù)量小，測(cè)試出來(lái)的差別并不大，官方并沒(méi)有說(shuō)明是基于什么級(jí)別的并發(fā)，\

  27. 只寫了一句：Load files in parallel using N threads，推測(cè)可能是基于類似mydumper的并發(fā)，但是多表導(dǎo)入時(shí)指定多線程就明顯比單線程要快很多）




  31. -u, --user=name 指定導(dǎo)入server的用戶名


  33. -d, --delete 指定導(dǎo)入操作之前先把表清空（實(shí)測(cè)重復(fù)導(dǎo)入時(shí)加了這個(gè)選項(xiàng)之后可以正常執(zhí)行，，通過(guò)解析binlog發(fā)現(xiàn)，發(fā)現(xiàn)binlog中記錄的第二次和第一次導(dǎo)入的語(yǔ)句完全相同是，\

  34. 第二次導(dǎo)入時(shí)如果發(fā)現(xiàn)表中有沖突數(shù)據(jù)，就先執(zhí)行的不帶where條件的delete，所有表先delete掉，然后再執(zhí)行l(wèi)oad data語(yǔ)句導(dǎo)入數(shù)據(jù)，另外，當(dāng)與replace一起使用時(shí)，忽略replace選項(xiàng)）

2.2.2. mysqlimport用法演示示例

  • 先執(zhí)行清理server中表的數(shù)據(jù)



  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. 單表導(dǎo)入

  • 使用mysqlimport命令導(dǎo)入單張表


  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 ~]# mysqlimport -uadmin -pletsg0 -h20.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. # 查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)

  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內(nèi)部調(diào)用的load data語(yǔ)句在這里

  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中時(shí)內(nèi)部把load data語(yǔ)句轉(zhuǎn)換為了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.多表導(dǎo)入

  • 清理掉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導(dǎo)入多表


  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h20.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. # 多表導(dǎo)入時(shí)可以使用參數(shù)--use-threads指定多個(gè)線程，明顯比單線程導(dǎo)入速度要快

  15. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -pletsg0 -h20.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

  • 校驗(yàn)數(shù)據(jù)


  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查看（由于內(nèi)容較多，這里就不貼出來(lái)了，binlog解析的多表導(dǎo)入操作在binlog中記錄的就是一個(gè)表一個(gè)load data語(yǔ)句）

3、總結(jié)

  • 關(guān)于使用local子句與不使用local子句的時(shí)候的差異

    • 如果load data語(yǔ)句使用了local子句，則客戶端使用TCP遠(yuǎn)程連接mysql server時(shí)，沒(méi)有file權(quán)限仍然能夠?qū)胛谋疚募?，這個(gè)時(shí)候是非常危險(xiǎn)的，因?yàn)閘ocal子句的內(nèi)部原理是從客戶端的主機(jī)讀取文本文件并傳送到server端的/tmp目錄并保存為一個(gè)臨時(shí)文件，再執(zhí)行l(wèi)oad data語(yǔ)句的。另外，要使用local子句，還需要看server端啟動(dòng)是否關(guān)閉了local_infile選項(xiàng)（如果不指定該選項(xiàng)，則服務(wù)端默認(rèn)為ON），mysql client連接時(shí)是否關(guān)閉了local_infile選項(xiàng)（如果不指定該選項(xiàng)，則客戶端默認(rèn)為ON），local_infile在server或client端任意一端關(guān)閉都不能使用local子句，會(huì)報(bào)錯(cuò)誤：ERROR 1148 (42000): The used command is not allowed with this MySQL version

    • 如果load data語(yǔ)句不使用local子句，則這個(gè)時(shí)候用戶必須要有file權(quán)限才能夠執(zhí)行導(dǎo)入文本文件（并且只能夠?qū)雜erver端的本地文本文件），如果沒(méi)有file權(quán)限，可能報(bào)沒(méi)有file權(quán)限的錯(cuò)誤，也可能報(bào)錯(cuò)：ERROR 1045 (28000): Access denied for user 'test'@'%' (using password: YES)

    • 如果不想這么麻煩（因?yàn)橐拗瓶蛻舳耸褂胠ocal子句在沒(méi)有file權(quán)限的時(shí)候使用load data語(yǔ)句，需要在server端使用local_infile=OFF來(lái)關(guān)閉，不使用local子句時(shí)，如果用戶沒(méi)有file權(quán)限，那很顯然不能夠使用load data語(yǔ)句，但是如果還想限制由具有file權(quán)限的用戶怎么辦？），可以使用參數(shù)secure_file_priv=null，設(shè)置為null時(shí)，全面禁止使用load data語(yǔ)句（不管使用local子句還是不使用都不允許執(zhí)行l(wèi)oad data語(yǔ)句）

  • 強(qiáng)調(diào)一點(diǎn)：在mysql的主備復(fù)制架構(gòu)中，load data語(yǔ)句被認(rèn)為是不安全的，要使得load data語(yǔ)句安全地進(jìn)行復(fù)制，在binlog_format=mixed格式下會(huì)轉(zhuǎn)為row格式記錄，在binlog_format=statement時(shí)執(zhí)行l(wèi)oad data語(yǔ)句不會(huì)發(fā)出警告，而是內(nèi)部通過(guò)一些列的流程來(lái)處理。具體是如何處理的呢，請(qǐng)看下回分解《load data語(yǔ)句如何保證主備復(fù)制數(shù)據(jù)一致性》


向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手