mysql load data infile 的用法.docx

mysql load data infile 的用法.docx

《mysql load data infile 的用法.docx》由会员分享，可在线阅读，更多相关《mysql load data infile 的用法.docx（19页珍藏版）》请在冰点文库上搜索。

mysqlloaddatainfile的用法

mysqlloaddatainfile的用法（40w数据用了3-5秒导进mysql）

测试数据的时候，生成txt文件应该快点，再用这种方式导入到mysql速度上快点。

40w数据用了3-5秒导进mysql，牛逼毁了

如果是导入有中文的数据，我的mysql 设置的utf8 字符集，所以你要导入的xxx.txt 文件也要保存utf-8的字符集，命令 load data infile "d:

/Websites/Sxxxx/test1.txt" ignore into table `names` fields terminated by ',' enclosed by '"';

不知道用replace 这个关键字的话，还是会乱码。

。

不同、等高手回答。

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

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

（1）在远程客户端（需要添加选项：

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

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

测试如下：

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

mysql> show create table tmp_loaddata\G;

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

Table:

 tmp_loaddata

Create Table:

CREATE TABLE `tmp_loaddata` （

`id` int（11） NOT NULL,

`name` varchar（10） DEFAULT NULL,

PRIMARY KEY （`id`）

） ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set （0.00 sec）

ERROR:

No query specified

mysql>select * from tmp_loaddata;

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

| id | name |

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

|1 | test |

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

1 row in set （0.00 sec）

mysql>

mysql>system cat /home/zhuxu/1.txt

1,new update

2,new update

mysql>

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

ERROR 1062 （23000）:

 Duplicate entry '1' for key 'PRIMARY'

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

mysql>select * from tmp_loaddata;

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

| id | name |

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

|1 | test |

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

1 row in set （0.00 sec）

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

Query OK,1 row affected（0.00 sec）

Records:

 2Deleted:

 0Skipped:

 1Warnings:

 0

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

mysql>select * from tmp_loaddata;

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

| id | name|

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

|1 | test|

|2 | new update |

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

2 rows in set （0.00 sec）

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

Query OK,3 rows affected（0.00 sec）

Records:

 2Deleted:

 1Skipped:

 0Warnings:

 0

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

mysql>select * from tmp_loaddata;

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

| id | name|

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

|1 | new update |

|2 | new update |

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

2 rows in set （0.00 sec）

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

[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151

Welcome to the MySQL monitor.Commands end with ; or \g.

Your MySQL connection id is 15

Server version:

 5.1.47-log Source distribution

Copyright （c） 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>select * from tmp_loaddata;

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

| id | name |

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

|1 | test |

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

1 row in set （0.00 sec）

mysql>system cat /tmp/2.txt

1,new update

2,new update

3,new update

mysql>

mysql>LOAD DATA INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

ERROR 13 （HY000）:

 Can't get stat of '/tmp/2.txt' （Errcode:

 2）

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

mysql>

mysql>LOAD DATA LOCALINFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

ERROR 1148 （42000）:

 The used command is not allowed with this MySQL version

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

mysql> exit

Bye

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

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

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

LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

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

Query OK,2 rows affected（0.00 sec）

Records:

 3Deleted:

 0Skipped:

 1Warnings:

 0

Bye

mysql>select * from tmp_loaddata;

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

| id | name|

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

|1 | test|

|2 | new update |

|3 | new update |

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

3 rows in set （0.00 sec）

#

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

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

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

LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

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

Query OK,0 rows affected（0.00 sec）

Records:

 3Deleted:

 0Skipped:

 3Warnings:

 0

Bye

mysql>select * from tmp_loaddata;

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

| id | name|

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

|1 | test|

|2 | new update |

|3 | new update |

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

3 rows in set （0.00 sec）

#

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

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

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

LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

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

Query OK,4 rows affected（0.00 sec）

Records:

 3Deleted:

 1Skipped:

 0Warnings:

 0

Bye

mysql> select * from tmp_loaddata;

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

| id | name|

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

|1 | new update |

|2 | new update |

|3 | new update |

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

3 rows in set （0.00 sec）

--EOF--

mysqlloaddatainfile的使用

LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'file_name.txt'

  [REPLACE|IGNORE]

 INTOTABLEtbl_name

  [FIELDS

   [TERMINATEDBY'string']

  [[OPTIONALLY]ENCLOSEDBY'char']

  [ESCAPEDBY'char']

 ]

  [LINES

  [STARTINGBY'string']

 [TERMINATEDBY'string']

 ]

  [IGNOREnumberLINES]

 [（col_name_or_user_var,...）]

  [SETcol_name=expr,...]]

LOADDATAINFILE语句以很高的速度从一个文本文件中读取行到一个表中。

文件名必须是一个文字字符串。

关于INSERT与LOADDATAINFILE的效率和提高LOADDATAINFILE速度的更多信息，参考管理员手册中的加速INSERT语句。

系统变量character_set_database所指出的字符集被用来解释文件中的信息。

SETNAMES和设置character_set_client不会影响输入的解释。

用户也可以使用import实用程序装载数据文件；它通过发送一个LOADDATAINFILE命令到服务器来实现。

--local选项使得import从客户端主机读取数据文件。

如果客户端与服务器支持压缩协议，用户可以指定--compress选项，以在较慢的网络中获得更好的性能。

参考管理员手册中的import—数据导入程序。

如果用户指定关键词LOW_PRIORITY，LOADDATA语句的执行将会被延迟，直到没有其它的客户端正在读取表。

如果一个GSSYS表满足同时插入的条件（即该表在中间有空闲块），并且您对这个GSSYS表指定了CONCURRENT，则当LOADDATA正在执行时，其它线程会从表中重新获取数据。

即使没有其它线程在同时使用本表格，使用本选项也会略微影响LOADDATA的性能。

如果指定了LOCAL关键字，它将对连接的客户端做出解释：

•    如果指定了LOCAL，客户端主机上的客户端组件读取文件并发送到服务器。

可以给出文件的完整路径以确定其精确位置。

如果给出的是相对路径，则文件名是相对于客户端组件启动时所在的目录。

•    如果没有指定LOCAL，文件是位于服务器的主机上，并且直接被服务器读取。

当从服务器主机定位文件时，服务器使用下列规则：

•    如果给定完整的路径，服务器使用该路径名。

•    如果给定一个或多个前置构件的相对路径，服务器以相对服务器的数据目录搜索文件。

•    如果给定没有前置构件的文件名，服务器从当前数据库的数据库目录搜寻文件。

 注意：

 这些规则意味着，一个以'/gsfile.txt'给出的文件是从服务器的数据目录中读取的，然而，以`gsfile.txt'给出的一个文件是从当前数据库的数据目录下读取的。

举例来说，下面的LOADDATA语句从db1数据库目录下读取文件'data.txt'，因为db1是当前数据库，即使语句明确把文件载入到db2数据库中的表里，也会从db1目录中读取：

sqlcli>USEdb1;

sqlcli>LOADDATAINFILE'data.txt'INTOTABLEdb2.gs_table;

 注意：

指定Windows路径名时，使用的是斜线而不是反斜线。

如果要用反斜线，必须双写。

出于安全的原因，当读取位于服务器上的文本文件时，文件必须位于数据库目录下或者可以被所有用户读取。

也就是说，当对服务器上的文件执行LOADDATAINFILE时，用户必须获得FILE权限。

参考管理员手册中的GBase提供的权限。

与服务器直接访问文件相比，使用LOCAL速度稍微慢些，这是因为文件的内容必须通过客户端到服务器的连接传送。

另一方面，对于本地文件，不需要获得FILE权限。

只有服务器和客户端都允许时，LOCAL才可以工作。

例如，如果kernel启动时，--local-infile=0，则LOCAL不能工作。

参考管理员手册中的LOADDATALOCAL的安全问题。

REPLACE和IGNORE关键字处理那些与已存在的主键值重复的输入记录。

如果指定了REPLACE，输入行将会代替已存在的行（也就是说，主索引值相同的行将作为存在的行）。

参考REPLACE语法。

如果指定了IGNORE，与已存在行主键值重复的输入行将被跳过。

如果不指定二者中的任一个，则操作行为将依赖是否指定了LOCAL关键字。

没有指定LOCAL，则如果发现有重复的键值，将产生一个错误，并忽略文本文件的其余部分。

如果指定了LOCAL，则缺省的操作行为将与指定了IGNORE的相同；这是因为，在操作过程中，服务器没有办法终止文件的传送。

如果希望装载操作中忽略外键约束，可以在执行LOADDATA之前执行SETFOREIGN_KEY_CHECKS=0语句。

如果用户在一个空的GsSYS表上使用LOADDATAINFILE，所有非唯一索引会以分批方式被创建（就像REPAIR）。

当有许多索引时，这通常可以使LOADDATAINFILE更快一些。

正常情况下非常快，但也有极端的情况，用户可以通过在装载文件之前使用ALTERTABLE..DISABLEKEYS关闭它们和在装载文件之后使用ALTERTABLE..ENABLEKEYS重建索引，从而加速索引创建。

参考管理员手册中的加速INSERT语句。

LOADDATAINFILE是SELECT...INTOOUTFILE的反操作。

参考SELECT语法。

使用SELECT...INTOOUTFILE将数据从一个数据库写到一个文件中。

使用LOADDATAINFILE读取文件到数据库中。

两个命令的FIELDS和LINES子句的语法是一样的。

两个子句都是可选的，但是如果两个同时被指定，FIELDS子句必须出现在LINES子句之前。

如果用户指定一个FIELDS子句，它的子句（TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY）也是可选的，不过，用户必须至少指定它们中的一个。

如果用户没有指定一个FIELDS子句，缺省时如同使用下列语句：

FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'

如果用户没有指定一个LINES子句，缺省时如同使用下列语句：

LINESTERMINATEDBY'\n'STARTINGBY''

换句话说，当读取输入时，缺省的LOADDATAINFILE表现如下：

•    在换行符处寻找行的边界。

•    不遗漏任何行前缀。

•    在制表符处将行分离成字段。

•    不认为字段由任何引号字符封装。

•    将有“\”开头的定位符、换行符或`\'解释为字段值的一个文字字符。

相反的，当写入输出时，缺省值导致SELECT...INTOOUTFILE表现如下：

•    在字段值间加上制表符。

•    不用任何引号字符封装字段。

•    使用“\”转义出现在字段值中的定位符、换行符或`\'字符实例。

•    在行的结尾处加上换行符。

 注意：

为了写FIELDSESCAPEDBY'\\'，用户必须指定两个反斜线，该值会作为一个反斜线被读入。

 注意：

如果是Windows 系统的文本文件，可能必须使用LINESTERMINATEDBY'\r\n'来读取文件，这是因为Windows系统的特点是使用两个字符作为行终止符。

在某些程序中，在书写文件时，可能使用\r作为行终止符，如写字板。

读取这类文件时，需要用LINESTERMINATEDBY'\r'。

如果所有的将要读取的行都有用户希望忽略的前缀，可以使用LINESSTARTINGBY'prefix_string'来跳过此前缀（和在它前面的任何内容）。

如果一个行没有此前缀，则整个行都被跳过。

注意，prefix_string可能在行的中间！

例如：

sqlcli>LOADDATAINFILE'/tmp/test.txt'

->INTOTABLEtestLINESSTARTINGBY"xxx";

用它读取包含有下面内容的文件：

xxx"Row",1

somethingxxx"Row",2

则可以得到数据（“row”，1）和（“row”，2）。

IGNOREnumberLINES这个选项可以用来忽略文件开头部分的行。

例如，可以用IGNORE1LINES来跳过含有列名的的头一行：

sqlcli>LOADDATAINFILE'/tmp/test.txt'

->INTOTABLEtestIGNORE1LINES;

当用户一前一后地使用SELECT...INTOOUTFILE和LOADDATAINFILE将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。

否则，LOADDATAINFILE将不能正确地解释文件内容。

假设用户使用SELECT...INTOOUTFILE以逗号分隔字段的方式将数据写入到一个文件中：

sqlcli>SELECT*INTOOUTFILE'data.txt'

->    FIELDSTERMINATEDBY','

->    FROMtable2;

为了将由逗号分隔的文件读回时，正确的语句应该是：

sqlcli>LOADDATAINFILE'data.txt'INTOTABLEtable2

->    FIELDSTERMINATEDBY',';

如果用户试图用下面所示的语句读取文件，它将不会工作，因为命令LOADDATAINFILE以定位符区分字段值：

sqlcli>LOADDATAINFILE'data.txt'INTOTABLEtable2

->    FIELDSTERMINATEDBY'\t';

可能的结果是每个输入行将被解释为一个单独的字段。

LOADDATAINFILE也可以被用来读取外部源获得的文件。

例如，dBASE格式的文件，字段以逗号分隔并以双引号包围着。

如果文件中的行以一个换行符终止，那么下面所示的可以说明用户将用来装载文件的字段和行处理选项：

sqlcli>LOADDATA