如果是导入有中文的数据，我的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_loaddataG;
***************************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;org.
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--
【mysql load data infile 的用法(40w数据 用了3-5秒导进mysql)】相关文章：
★ Mysql 原生语句中save or update 的写法汇总
★ 解决mysql 1040错误Too many connections的方法