欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

Mysql必讀mysql load data infile 的用法(40w數據 用了3-5秒導進mysql)

作者：VEPHP   時間 2017-09-28

《Mysql必讀mysql load data infile 的用法(40w數據 用了3-5秒導進mysql)》要點：
本文介紹了Mysql必讀mysql load data infile 的用法(40w數據 用了3-5秒導進mysql)，希望對您有用。如果有疑問，可以聯系我們。

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

MYSQL數據庫不知道用replace?這個關鍵字的話,還是會亂碼..不同、等高手回答.

MYSQL數據庫在詳細的介紹,推薦大家去看mysql手冊去吧、里面介紹的很詳細、

MYSQL數據庫在使用LOAD?DATA到MySQL的時候,有2種情況：?

MYSQL數據庫(1)在遠程客戶端(需要添加選項：--local-infile=1)導入遠程客戶端文本到MySQL,需指定LOCAL(默認就是ignore),加ignore選項會放棄數據,加replace選項會更新數據,都不會出現唯一性約束問題.?

MYSQL數據庫(2)在本地服務器導入本地服務器文本到MySQL,不指定LOACL,出現唯一性約束沖突,會失敗回滾,數據導入不進去,這個時候就需要加ignore或者replace來導入數據.

MYSQL數據庫測試如下：

MYSQL數據庫(1)本地服務器導入本地服務器文本

MYSQL數據庫mysql>?show?create?table?tmp_loaddata\G;

MYSQL數據庫***************************?1.?row?***************************

MYSQL數據庫Table:?tmp_loaddata

MYSQL數據庫Create?Table:CREATE?TABLE?`tmp_loaddata`?(

MYSQL數據庫`id`?int(11)?NOT?NULL,

MYSQL數據庫`name`?varchar(10)?DEFAULT?NULL,

MYSQL數據庫PRIMARY?KEY?(`id`)

MYSQL數據庫)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1

MYSQL數據庫1?row?in?set?(0.00?sec)

MYSQL數據庫ERROR:

MYSQL數據庫No?query?specified

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------+

MYSQL數據庫|?id?|?name?|

MYSQL數據庫+----+------+

MYSQL數據庫|1?|?test?|

MYSQL數據庫+----+------+

MYSQL數據庫1?row?in?set?(0.00?sec)

MYSQL數據庫mysql>

MYSQL數據庫mysql>system?cat?/home/zhuxu/1.txt

MYSQL數據庫1,new?update

MYSQL數據庫2,new?update

MYSQL數據庫mysql>

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

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

MYSQL數據庫#出現唯一性約束沖突,會失敗回滾

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------+

MYSQL數據庫|?id?|?name?|

MYSQL數據庫+----+------+

MYSQL數據庫|1?|?test?|

MYSQL數據庫+----+------+

MYSQL數據庫1?row?in?set?(0.00?sec)

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

MYSQL數據庫Query?OK,1?row?affected(0.00?sec)

MYSQL數據庫Records:?2Deleted:?0Skipped:?1Warnings:?0

MYSQL數據庫#使用IGNORE對于沖突的數據丟棄掉.

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------------+

MYSQL數據庫|?id?|?name|

MYSQL數據庫+----+------------+

MYSQL數據庫|1?|?test|

MYSQL數據庫|2?|?new?update?|

MYSQL數據庫+----+------------+

MYSQL數據庫2?rows?in?set?(0.00?sec)

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

MYSQL數據庫Query?OK,3?rows?affected(0.00?sec)

MYSQL數據庫Records:?2Deleted:?1Skipped:?0Warnings:?0

MYSQL數據庫#使用REPLACE對于沖突的數據進行更新.

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------------+

MYSQL數據庫|?id?|?name|

MYSQL數據庫+----+------------+

MYSQL數據庫|1?|?new?update?|

MYSQL數據庫|2?|?new?update?|

MYSQL數據庫+----+------------+

MYSQL數據庫2?rows?in?set?(0.00?sec)

MYSQL數據庫(2)遠程客戶端導入遠程客戶端文本

MYSQL數據庫[zhuxu@xentest9-vm1?tmp]$mysql?-uzhuxu?-pzhuxu?test?-h10.254.5.151

MYSQL數據庫Welcome?to?the?MySQL?monitor.Commands?end?with?;?or?\g.

MYSQL數據庫Your?MySQL?connection?id?is?15

MYSQL數據庫Server?version:?5.1.47-log?Source?distribution

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

MYSQL數據庫This?software?comes?with?ABSOLUTELY?NO?WARRANTY.?This?is?free?software,

MYSQL數據庫and?you?are?welcome?to?modify?and?redistribute?it?under?the?GPL?v2?license

MYSQL數據庫Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------+

MYSQL數據庫|?id?|?name?|

MYSQL數據庫+----+------+

MYSQL數據庫|1?|?test?|

MYSQL數據庫+----+------+

MYSQL數據庫1?row?in?set?(0.00?sec)

MYSQL數據庫mysql>system?cat?/tmp/2.txt

MYSQL數據庫1,new?update

MYSQL數據庫2,new?update

MYSQL數據庫3,new?update

MYSQL數據庫mysql>

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

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

MYSQL數據庫#由于數據庫服務器沒有對應的文本文件,所以報錯.

MYSQL數據庫mysql>

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

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

MYSQL數據庫#進去mysql遠程客戶端,還需要加--local-infile=1參數指定.

MYSQL數據庫mysql>?exit

MYSQL數據庫Bye

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

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

MYSQL數據庫--------------

MYSQL數據庫LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數據庫--------------

MYSQL數據庫Query?OK,2?rows?affected(0.00?sec)

MYSQL數據庫Records:?3Deleted:?0Skipped:?1Warnings:?0

MYSQL數據庫Bye

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------------+

MYSQL數據庫|?id?|?name|

MYSQL數據庫+----+------------+

MYSQL數據庫|1?|?test|

MYSQL數據庫|2?|?new?update?|

MYSQL數據庫|3?|?new?update?|

MYSQL數據庫+----+------------+

MYSQL數據庫3?rows?in?set?(0.00?sec)

MYSQL數據庫#

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

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

MYSQL數據庫--------------

MYSQL數據庫LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?IGNORE?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數據庫--------------

MYSQL數據庫Query?OK,0?rows?affected(0.00?sec)

MYSQL數據庫Records:?3Deleted:?0Skipped:?3Warnings:?0

MYSQL數據庫Bye

MYSQL數據庫mysql>select?*?from?tmp_loaddata;

MYSQL數據庫+----+------------+

MYSQL數據庫|?id?|?name|

MYSQL數據庫+----+------------+

MYSQL數據庫|1?|?test|

MYSQL數據庫|2?|?new?update?|

MYSQL數據庫|3?|?new?update?|

MYSQL數據庫+----+------------+

MYSQL數據庫3?rows?in?set?(0.00?sec)

MYSQL數據庫#

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

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

MYSQL數據庫--------------

MYSQL數據庫LOAD?DATA?LOCAL?INFILE?'/tmp/2.txt'?REPLACE?INTO?TABLE?tmp_loaddata?FIELDS?TERMINATED?BY?','

MYSQL數據庫--------------

MYSQL數據庫Query?OK,4?rows?affected(0.00?sec)

MYSQL數據庫Records:?3Deleted:?1Skipped:?0Warnings:?0

MYSQL數據庫Bye

MYSQL數據庫mysql>?select?*?from?tmp_loaddata;

MYSQL數據庫+----+------------+

MYSQL數據庫|?id?|?name|

MYSQL數據庫+----+------------+

MYSQL數據庫|1?|?new?update?|

MYSQL數據庫|2?|?new?update?|

MYSQL數據庫|3?|?new?update?|

MYSQL數據庫+----+------------+

MYSQL數據庫3?rows?in?set?(0.00?sec)

MYSQL數據庫--EOF--

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5155.html


    標簽：