bitsCN.com
LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词,从客户主机读文件。如果LOCAL没指定,文件必须位于服务器上。(LOCAL在MySQL3.22.6或以后版本中可用。)
为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用LOAD DATA INFILE,在服务器主机上你必须有file的权限。
头一回用load data infile,以为只是把插入语句写到一个文件里,然后用load data infile把文件传入数据库就OK了,于是生成了一个内容类似同n句 insert into table_name (`id`,`name`,`content`) values (1,"a","abc"),(2,"b","abc"),(3,"c","abc");组成的.sql文件。然后在PHP中执行 LOAD DATA INFILE 'file_name.sql' INTO TABLE table_name;发现总是执行出错,真晕,不知道怎么回事,只好去再详细地读一下mysql的应用手册:
如果你指定一个FIELDS子句,它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可选的,除了你必须至少指定他们之一。 如果你不指定一个FIELDS子句,缺省值与如果你这样写的相同: FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//' 如果你不指定一个LINES子句,缺省值与如果你这样写的相同: LINES TERMINATED BY '/n' 换句话说,缺省值导致读取输入时,LOAD DATA INFILE表现如下: 在换行符处寻找行边界 在定位符处将行分进字段 不要期望字段由任何引号字符封装 将由“/”开头的定位符、换行符或“/”解释是字段值的部分字面字符
才知道原来我的sql里的内容并不是按照 load data infile的缺省设置来写的, 例如缺省设置下,每一句插入语句里的字段是由制表符隔开且内容不以任何引号封装(也就是括起来)的,但我的是由逗号隔开且有双引号括起来的,难怪会执行出错。 于是,将sql执行语句写成mysql_query('load data local infile "file_name.sql" into table `table_name` FIELDS TERMINATED BY "," ENCLOSED BY /'"/' ESCAPED BY "///" LINES TERMINATED BY "/n" STARTING BY ""'); 这会算是执行成功了,成功地往指定的数据表里插入了很多条记录,可是又发现了一个问题,插入的数据乱七八糟的,字段与要插入的内容不符合我的要求,而且有好多是把字段名给插入到字段里面去了,唉,只好再回头去又读了一遍mysql中的load data infile用法,终于搞明白了,原来file_name.sql里面只需要按一定格式把内容写进去就行了,不是把整个sql执行语句都写进去的,真笨!^_^ 于是,把内容换成了 1,"a","abc" 2,"b","abc" 3,"c","abc" 而且内容要与数据表里的字段从数量上和顺序上都要严格对应 在本地服务器(我用的是WINDOW主机)上测试了一下,OK,操作成功! 然后把程序传到网络服务器上(LINUX主机),一执行,提示: Can't get stat of ……'' (Errcode: 13) 开始还以为是sql文件权限或者是mysql的root用户权限的问题,后来想不对呀,mysql的root用户是超级用户,肯定有权限的,那问题就出在sql文件的权限上,后来把sql的权限改成777,执行操作后还是不行。 网上搜索了一下,有说把文件放在/var/lib/mysql里就行了,一试,果真可以,可是我又不可能在PHP网页中把sql文件生成放到/var/lib/mysql下,费尽了心思,最后终于在网上搜索到一个解决办法:
使用LOCAL将比让服务器直接存取文件慢些,因为文件的内容必须从客户主机传送到服务器主机。在另一方面,你不需要file权限装载本地文件。 你也可以使用mysqlimport实用程序装载数据文件;它由发送一个LOAD DATA INFILE命令到服务器来运作。 --local选项使得mysqlimport从客户主机上读取数据。如果客户和服务器支持压缩协议,你能指定--compress在较慢的网络上获得更好的性能。
其实办法简单得很,那就是把load data infile写成load data local infile 就OK啦。
以下是内容补充:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...]]
LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串。
关于INSERT 与LOAD DATA INFILE 的效率和提高LOAD DATA INFILE 速度的更多信息,参考管理员手册中的加速INSERT 语句 。
如果希望装载操作中忽略外键约束,可以在执行LOAD DATA 之前执行SET FOREIGN_KEY_CHECKS=0 语句。 如果用户在一个空的GsSYS 表上使用LOAD DATA INFILE,所有非唯一索引会以分批方式被创建(就像REPAIR)。当有许多索引时,这通常可以使LOAD DATA INFILE 更快一些。正常情况下非常快,但也有极端的情况,用户可以通过在装载文件之前使用ALTER TABLE .. DISABLE KEYS 关闭它们和在装载文件之后使用ALTER TABLE .. ENABLE KEYS 重建索引,从而加速索引创建。参考管理员手册中的加速INSERT 语句 。
LOAD DATA INFILE 是SELECT ... INTO OUTFILE 的反操作。参考SELECT 语法 。 使用SELECT ... INTO OUTFILE 将数据从一个数据库写到一个文件中。使用LOAD DATA INFILE 读取文件到数据库中。两个命令的FIELDS 和LINES 子句的语法是一样的。两个子句都是可选的,但是如果两个同时被指定,FIELDS 子句必须出现在LINES 子句之前。 如果用户指定一个FIELDS 子句,它的子句 (TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和ESCAPED BY) 也是可选的,不过,用户必须至少指定它们中的一个。 如果用户没有指定一个FIELDS 子句,缺省时如同使用下列语句: FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//' 如果用户没有指定一个LINES 子句,缺省时如同使用下列语句: LINES TERMINATED BY '/n' STARTING BY '' 换句话说,当读取输入时,缺省的LOAD DATA INFILE 表现如下: • 在换行符处寻找行的边界。 • 不遗漏任何行前缀。 • 在制表符处将行分离成字段。 • 不认为字段由任何引号字符封装。 • 将有 “/” 开头的定位符、换行符或`/' 解释为字段值的一个文字字符。 相反的,当写入输出时,缺省值导致SELECT ... INTO OUTFILE 表现如下: • 在字段值间加上制表符。 • 不用任何引号字符封装字段。 • 使用 “/” 转义出现在字段值中的定位符、换行符或`/' 字符实例。 • 在行的结尾处加上换行符。 注意:
为了写FIELDS ESCAPED BY '//',用户必须指定两个反斜线,该值会作为一个反斜线被读入。 注意:
如果是Windows 系统的文本文件,可能必须使用LINES TERMINATED BY '/r/n' 来读取文件,这是因为Windows 系统的特点是使用两个字符作为行终止符。在某些程序中,在书写文件时,可能使用/r 作为行终止符,如写字板。读取这类文件时,需要用LINES TERMINATED BY '/r'。 如果所有的将要读取的行都有用户希望忽略的前缀,可以使用LINES STARTING BY 'prefix_string' 来跳过此前缀(和在它前面的任何内容)。如果一个行没有此前缀,则整个行都被跳过。注意,prefix_string 可能在行的中间! 例如: sqlcli> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test LINES STARTING BY "xxx";
IGNORE number LINES 这个选项可以用来忽略文件开头部分的行。例如,可以用IGNORE 1 LINES 来跳过含有列名的的头一行: sqlcli> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test IGNORE 1 LINES; 当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中,然后再从文件中将它读入数据库中时,两个命令的字段和行处理选项必须匹配。否则,LOAD DATA INFILE 将不能正确地解释文件内容。假设用户使用SELECT ... INTO OUTFILE 以逗号分隔字段的方式将数据写入到一个文件中: sqlcli> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY ',' -> FROM table2; 为了将由逗号分隔的文件读回时,正确的语句应该是: sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY ','; 如果用户试图用下面所示的语句读取文件,它将不会工作,因为命令LOAD DATA INFILE 以定位符区分字段值: sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY '/t'; 可能的结果是每个输入行将被解释为一个单独的字段。 LOAD DATA INFILE 也可以被用来读取外部源获得的文件。例如,dBASE 格式的文件,字段以逗号分隔并以双引号包围着。如果文件中的行以一个换行符终止,那么下面所示的可以说明用户将用来装载文件的字段和行处理选项: sqlcli> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '/n';
任何字段和行处理选项都可以指定一个空字符串('')。如果不是空的,FIELDS [OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPED BY 值必须是一个单个字符。FIELDS TERMINATED BY 和LINES TERMINATED BY 值可以超过一个字符。例如,为了写入由回车换行符终止的行,或读取包含这样的行的文件,应该指定一个LINES TERMINATED BY '/r/n' 子句。 FIELDS [OPTIONALLY] ENCLOSED BY 控制字段的引用。对于输出(SELECT ... INTO OUTFILE),如果用户省略单词OPTIONALLY,所有的字段被ENCLOSED BY 字符包围。这样的一个输出文件(以一个逗号作为字段分界符)示例如下: "1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a /" quote","102.20" "4","a string containing a /", quote and comma","102.20"
如果用户指定OPTIONALLY,ENCLOSED BY 字符仅被用于包装诸如含有字符串类型的字段(诸如CHAR,BINARY,TEXT 或ENUM): 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a /" quote",102.20 4,"a string containing a /", quote and comma",102.20 注意:
在一个字段值中出现的ENCLOSED BY 字符,通过用ESCAPED BY 字符作为其前缀对其转义。同时也要注意,如果用户指定一个空的ESCAPED BY 值,可能会产生不能被LOAD DATA INFILE 正确读出的输出文件。例如,如果转义字符为空,上面显示的输出将变成如下显示的输出。请注意第四行的第二个字段,它包含一个跟在一个引号后的逗号,看起来 像是一个字段的终止:
1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20 对于输入,ENCLOSED BY 字符如果存在,它将从字段值的尾部被剥离。(不管OPTIONALLY 是否被指定,都是这样;对于输入解释,OPTIONALLY 不会影响它。)如果在ENCLOSED BY 字符前存在ESCAPED BY 字符,那么它将被解释为当前字段值的一部分。
如果字段以ENCLOSED 字符开始,只要后面紧跟着字段或行TERMINATED BY 序列 ,这个字符实例就被认为用来终止一个字段值。为了明确,如果在字段中要使用ENCLOSED BY 字符,可以重复写两遍该字符,那么它们会被解释成单个ENCLOSED BY字符处理。例如,如果指定ENCLOSED BY '"',引号将做如下处理: "The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY 控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY 字符不是空的,它将被用于做为下列输出字符的前缀:
• FIELDS ESCAPED BY 字符 • FIELDS [OPTIONALLY] ENCLOSED BY 字符 。 • FIELDS TERMINATED BY 和LINES TERMINATED BY 值的第一个字符。 • ASCII 0 (实际上在转义字符后写上ASCII '0',而不是一个零值字节)。 如果FIELDS ESCAPED BY 字符为空,那么将没有字符被转义并且NULL 值仍输出为NULL,而不是/N。指定一个空的转义字符可能不是一个好的方法,特别是用户的数据字段值中包含刚才列表中的任何字符时。 对于输入值,如果FIELDS ESCAPED BY 字符不是空字符,则出现这种字符时会被剥离,然后以下字符被作为字段值的一部分。例外情况是,被转义的‘0'或‘N'(例如,/0 或/N,此时转义符为‘/')。这些序列被理解为ASCII NUL(一个零值字节)和NULL。用于NULL 处理的规则在本节的后部进行说明。 关于更多的 “/” 转义语法信息,查看文字值 。 在某些情况下,字段与行处理相互作用: • 如果LINES TERMINATED BY 是一个空字符串,FIELDS TERMINATED BY 是非空的,则各行以FIELDS TERMINATED BY作为结尾。 • 如果FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 值都是空的(''),那么一个固定行(无定界符) 格式将被使用。用固定行格式时,在字段之间不使用分隔符(但是用户仍然有一个行终结符)。列值的写入和读取使用列的“显示”宽度。例如,如果一个列被定义 为INT(7),列的值将使用7 个字符的字段被写入。对于输入,列值通过读取7 个字符来获得。 LINES TERMINATED BY 仍然用于分离行。如果一行没有包含所有的字段,那么列的剩余部分被设置为它们的默认值。如果用户没有一个行终结符,用户应该设置它为''。在这种情况下,文本文件必须包含每行的所有的字段。 固定行格式也影响对NULL 值的处理;见下面。注意,如果用户正在使用一个多字节的字符集,固定长度格式将不能工作。 NULL 值的处理有很多,取决于用户所使用的FIELDS 和LINES 选项: • 对于缺省的FIELDS 和LINES 值,输出时,NULL 被写成/N,当读入时,/N 被作为NULL 读入(假设ESCAPED BY 字符为 “/”)。 • 如果FIELDS ENCLOSED BY 不是空值,则包含以文字词语NULL 为值的字段被作为NULL 值读取。这与被FIELDS ENCLOSED BY 字符包围的词语NULL不同。该词语被作为字符串'NULL'读取。 • 如果FIELDS ESCAPED BY 是空的,NULL 值被写为词NULL。 • 采用固定行格式时(当FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均为空值时采用),NULL 被作为一个空字符串写入。注意,这会导致在被写入文件时,表中的NULL 值和空字符串均无法辨别,这是因为两者都被作为空字符串写入。如果用户需要在读取文件并返回时能够分辨两者,则用户不应使用固定行格式。 一些不能被LOAD DATA INFILE 支持的情况: • 固定尺寸的记录行(FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均为空)和BLOB 或TEXT 列。 • 如果用户指定一个分隔符与另一个相同,或是另一个的前缀,LOAD DATA INFILE 可能会不能正确地解释输入。例如,下列的FIELDS 子句将会产生问题: FIELDS TERMINATED BY '"' ENCLOSED BY '"' • • 如果FIELDS ESCAPED BY 为空,一个字段值中包含有FIELDS ENCLOSED BY 或LINES TERMINATED BY 后面紧跟着FIELDS TERMINATED BY 的值时,将会引起LOAD DATA INFILE 过早地停止读取一个字段或一行。这是因为LOAD DATA INFILE 不知道字段或行值在哪里结束。 下面的例子将装载persondata 表的所有列: sqlcli> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata; 默认情况下,当LOAD DATA INFILE 语句后没有提供字段列时,那么LOAD DATA INFILE 认为输入行包含表列中所有的字段。如果用户希望装载表中的某些列,那指定一个字段列表: sqlcli> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...); 如果输入文件的字段顺序不同于表中列的顺序,用户也必须指定一个字段列表。否则GBase 不知道如何将输入字段与表中的列匹配。 Column 列表可以包含列名或者用户变量,并且支持SET 子句。这使得能用输入值给用户变量赋值, 并在赋予列的结果之前对这些值进行变换。 SET 子句中的用户变量有多种用途。下面的例子将数据文件的第一列直接作为t1.column1 的值,并将第二列赋予一个用户变量,此变量在作为t2.column2 的值之前进行一个除法操作 LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100; SET 子句可以提供不是来源于输入文件的值。下面的语句将column3 设置为当前的日期和时间: LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP; 通过将一个输入值赋予一个用户变量,可以将此输入值丢弃,并且不将此值赋予表的一个列: LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
使用列/变量列表和SET子句要受到一下限制:
• SET 子句中的赋值列名应当只能在赋值操作符的左边。 • 在SET 赋值语句中,可以使用子查询。此查询返回一个将被赋予列的值,它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。 • 对于列/变量列表或SET 子句,由于使用IGNORE 子句而被忽略的行就不会被处理。 • 由于用户变量没有显示宽度,当导入的数据是行固定格式时,将不能使用用户变量。 当处理输入行时,LOAD DATA 将行分为域,如果提供了列/变量列表和SET 子句,将按照它去使用这些值。然后得到的行就插入到表中。如果此表有BEFORE INSERT 或AFTER INSERT 触发器,则插入行前后将分别激活它们。