【转】LOAD DATA INFILE 句法

2024-03-08 13:13:58

如何提高mysql load data infile的速度 测试数据2.5G，共有数据9427567条。用的mysql的large服务器的配置。 load一次需要大概10分钟左右。 建的表用的是MYISAM，调整了几个session的参数值

SET SESSION BULK_INSERT_BUFFER_SIZE=256217728; SET SESSION MYISAM_SORT_BUFFER_SIZE=256217728;

运行结果如下

Query OK, 9427567 rows affected, 1558 warnings (3 min 55.21 sec) Records: 9427567 Deleted: 0 Skipped: 0 Warnings: 0 google到的还可以

set global KEY_BUFFER_SIZE=256217728; alter table tablename disable keys; 禁止索引更新

如何load数据里面带反斜杠(backslash)”\” 的数据 由于如果你没有指定FIELDS子句，则默认值为假设您写下如下语句时的值： FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ‘\\’ 所以，如果你的数据里面有反斜杠(backslash)”\”的时候，数据会出现被截断的问题。出现这种问题，只要写上如下的fields子句即可 FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ”

如何load不同编码的数据 原来用的4.X的mysql，我是select INTO OUTFILE ，只后用iconv,或者其他软件来做。可以参考这里，但是由于这次数据大，用ultraedit等软件打开都要半天。好在新版的mysql可以增加一个新的参数 CHARACTER SET gbk 我的文本数据是GBK的编码，数据表是utf8的，用这种方法测试成功。如何load的时候只insert特定的列 比如表里面有比元数据多的列。可以在load的时候指定要插入的字段名字。

示例的代码如下：

LOAD DATA INFILE '~/data.txt'    INTO TABLE fb0505    CHARACTER SET  gbk   FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY ''  LINES TERMINATED BY '\n' STARTING BY ''   (seller_id,fb_type,fb_date,item_url);

其中表fb0505里面还有一列是id。

Popularity: 20%




LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

LOAD DATAINFILE 语句以非常高的速度从一个文本文件中读取记录行并插入到一个表中。如果LOCAL关键词被指定，文件从客户端主机读取。如果LOCAL没有被指定，文件必须位于服务器上。由于安全性的原因，当读取位于服务器端的文本文件时，文件必须处于数据库目录或可被所有人读取的地方。同时，为了对服务器端的文件使用LOAD DATAINFILE，你必须在服务器主机上有FILE 权限。只有当你没有以--local-infile=0选项启动mysqld，或你没有禁止你的客户端程序支持LOCAL的情况下，LOCAL 才会工作。查看章节，如果你指定关键词LOW_PRIORITY，LOAD DATA语句的执行将会被延迟，直到没有其它的客户端正在读取表。

如果你对一个 MyISAM 表指定关键词CONCURRENT，那么当LOADDATA正在执行时，其它的线程仍可以从表中检索数据。使用这个选项时，如果同时也有其它的线程正在使用表，这当然会有一点影响LOAD DATA的执行性能。

使用 LOCAL将比让服务器直接访问文件要慢一些，因为文件的内容必须从客户端主机传送到服务器主机。而在另一方面，你不再需要有FILE权限用于装载本地文件。

你也可以使用 mysqlimport 实用程序装载数据文件；它通过发送一个LOAD DATAINFILE命令到服务器来动作。--local 选项使得 mysqlimport 从客户端主机读取数据文件。如果客户端与服务器支持压缩协议，你可以指定--compress选项，以在较慢的网络中获得更好的性能。

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

  • 如果给定一个完整的路径，服务器使用该路径名。
  • 如果给定一个有一个或多个前置构件的相对路径，服务器以相对服务器的数据目录搜索文件。
  • 如果给定一个没有前置构件的文件名，服务器从当前数据库的数据库目录搜寻文件。

注意，这些规则意味着，一个以 `./myfile.txt' 给出的文件是从服务器的数据目录中读取的，然而，以`myfile.txt'给出的一个文件是从当前数据库的数据目录下读取的。举例来说，下面的LOAD DATA 语句从 db1 数据库目录下读取文件`data.txt'，因为db1是当前数据库，即使该语句明确地指定读取的文件被放入到db2数据库中的一个表中：

mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

REPLACE 和IGNORE关键词控制对与现有的记录在唯一键值上重复的记录的处理。如果你指定REPLACE，新的记录行将替换有相同唯一键值的现有记录行。如果你指定IGNORE，将跳过与现有的记录行在唯一键值上重复的输入记录行。如果你没有指定任何一个选项，当重复键值出现时，将会发生一个错误，文本文件的剩余部分也将被忽略。

如果你使用 LOCAL关键词从一个本地文件中读取数据，在此操作过程中，服务器没有办法停止文件的传送，因此缺省的处理方式就好像是IGNORE被指定一样。

如果你在一个空的 MyISAM 表上使用LOAD DATA INFILE，所有非唯一索引会以一个分批方式被创建(就像REPAIR)。当有许多索引时，这通常可以使LOAD DATA INFILE 更快一些。

LOAD DATAINFILE 的SELECT ... INTO OUTFILE 的逆操作。使用SELECT ... INTOOUTFILE 将数据从一个数据库写到一个文件中。使用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'

换句话说，当读取输入时，缺省值导致 LOADDATA INFILE 表现如下：

  • 在换行符处寻找行的边界。
  • 在定位符处将行分开放到字段中。
  • 不认为字段由任何引号字符封装。
  • 将有 “\”开头的定位符、换行符或`\' 解释为字段值的一个文字字符。

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

  • 在字段值间加上定位符。
  • 不用任何引号字符封装字段。
  • 使用 “\”转义出现在字段值中的定位符、换行符或`\' 字符实例。
  • 在行的结尾处加上换行符。

注意，为了写 FIELDS ESCAPED BY'\\'，你必须指定两个反斜线，该值会作为一个反斜线被读入。

IGNORE numberLINES 选项可被用于忽略文件开头处的一个列名的头：

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

当你一前一后地使用 SELECT ... INTOOUTFILE 和LOADDATA INFILE将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。假设你使用SELECT ... INTOOUTFILE 以逗号分隔字段的方式将数据写入到一个文件中：

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

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

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

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

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

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

LOAD DATAINFILE 也可以被用来读取从外部来源获得的文件。例如，dBASE格式的文件，字段以逗号分隔并以双引号包围着。如果文件中的行以一个换行符终止，那么下面所示的可以说明你将用来装载文件的字段和行处理选项：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

任何字段和行处理选项都可以指定一个空字符串('')。如果不是空的，FIELDS [OPTIONALLY] ENCLOSED BY 和FIELDS ESCAPEDBY 值必须是一个单个字符。FIELDS TERMINATED BY 和LINES TERMINATED BY值可以超过一个字符。例如，为了写入由回车换行符终止的行，或读取包含这样的行的文件，应该指定一个LINES TERMINATED BY '\r\n'子句。

举例来说，为了读取一个文件到一个 SQL 表中，文件以一行 %% 分隔(开玩笑的)，你可以这样做：

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT
NOT NULL);
LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (joke);

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 和 VARCHAR 字段：

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

注意，在一个字段值中出现的 ENCLOSEDBY 字符，通过用ESCAPED BY 字符作为其前缀对其转义。同时也要注意，如果你指定一个空的ESCAPED BY值，可能会产生不能被LOAD DATAINFILE正确读出的输出文件。例如，如果转义字符为空，上面显示的输出将变成如下显示的输出。请注意第四行的第二个字段，它包含一个逗号跟在一个引号后的两个字符，这(错误的)看起来像是一个字段的终止：

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

对于输入，ENCLOSEDBY 字符如果存在，它将从字段值的尾部被剥离。(不管OPTIONALLY是否被指定，都是这样；对于输入解释，OPTIONALLY 不会影响它。) 由ESCAPED BY 字符领先于ENCLOSED 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 ESCAPEDBY 控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY 字符不是空的，它将被用于做为下列输出字符的前缀：

  • FIELDS ESCAPEDBY 字符
  • FIELDS [OPTIONALLY]ENCLOSED BY 字符
  • FIELDS TERMINATEDBY 和LINESTERMINATED BY 值的第一个字符。
  • ASCII 0(实际上在转义字符后写上 ASCII'0'，而不是一个零值字节)

如果 FIELDS ESCAPEDBY字符为空，没有字符被转义。指定一个空的转义字符可能不是一个好的主意，特别是如果你的数据字段值中包含刚才列表中的任何字符时。

对于输入，如果 FIELDS ESCAPEDBY 字符不为空，该字符的出现将会被剥离，后续的字符在字面上做为字段值的一部分。除了一个转义的“0” 或“N”(即，\0或\N，如果转义字符为`\')。这些序列被解释为ASCII0(一个零值字节) 和 NULL。查看下面的有关NULL 处理的规则。

关于更多的 “\”转义句法信息，查看章节

在某些情况下，字段与行处理相互作用：

  • 如果 LINES TERMINATEDBY 是一个空字符串，FIELDS TERMINATED BY 是非空的，行也用FIELDS TERMINATEDBY 终止。
  • 如果 FIELDS TERMINATEDBY 和FIELDSENCLOSED BY 值都是空的 ('')，一个固定行(无定界符)格式被使用。用固定行格式时，在字段之间不使用分隔符。代替的，列值的写入和读取使用列的“显示”宽度。例如，如果一个列被定义为INT(7)，列的值将使用7 个字符的字段被写入。对于输入，列值通过读取 7 个字符来获得。固定行格式也影响对 NULL值的处理；见下面。注意，如果你正在使用一个多字节的字符集，固定长度格式将不能工作。

NULL值的处理有很多，取决于你所使用的FIELDS 和LINES 选项：

  • 对于缺省的 FIELDS 和LINES 值，输出时，NULL 被写成\N，当读入时，\N 被作为NULL 读入(假设ESCAPED BY 字符为 “\”)。
  • 如果 FIELDS ENCLOSEDBY 是非空的，一个字段包含文字词NULL 的，它的值做为一个NULL 值被读入 (这不同于被FIELDS ENCLOSED BY 包围的词NULL，它是被作为'NULL'读入的)。
  • 如果 FIELDS ESCAPEDBY 是空的，NULL 值被写为词NULL。
  • 用固定行格式时 (它发生于 FIELDSTERMINATED BY 和FIELDS ENCLOSED BY 两者均为空)，NULL 被写为一个空的字符串。注意，当将表中的NULL值和空字符串一起写到文件中时，它们将被混淆，因为它们都是作为空字符串被写入的。如果你在文件时，需要对他们两个进行区分，你不应该使用固定行格式。

一些不能被 LOAD DATAINFILE 支持的情况：

  • 固定尺寸的记录行 (FIELDSTERMINATED BY 和FIELDS ENCLOSED BY 均为空) 和BLOB或 TEXT 列。
  • 如果你指定一个分隔符与另一个相同，或是另一个的前缀，LOAD DATA INFILE 可能会不能正确地解释输入。例如，下列的FIELDS子句将会产生问题： 
    FIELDS TERMINATED BY '"' ENCLOSED BY '"'
  • 如果 FIELDS ESCAPEDBY 为空，一个字段值中包含有FIELDS ENCLOSED BY 或LINES TERMINATED BY 被FIELDS TERMINATEDBY 跟随的值时，将会引起LOAD DATA INFILE过早地停止读取一个字段或一行。这是因为LOAD DATAINFILE 不能够正确地决定字段或行值在哪里结果。

下面的例子将装载 persondata 表的所有列：

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

没有字段列被指定，因而 LOAD DATAINFILE 认为输入行包含表列中所有的字段。使用缺省的FIELDS 和LINES 值。

如果你希望装载表中的某些列，那指定一个字段列表：

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

如果输入文件的字段次序不同于表中列的顺序，你也必须指定一个字段列表。否则 MySQL不知道如何将输入字段与表中的列匹配。

如果一个行有很少的字段，没有输入字段的列将被设置为缺省值。缺省值赋值在章节6.5.3 CREATETABLE 句法 中被描述。

一个空的字段值不同于字段值丢失的解释：

  • 对于字符串类型，列被设置为空字符串。
  • 对于数字类型，列被设置为 0。
  • 对于日期和时间类型，列被设置为适合列类型的“零”值。查看章节 6.2.2 Date 和 Time 类型。

注意，如果在一个 INSERT 或UPDATE语句中明确地将一个空字符串赋给一个字符串、数字或日期或时间类型，你会得到与上面相同的结果。

如果对 TIMESTAMP 列指定一个NULL 值，或者当字段列表被指定时，TIMESTAMP 在字段列表中被遗漏(仅仅第一个TIMESTAMP列被影响)，TIMESTAMP 列会被设置为当前的日期和时间。

如果输入的记录行有太多的字段，多余的字段将被忽略，并增加警告的数目。

LOAD DATAINFILE 认为所有的输入均是字符串，因而，对于ENUM 或SET 列，你不能以INSERT 语句的形式为其设置数字值。所有的ENUM 和SET 必须以字符串指定！

转载于:https://www.cnblogs.com/kiwi/archive/2012/11/29/2794994.html

上一篇：hive的数据类型


下一篇：安装RSAT for win10 1809 later