MySQL的文本導(dǎo)入之loaddatalocal

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

成都創(chuàng)新互聯(lián)擁有網(wǎng)站維護(hù)技術(shù)和項(xiàng)目管理團(tuán)隊(duì)，建立的售前、實(shí)施和售后服務(wù)體系，為客戶提供定制化的做網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)站維護(hù)、達(dá)州托管服務(wù)器解決方案。為客戶網(wǎng)站安全和日常運(yùn)維提供整體管家式外包優(yōu)質(zhì)服務(wù)。我們的網(wǎng)站維護(hù)服務(wù)覆蓋集團(tuán)企業(yè)、上市公司、外企網(wǎng)站、成都做商城網(wǎng)站、政府網(wǎng)站等各類型客戶群體，為全球千余家企業(yè)提供全方位網(wǎng)站維護(hù)、服務(wù)器維護(hù)解決方案。

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)入命令：?

drop?table?aa;

create?table?aa(a?varchar(40),tt?text);

load?data?local?infile?'a.txt'?into?table?aa;

select?*?from?aa;

過(guò)程：?

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)

幫助：

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<            
            
                        

            當(dāng)前文章：MySQL的文本導(dǎo)入之loaddatalocal            

            分享URL：http://weahome.cn/article/gejeci.html


    


    其他資訊
    
        
                在mysql中怎么統(tǒng)計(jì)的簡(jiǎn)單介紹
            

                c語(yǔ)言金字塔編程用函數(shù) c語(yǔ)言程序設(shè)計(jì)數(shù)字金字塔
            

                java代碼塊作用域 實(shí)例作用域和分類作用域
            

                linux編輯修改命令行 linux常用編輯命令
            

                go語(yǔ)言編寫的搜索引擎 go語(yǔ)言簡(jiǎn)介
            
    


    
        
            
                
                    網(wǎng)站制作
                    成都網(wǎng)站制作定制網(wǎng)站制作成都網(wǎng)站制作公司成都網(wǎng)站制作手機(jī)網(wǎng)站制作設(shè)計(jì)綿陽(yáng)網(wǎng)站制作公司                
                
                    企業(yè)服務(wù)
                    互聯(lián)網(wǎng)藥品信息服務(wù)資格證ssl證書(shū)免費(fèi)建站免費(fèi)網(wǎng)站建設(shè)軟文發(fā)布平臺(tái)藥房許可證                
                
                    網(wǎng)站建設(shè)
                    雅安網(wǎng)站建設(shè)響應(yīng)式網(wǎng)站建設(shè)專業(yè)網(wǎng)站建設(shè)綿陽(yáng)網(wǎng)站建設(shè)成都網(wǎng)站建設(shè)流程成都集團(tuán)網(wǎng)站建設(shè)                
                
                    服務(wù)器托管
                    光華服務(wù)器托管雅安服務(wù)器托管機(jī)柜租用重慶水土雙線托管綿陽(yáng)服務(wù)器托管移動(dòng)服務(wù)器托管                
            
        
        
            全國(guó)免費(fèi)咨詢:
            400-028-6601
            業(yè)務(wù)咨詢：028-86922220 / 13518219792
            節(jié)假值班：18980820575 / 13518219792
            聯(lián)系地址：成都市太升南路288號(hào)錦天國(guó)際A幢1002號(hào)
        
    
    
        
            Copyright ? 成都創(chuàng)新互聯(lián)科技有限公司重慶分公司  渝ICP備2021005571號(hào)
            
        
    
    
        
            友情鏈接:：
            成都網(wǎng)站建設(shè)
            重慶網(wǎng)站建設(shè)
            四川網(wǎng)站建設(shè)
            重慶建設(shè)網(wǎng)站
            移動(dòng)服務(wù)器托管
            成都服務(wù)器托管
            云服務(wù)器
            廣告設(shè)計(jì)制作
            重慶網(wǎng)頁(yè)設(shè)計(jì)
            重慶做網(wǎng)站
            重慶網(wǎng)站制作
            重慶網(wǎng)站建設(shè)
            重慶網(wǎng)站公司
            渝中網(wǎng)站制作
            重慶網(wǎng)站設(shè)計(jì)
        
    


    
        
            
                
                在線咨詢
            
        
        
            
                
                電話咨詢
            
        
        
            
                
                QQ咨詢
            
        
        
            
                
                預(yù)約顧問(wèn)
            
        
    


    
        
            
                
                在線咨詢
            
        
    
    
        微信咨詢
        
    
    
        電話咨詢
        
            028-86922220（工作日）
            18980820575（7×24）
        
    
    
        
            
                提交需求
            
        
    
    
        
            
                返回頂部
            
        
    




感谢您访问我们的网站，您可能还对以下资源感兴趣：
真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆