mysql load data infile 的用法(40w数据 用了3-5秒导进mysql)

如果是导入有中文的数据，我的mysql/' target='_blank'>mysql设置的utf8字符集，所以你要导入的xxx.txt文件也要保存utf-8的字符集，命令loaddatainfile"d:/Websites/Sxxxx/test1.txt"ignoreintotable`names`fieldsterminatedby','enclosedby'"';

不知道用replace这个关键字的话，还是会乱码。。不同、等高手回答。

在详细的介绍，推荐大家去看MySQL手册去吧、里面介绍的很详细、

在使用LOADDATA到MySQL的时候，有2种情况：

（1）在远程客户端（需要添加选项：--local-infile=1）导入远程客户端文本到MySQL，需指定LOCAL（默认就是ignore）,加ignore选项会放弃数据，加replace选项会更新数据，都不会出现唯一性约束问题。

（2）在本地服务器导入本地服务器文本到MySQL，不指定LOACL，出现唯一性约束冲突，会失败回滚，数据导入不进去，这个时候就需要加ignore或者replace来导入数据。

测试如下：

（1）本地服务器导入本地服务器文本

mysql>showcreatetabletmp_loaddata\G;

***************************1.row***************************

Table:tmp_loaddata

CreateTable:CREATETABLE`tmp_loaddata`(

`id`int(11)NOTNULL,

`name`varchar(10)DEFAULTNULL,

PRIMARYKEY(`id`)

)ENGINE=InnoDBDEFAULTCHARSET=latin1

1rowinset(0.00sec)

ERROR:

Noqueryspecified

mysql>select*fromtmp_loaddata;

+----+------+

|id|name|

+----+------+

|1|test|

+----+------+

1rowinset(0.00sec)

mysql>

mysql>systemcat/home/zhuxu/1.txt

1,newupdate

2,newupdate

mysql>

mysql>LOADDATAINFILE'/home/zhuxu/1.txt'INTOTABLEtmp_loaddataFIELDSTERMINATEDBY',';

ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'

#出现唯一性约束冲突，会失败回滚

mysql>select*fromtmp_loaddata;

+----+------+

|id|name|

+----+------+

|1|test|

+----+------+

1rowinset(0.00sec)

mysql>LOADDATAINFILE'/home/zhuxu/1.txt'IGNOREINTOTABLEtmp_loaddataFIELDSTERMINATEDBY',';

QueryOK,1rowaffected(0.00sec)

Records:2Deleted:0Skipped:1Warnings:0

#使用IGNORE对于冲突的数据丢弃掉。

mysql>select*fromtmp_loaddata;

+----+------------+

|id|name|

+----+------------+

|1|test|

|2|newupdate|

+----+------------+

2rowsinset(0.00sec)

mysql>LOADDATAINFILE'/home/zhuxu/1.txt'REPLACEINTOTABLEtmp_loaddataFIELDSTERMINATEDBY',';

QueryOK,3rowsaffected(0.00sec)

Records:2Deleted:1Skipped:0Warnings:0

#使用REPLACE对于冲突的数据进行更新。

mysql>select*fromtmp_loaddata;

+----+------------+

|id|name|

+----+------------+

|1|newupdate|

|2|newupdate|

+----+------------+

2rowsinset(0.00sec)

（2）远程客户端导入远程客户端文本

[zhuxu@xentest9-vm1tmp]$mysql-uzhuxu-pzhuxutest-h10.254.5.151

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis15

Serverversion:5.1.47-logSourcedistribution

Copyright(c)2000,2010,Oracleand/oritsaffiliates.Allrightsreserved.

ThissoftwarecomeswithABSOLUTELYNOWARRANTY.Thisisfreesoftware,

andyouarewelcometomodifyandredistributeitundertheGPLv2license

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>select*fromtmp_loaddata;

+----+------+

|id|name|

+----+------+

|1|test|

+----+------+

1rowinset(0.00sec)

mysql>systemcat/tmp/2.txt

1,newupdate

2,newupdate

3,newupdate

mysql>

mysql>LOADDATAINFILE'/tmp/2.txt'INTOTABLEtmp_loaddataFIELDSTERMINATEDBY',';

ERROR13(HY000):Can'tgetstatof'/tmp/2.txt'(Errcode:2)

#由于数据库服务器没有对应的文本文件，所以报错。

mysql>

mysql>LOADDATALOCALINFILE'/tmp/2.txt'INTOTABLEtmp_loaddataFIELDSTERMINATEDBY',';

ERROR1148(42000):TheusedcommandisnotallowedwiththisMySQLversion

#进去mysql远程客户端，还需要加--local-infile=1参数指定。

mysql>exit

Bye

[zhuxu@xentest9-vm1tmp]$mysql-uzhuxu-pzhuxutest-h10.254.5.151--local-infile=1--show-warnings-v-v-v\

>-e"LOADDATALOCALINFILE'/tmp/2.txt'INTOTABLEtmp_loaddataFIELDSTERMINATEDBY','";

--------------

LOADDATALOCALINFILE'/tmp/2.txt'INTOTABLEtmp_loaddataFIELDSTERMINATEDBY','

--------------

QueryOK,2rowsaffected(0.00sec)

Records:3Deleted:0Skipped:1Warnings:0

Bye

mysql>select*fromtmp_loaddata;

+----+------------+

|id|name|

+----+------------+

|1|test|

|2|newupdate|

|3|newupdate|

+----+------------+

3rowsinset(0.00sec)

#

[zhuxu@xentest9-vm1tmp]$mysql-uzhuxu-pzhuxutest-h10.254.5.151--local-infile=1--show-warnings-v-v-v\

>-e"LOADDATALOCALINFILE'/tmp/2.txt'IGNOREINTOTABLEtmp_loaddataFIELDSTERMINATEDBY','";

--------------

LOADDATALOCALINFILE'/tmp/2.txt'IGNOREINTOTABLEtmp_loaddataFIELDSTERMINATEDBY','

--------------

QueryOK,0rowsaffected(0.00sec)

Records:3Deleted:0Skipped:3Warnings:0

Bye

mysql>select*fromtmp_loaddata;

+----+------------+

|id|name|

+----+------------+

|1|test|

|2|newupdate|

|3|newupdate|

+----+------------+

3rowsinset(0.00sec)

#

[zhuxu@xentest9-vm1tmp]$mysql-uzhuxu-pzhuxutest-h10.254.5.151--local-infile=1--show-warnings-v-v-v\

>-e"LOADDATALOCALINFILE'/tmp/2.txt'REPLACEINTOTABLEtmp_loaddataFIELDSTERMINATEDBY','";

--------------

LOADDATALOCALINFILE'/tmp/2.txt'REPLACEINTOTABLEtmp_loaddataFIELDSTERMINATEDBY','

--------------

QueryOK,4rowsaffected(0.00sec)

Records:3Deleted:1Skipped:0Warnings:0

Bye

mysql>select*fromtmp_loaddata;

+----+------------+

|id|name|

+----+------------+

|1|newupdate|

|2|newupdate|

|3|newupdate|

+----+------------+

3rowsinset(0.00sec)

--EOF--