MySQL load data加载数据

load data 加载数据
load data infile语法如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT},
[, col_name={expr | DEFAULT}] ...]


load data infile语句从文本文件中读取数据加载到数据表中有非常高的效率。为了将数据从表中写入文本文件可以使用select … into outfile,为了将数据从文本文件加载回数据表,使用load data infile语句。对于这两种语句fields与lines子句是相同的。这两个子句是可选的,如果两个子句被指定那么fields必须在lines的前面。

也可以使用mysqlimport工具来加载数据文件,它是通过发送oad data infile语句给服务器来进行操作。mysqlimport的–local选项会导致mysqlimport从客户端主机上读取数据文件。如果客户端和服务器支持压缩协议使用–compress选项将得到更好的性能。

文件名必须以字面值字符串的形式给出。在Windows上,在路径名中指定反斜杠为正斜杠或双反斜杠。character_set_filesystem系统变量控制文件名的解释。

LOAD DATA支持使用带有一个或多个以逗号分隔的分区、子分区或是同时指定分区与子分区的名称列表的partition选项进行显式分区选择。使用此选项时,如果文件中的任何行不能插入到列表中指定的任何分区或子分区中,则语句会失败,错误为Found a row not matching the给定的分区集。

对于使用MyISAM等使用表锁的存储引擎的分区表,LOAD DATA不能删除任何分区锁。这不适用于使用行级锁的存储引擎的表,比如InnoDB

服务器使用由character_set_database系统变量指示的字符集来解释文件中的信息。SET NAMES和character_set_client的设置不会影响输入的解释。如果输入文件的内容使用与默认不同的字符集,通常最好使用character set子句指定文件的字符集。二进制字符集指定不转换。

LOAD DATA INFILE将文件中的所有字段解释为具有相同的字符集,而不考虑字段值加载到的列的数据类型。为了正确解释文件内容,您必须确保它是用正确的字符集编写的。例如,如果你写一个数据文件使用mysqldump -T或者在mysql中发出SELECT…INTO OUTFILE语句,一定要使用–default-character-set选项输出时所使用的字符集在当文件被LOAD DATA INFILE加载时将要使用。

如果使用LOW_PRIORITY, LOAD DATA语句的执行将被延迟,直到没有其他客户端从表中读取数据。这只会影响仅使用表级锁的存储引擎(如MyISAM、memory和merge)。

如果MyISAM表指定CONCURRENT来满足并发插入条件的(即,它中间不包含空闲块),那么在执行LOAD DATA时,其他线程可以从表中检索数据。即使没有其他线程同时使用这个表,这个选项也会略微影响LOAD DATA的性能。

使用基于行的复制,无论MySQL版本如何,都可以并发复制。使用基于语句的复制,在MySQL 5.5.1之前不会执行并发复制(见Bug #34628)。

LOCAL关键字影响文件的预期位置和错误处理,如后面所述。只有当服务器和客户机都配置为允许LOCAL时,LOCAL才能工作。例如,如果mysqld在启动时禁用了local_infile系统变量,那么LOCAL就不起作用

local关键字的影响期望在文件中的何处被找到:
.如果local关键字被指定,通过客户端主机上的客户端程序来读取文件并发送给服务器。这个文件可以被指定完全路径名来指定精确位置。如果指定相对路径名,这将会解析成启动客户端程序目录的相对路径。

当使用load data local时,在服务器的临时目录中会创建一个文件副本。这不是由tmpdir或slave_load_tmpdir的值决定的目录,而是操作系统的临时目录,并且在MySQL服务器中不能配置(通常系统临时目录在linux系统中是/tmp,在windows中是c:\windows\temp)。如果临时目录没有足够的空间可能会造成load data local语句失败。

.如果local关键字没有指定,那么数据文件必须存储在服务器上并且服务器可以直接读取。服务器使用以下规则来定位文件:

.如果文件名是绝对路径名,服务器将按照给定的方式使用它。

.如果文件名是包含一个或多个前导组件的相对路径名,服务器将相对于服务器的数据目录搜索该文件。

.如果文件名中没有给出前导组件,服务器将在默认数据库的数据库目录中查找该文件。

在非local情况下,这些规则意味着从服务器的数据目录中读取名为./myfile.txt的文件,而从默认数据库的数据库目录中读取名为myfile.txt的文件。例如,如果db1是默认数据库,那么下面的LOAD DATA语句将从db1的数据库目录中读取文件data.txt,即使该语句显式地将文件加载到db2数据库中的一个表中:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

非local加载操作读取位于服务器上的文本文件。出于安全原因,这些操作要求您拥有FILE特权。非local加载操作受secure_file_priv系统变量设置的影响。如果变量值是非空目录名,则要加载的文件必须位于该目录中。如果变量值为空(不安全),则该文件只需要由服务器读取。

使用LOCAL比让服务器直接访问文件要慢一些,因为文件的内容必须通过客户机连接发送到服务器。另一方面,您不需要FILE特权来加载本地文件。

local也会影响错误的处理:
.使用load data infile,数据解释和重复键错误终止操作。
.使用load data local infile 数据解释和重复键错误成为警告,操作继续进行,因为服务器没有办法在操作过程中停止文件传输。对于重复键错误,这就相当于指定了IGNORE。

REPLACE和IGNORE关键字控制对在唯一键值上存在与现有行有重复的输入行的处理:
.如果指定replace,输入行将替换存在行。换句话说,与现有行的主键或唯一索引有相同的值。

.如果指定ignore,与现有行的唯一键值重复的行会被丢弃。

.如果没有指定这些选项,那么依据是否指定了local来决定。在没有指定local时,当找到重复键值时会报错且文件中剩余的内容会被忽略。当指定local时,默认的行为与指定了ignore是一样的,这是因为服务器不能阻止正在执行的文件传输操作。

为了在加载操作时忽略外键约束,可以在执行load data之前执行set foreign_key_checks=0语句。

如果对一个MyISAM空表执行load data infile操作,那么所有非唯一索引将在单独的一个批处理中创建(类似于repair table)。通常来说,当有许多索引的情况下这将使load data infile操作更快。在一些极端的情况下,在加载数据文件之前可以执行alter table … disable keys来禁用索引,在加载数据文件之后执行alter table .. enable kyes来重建索引这要比创建索引效率更高。

对于load data infile和select … into outfile语句来说fiels和lines子句的语法是相同的。这些子句是可选项,但如果都被指定的话fields必须在lines的前面。

如果指定fields子句,它的每一个子句(terminated by,[optionally] enclosed by和escaped by)也是可选项,除非你必须至少指定一个。

如果没有指定fields或lines子句,默认就和下面所写的一样:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

反斜杠是SQL语句中字符串中的MySQL转义字符,因此要指定一个字面值的反斜杠,您必须指定两个反斜杠才能将值解释为单个反斜杠。转义序列’\t’和’\n’分别指定制表符和换行符

换句话说,在读取输入时,默认值导致LOAD DATA INFILE执行如下操作:
.在换行处查看行边界。
.不会跳过任何行前缀
.在制表符中将行拆分为字段
.不要期望字段用引号括起来
.将前面有转义字符\的字符解释为转义序列。例如,“\t”、“\n”、“\\”分别表示制表符、换行符、反斜杠

相反,默认值会导致select … into outfile执行如下操作
.在字段之间写制表符
.要用引号括起字段
.使用\转义字段值中出现的制表符、换行符或\实例
.在行尾写换行

如果您已经在Windows系统上生成了文本文件,您可能必须使用LINES TERMINATED BY ‘\r\n’来正确读取该文件,因为Windows程序通常使用两个字符作为行结束符。有些程序,如写字板,在写文件时可能使用\r作为行结束符。要读取这样的文件,请使用以’\r’结尾的行。

如果您想要读入的所有行都有一个您想要忽略的公共前缀,您可以使用LINES STARTING BY ‘prefix_string’来跳过该前缀和它之前的任何内容。如果某一行不包含前缀,则跳过整行。假设您发出以下语句:

load data infile '/tmp/test.txt' into table test fields terminated by ',' lines starting by 'xxx';

如果数据文件内容类似如下:

xxx"abc",1
something xxx"def",2
"ghi",3

上面数据文件执行加载后行数将是(“abc”,1)和(“def”,2)。第三行将会被跳过因为它不包含前缀。

ignore number lines选项可以用来忽略文件开头的行。例如,可以使用ignore 1 lines来跳过包含列名的首标题行:

load data infile '/tmp/test.txt' into table test ignore 1 lines;

当你使用SELECT…INTO OUTFILE与LOAD DATA INFILE一起将数据从数据库写入文件,然后稍后将文件读回数据库,这两个语句的字段和行处理选项必须匹配。否则,LOAD DATA INFILE将不能正确解释文件的内容。假设你使用SELECT…写入一个以逗号分隔的字段的文件:

select * into outfile 'data.txt' fields terminated by ',' from table2;

要将逗号分隔的文件读入,正确的语句应该是:

load data infile 'data.txt' into table table2 fields terminated by ',';

如果您尝试使用下面所示的语句读取文件,则不会工作,因为它指示LOAD DATA INFILE查找字段之间的制表符:

load data infile 'data.txt' into table table2 fields terminated by '\t';

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

LOAD DATA INFILE可用于读取从外部来源获得的文件。例如,许多程序可以以逗号分隔值(CSV)格式导出数据,例如,行中有用逗号分隔的字段,并用双引号括起来,并使用列名的首行。如果这样一个文件中的行以回车/换行对结束,这里显示的语句说明了用于加载文件的字段和行处理选项。

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

如果输入值不一定用引号括起来,请在enclosed BY关键字前使用optionally。

任何字段处理或行处理选项都可以指定一个空字符串(“)。如果不为空,则FIELDS[OPTIONALLY] ENCLOSEDBY和FIELDS ESCAPED BY值必须是单个字符。FIELDS TERMINATED BY、LINES started BY和LINES TERMINATED BY值可以多于一个字符。例如,要写以回车/换行对结尾的行,或读取包含此类行的文件,请指定lines terminated by ‘\r\n’子句。

要读取包含由%%作为行分隔符的的文件,您可以这样做

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);
< ?pre>
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 by字符开头,则该字符的实例只有在后跟字段或行terminated 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字符不为空,则剥离出现的该字符,并按照字面意思将下面的字符作为字段值的一部分。一些双字符序列是例外,其中第一个字符是转义字符。这些序列如下表所示(转义字符使用\)。本节稍后将描述NULL处理规则。

如果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。指定空转义字符可能不是一个好主意,特别是如果数据中的字段值包含刚才给出的列表中的任何字符。

在某些情况下,字段和行处理选项相互作用:
.如果LINES TERMINATED BY为空字符串而FIELDS TERMINATED BY为非空字符串,则行也会以FIELDS TERMINATED BY结束。

.如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都为空(“),则使用固定行(非分隔)格式。使用固定行格式,字段之间不使用分隔符(但仍然可以使用行结束符)。相反,读写列值时使用足够宽的字段宽度来容纳字段中的所有值。对于TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT,无论声明的显示宽度是多少,字段宽度分别为4、6、8、11和20。

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值和空字符串在写入文件时无法区分,因为两者都是作为空字符串写入的。如果需要在重新读入文件时区分这两者,那么不应该使用固定行格式。

尝试将NULL加载到NOT NULL列会导致为列的数据类型分配隐式默认值,并出现警告,或者在严格的SQL模式下出现错误。

不被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表的所有列:

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

默认情况下,当LOAD DATA INFILE语句末尾没有提供字段列表时,输入行将包含每个表的所有字段。如果只想加载表的部分列,请指定字段列表:

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

如果输入文件中字段的顺序与表中列的顺序不同,还必须指定字段列表。否则,MySQL无法判断如何将输入字段与表列匹配。

列列表可以包含列名或用户变量。对于用户变量,SET子句允许您在将结果分配给列之前对其值执行转换.

SET子句中的用户变量有几种用法。下面的示例将第一个输入列直接用于t1.column1的值,并将第二个输入列赋值给一个用户变量,该变量在用于t1.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触发器,则分别在插入行之前或之后激活它们。

如果一个输入行有太多的字段,额外的字段将被忽略,警告的数量将增加

如果输入行字段太少,则将缺少输入字段的表列设置为默认值。

对空字段值的解释与缺失字段不同:
.对于字符串类型,列被设置为空字符串
.对于数字类型,列被设置为0
.对于日期和时间类型,该列被设置为该类型的适当零值

如果在INSERT或UPDATE语句中显式地将空字符串赋给字符串、数字类型或日期或时间类型,则会得到相同的值

如果SQL模式被设置为限制值,那么空字段值或不正确字段值的处理与刚才描述的不同。例如,如果sql_mode设置为traditional,对于数字列转换空值或值(如’x’)将导致错误,而不是转换为0。(对于LOCAL或IGNORE,即使使用限制性sql_mode值,也会出现警告而不是错误,并且使用与非限制性SQL模式相同的最接近值行为插入行。这是因为服务器没有办法在操作过程中停止文件的传输。)

只有TIMESTAMP列列值为NULL和列没有声明为允许NULL值或者如果TIMESTAMP列的默认值是当前的时间戳,并且在指定字段列表时从字段列表中省略它时,TIMESTAMP列会被设置为当前日期和时间。

LOAD DATA INFILE将所有输入视为字符串,所以不能像INSERT语句那样对ENUM或SET列使用数值。所有的ENUM和SET值必须指定为字符串。BIT值不能使用二进制表示法直接加载(例如,b’011010′)。为了解决这个问题,使用SET子句去掉前导b’和末尾b’,并执行base-2到base-10的转换,以便MySQL正确地将值加载到BIT列中:

[mysql@localhost ~]$ cat /var/lib/mysql/bit_test.txt
b'10'
b'1111111'

mysql> create table bit_test(b bit(10));
Query OK, 0 rows affected (0.10 sec)


mysql> load data infile '/var/lib/mysql/bit_test.txt'
    -> into table bit_test(@var1)
    -> set b=cast(conv(mid(@var1,3,length(@var1)-3),2,10) as unsigned);
Query OK, 2 rows affected (0.15 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select bin(b+0) from bit_test;
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

对于0b二进制表示法中的BIT值(例如0b011010),使用这个SET子句来去掉前导的0b:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

在Unix上,如果需要LOAD DATA从管道读取数据,可以使用以下技术(示例将/目录的清单加载到表db1.t1中):

mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

在这里,您必须在单独的终端上运行生成要加载的数据的命令和mysql命令,或者在后台运行数据生成过程(如前面的示例所示)。如果您不这样做,管道将阻塞,直到数据被mysql进程读取。

当LOAD DATA INFILE语句完成时,它将返回如下格式的信息字符串

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.