乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql必讀mysql load data infile 的用法(40w數(shù)據(jù) 用了3-5秒導(dǎo)進(jìn)mysql)

作者：VEPHP   時(shí)間 2017-09-28

《Mysql必讀mysql load data infile 的用法(40w數(shù)據(jù) 用了3-5秒導(dǎo)進(jìn)mysql)》要點(diǎn)：
本文介紹了Mysql必讀mysql load data infile 的用法(40w數(shù)據(jù) 用了3-5秒導(dǎo)進(jìn)mysql)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

MYSQL數(shù)據(jù)庫(kù)如果是導(dǎo)入有中文的數(shù)據(jù),我的mysql?設(shè)置的utf8?字符集,所以你要導(dǎo)入的xxx.txt?文件也要保存utf-8的字符集,命令?load?data?infile?"d:/Websites/Sxxxx/test1.txt"?ignore?into?table?`names`?fields?terminated?by?','?enclosed?by?'"';

MYSQL數(shù)據(jù)庫(kù)不知道用replace?這個(gè)關(guān)鍵字的話,還是會(huì)亂碼..不同、等高手回答.

MYSQL數(shù)據(jù)庫(kù)在詳細(xì)的介紹,推薦大家去看mysql手冊(cè)去吧、里面介紹的很詳細(xì)、

MYSQL數(shù)據(jù)庫(kù)在使用LOAD?DATA到MySQL的時(shí)候,有2種情況：?

MYSQL數(shù)據(jù)庫(kù)(1)在遠(yuǎn)程客戶(hù)端(需要添加選項(xiàng)：--local-infile=1)導(dǎo)入遠(yuǎn)程客戶(hù)端文本到MySQL,需指定LOCAL(默認(rèn)就是ignore),加ignore選項(xiàng)會(huì)放棄數(shù)據(jù),加replace選項(xiàng)會(huì)更新數(shù)據(jù),都不會(huì)出現(xiàn)唯一性約束問(wèn)題.?

MYSQL數(shù)據(jù)庫(kù)(2)在本地服務(wù)器導(dǎo)入本地服務(wù)器文本到MySQL,不指定LOACL,出現(xiàn)唯一性約束沖突,會(huì)失敗回滾,數(shù)據(jù)導(dǎo)入不進(jìn)去,這個(gè)時(shí)候就需要加ignore或者replace來(lái)導(dǎo)入數(shù)據(jù).

MYSQL數(shù)據(jù)庫(kù)測(cè)試如下：

MYSQL數(shù)據(jù)庫(kù)(1)本地服務(wù)器導(dǎo)入本地服務(wù)器文本

MYSQL數(shù)據(jù)庫(kù)mysql>?show?create?table?tmp_loaddata\G;

MYSQL數(shù)據(jù)庫(kù)***************************?1.?row?***************************

MYSQL數(shù)據(jù)庫(kù)Table:?tmp_loaddata

MYSQL數(shù)據(jù)庫(kù)Create?Table:CREATE?TABLE?`tmp_loaddata`?(

MYSQL數(shù)據(jù)庫(kù)`id`?int(11)?NOT?NULL,

MYSQL數(shù)據(jù)庫(kù)`name`?varchar(10)?DEFAULT?NULL,

MYSQL數(shù)據(jù)庫(kù)PRIMARY?KEY?(`id`)

MYSQL數(shù)據(jù)庫(kù))?ENGINE=InnoDB?DEFAULT?CHARSET=latin1

MYSQL數(shù)據(jù)庫(kù)1?row?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)ERROR:

MYSQL數(shù)據(jù)庫(kù)No?query?specified

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)1?row?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)mysql>

MYSQL數(shù)據(jù)庫(kù)mysql>system?cat?/home/zhuxu/1.txt

MYSQL數(shù)據(jù)庫(kù)1,new?update

MYSQL數(shù)據(jù)庫(kù)2,new?update

MYSQL數(shù)據(jù)庫(kù)mysql>

MYSQL數(shù)據(jù)庫(kù)mysql>LOAD?DATA?INFILE?'/home/zhuxu/1.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?',';

MYSQL數(shù)據(jù)庫(kù)ERROR?1062?(23000):?Duplicate?entry?'1'?for?key?'PRIMARY'

MYSQL數(shù)據(jù)庫(kù)#出現(xiàn)唯一性約束沖突,會(huì)失敗回滾

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)1?row?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)mysql>LOAD?DATA?INFILE?'/home/zhuxu/1.txt'?IGNOREINTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?',';

MYSQL數(shù)據(jù)庫(kù)Query?OK,1?row?affected(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)Records:?2Deleted:?0Skipped:?1Warnings:?0

MYSQL數(shù)據(jù)庫(kù)#使用IGNORE對(duì)于沖突的數(shù)據(jù)丟棄掉.

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test|

MYSQL數(shù)據(jù)庫(kù)|2?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)2?rows?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)mysql>LOAD?DATA?INFILE?'/home/zhuxu/1.txt'?REPLACEINTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?',';

MYSQL數(shù)據(jù)庫(kù)Query?OK,3?rows?affected(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)Records:?2Deleted:?1Skipped:?0Warnings:?0

MYSQL數(shù)據(jù)庫(kù)#使用REPLACE對(duì)于沖突的數(shù)據(jù)進(jìn)行更新.

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|1?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)|2?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)2?rows?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)(2)遠(yuǎn)程客戶(hù)端導(dǎo)入遠(yuǎn)程客戶(hù)端文本

MYSQL數(shù)據(jù)庫(kù)[zhuxu@xentest9-vm1?tmp]$mysql?-uzhuxu?-pzhuxu?test?-h10.254.5.151

MYSQL數(shù)據(jù)庫(kù)Welcome?to?the?MySQL?monitor.Commands?end?with?;?or?\g.

MYSQL數(shù)據(jù)庫(kù)Your?MySQL?connection?id?is?15

MYSQL數(shù)據(jù)庫(kù)Server?version:?5.1.47-log?Source?distribution

MYSQL數(shù)據(jù)庫(kù)Copyright?(c)?2000,?2010,?Oracle?and/or?its?affiliates.?All?rights?reserved.

MYSQL數(shù)據(jù)庫(kù)This?software?comes?with?ABSOLUTELY?NO?WARRANTY.?This?is?free?software,

MYSQL數(shù)據(jù)庫(kù)and?you?are?welcome?to?modify?and?redistribute?it?under?the?GPL?v2?license

MYSQL數(shù)據(jù)庫(kù)Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test?|

MYSQL數(shù)據(jù)庫(kù)+----+------+

MYSQL數(shù)據(jù)庫(kù)1?row?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)mysql>system?cat?/tmp/2.txt

MYSQL數(shù)據(jù)庫(kù)1,new?update

MYSQL數(shù)據(jù)庫(kù)2,new?update

MYSQL數(shù)據(jù)庫(kù)3,new?update

MYSQL數(shù)據(jù)庫(kù)mysql>

MYSQL數(shù)據(jù)庫(kù)mysql>LOAD?DATA?INFILE?'/tmp/2.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?',';

MYSQL數(shù)據(jù)庫(kù)ERROR?13?(HY000):?Can't?get?stat?of?'/tmp/2.txt'?(Errcode:?2)

MYSQL數(shù)據(jù)庫(kù)#由于數(shù)據(jù)庫(kù)服務(wù)器沒(méi)有對(duì)應(yīng)的文本文件,所以報(bào)錯(cuò).

MYSQL數(shù)據(jù)庫(kù)mysql>

MYSQL數(shù)據(jù)庫(kù)mysql>LOAD?DATA?LOCALINFILE?'/tmp/2.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?',';

MYSQL數(shù)據(jù)庫(kù)ERROR?1148?(42000):?The?used?command?is?not?allowed?with?this?MySQL?version

MYSQL數(shù)據(jù)庫(kù)#進(jìn)去mysql遠(yuǎn)程客戶(hù)端,還需要加--local-infile=1參數(shù)指定.

MYSQL數(shù)據(jù)庫(kù)mysql>?exit

MYSQL數(shù)據(jù)庫(kù)Bye

MYSQL數(shù)據(jù)庫(kù)[zhuxu@xentest9-vm1?tmp]$mysql?-uzhuxu?-pzhuxu?test?-h10.254.5.151?--local-infile=1--show-warnings?-v?-v?-v?\

MYSQL數(shù)據(jù)庫(kù)>?-e?"LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','";

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)Query?OK,2?rows?affected(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)Records:?3Deleted:?0Skipped:?1Warnings:?0

MYSQL數(shù)據(jù)庫(kù)Bye

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test|

MYSQL數(shù)據(jù)庫(kù)|2?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)|3?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)3?rows?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)#

MYSQL數(shù)據(jù)庫(kù)[zhuxu@xentest9-vm1?tmp]$mysql?-uzhuxu?-pzhuxu?test?-h10.254.5.151?--local-infile=1--show-warnings?-v?-v?-v?\

MYSQL數(shù)據(jù)庫(kù)>?-e?"LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?IGNOREINTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','";

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?IGNORE?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)Query?OK,0?rows?affected(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)Records:?3Deleted:?0Skipped:?3Warnings:?0

MYSQL數(shù)據(jù)庫(kù)Bye

MYSQL數(shù)據(jù)庫(kù)mysql>select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|1?|?test|

MYSQL數(shù)據(jù)庫(kù)|2?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)|3?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)3?rows?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)#

MYSQL數(shù)據(jù)庫(kù)[zhuxu@xentest9-vm1?tmp]$mysql?-uzhuxu?-pzhuxu?test?-h10.254.5.151?--local-infile=1--show-warnings?-v?-v?-v?\

MYSQL數(shù)據(jù)庫(kù)>?-e?"LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?REPLACEINTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','";

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?REPLACE?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數(shù)據(jù)庫(kù)--------------

MYSQL數(shù)據(jù)庫(kù)Query?OK,4?rows?affected(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)Records:?3Deleted:?1Skipped:?0Warnings:?0

MYSQL數(shù)據(jù)庫(kù)Bye

MYSQL數(shù)據(jù)庫(kù)mysql>?select?*?from?tmp_loaddata;

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|?id?|?name|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)|1?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)|2?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)|3?|?new?update?|

MYSQL數(shù)據(jù)庫(kù)+----+------------+

MYSQL數(shù)據(jù)庫(kù)3?rows?in?set?(0.00?sec)

MYSQL數(shù)據(jù)庫(kù)--EOF--

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5155.html


    標(biāo)簽：