mysql导⼊⽂本数据LOADDATAINFILE语法
mysql导⼊⽂本数据LOAD DATA INFILE语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ''
[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,...]]
常⽤参数含义：
TERMINATED BY character //描述字段的分隔符，默认情况下是tab字符（\\t）
ENCLOSED BY character //描述的是字段的括起字符，⽐⽅以双引号括起每⼀个字段
ESCAPED BY character //描述的转义字符，默认的是反些杠（backslash：\\ ）
实例：
mysql>USE dbname;
mysql>LOAD DATA INFILE "/remote/server/" INTO TABLE table_name;
//LOCAL参数表⽰⽂件是本地的⽂件，服务器是远程已经登陆的服务器
mysql>LOAD DATA LOCAL INFILE "/local/server/" INTO TABLE table_name;
//设置插⼊语句的优先级（LOW_PRIORITY 低优先级），LOAD DATA语句的执⾏将会被延迟，直到没有其它的客户端读取表
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/" INTO TABLE table_name;
//插⼊数据的时候，忽略⽂件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/" IGNORE INTO TABLE table_name;
//插⼊数据的时候，替代⽂件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "/remote/server/" REPLACE INTO TABLE table_name;
//字段以逗号分割，字段内容⽤双引号标记开始和结束
mysql>LOAD DATA INFILE "/remote/server/" INTO INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"';
//按指定的列把⽂件导⼊到数据库中
mysql>LOAD DATA INFILE "/remote/server/" INTO TABLE table_name (column1, column2, column3);
其他知识点：
1：关于INSERT与LOAD DATA INFILE的效率和提⾼LOAD DATA INFILE速度的更多信息，参考管理员⼿册中的加速INSERT语句
2：系统变量character_set_database所指出的字符集被⽤来解释⽂件中的信息。SET NAMES和设置character_set_client不会影响输⼊的解释
3：⽤户也可以使⽤mysqlimport命令装载数据⽂件，它通过发送⼀个LOAD DATA INFILE 命令到服务器来实现
--local选项使得mysqlimport从客户端主机读取数据⽂件
--compress启⽤压缩，在较慢的⽹络中获得更好的性能，需要客户端与服务器都⽀持压缩协议
4：⽂件路径尽量使⽤完整路径，并注意⽂件权限
5：如果kernel启动时，--local-infile=0，则LOCAL不能⼯作，参考管理员⼿册中的LOAD DATA LOCAL的安全问题
6：REPLACE和IGNORE关键字处理那些与已存在的主键值重复的输⼊记录
如果指定了REPLACE，输⼊⾏将会代替已存在的⾏（也就是说，主索引值相同的⾏将作为存在的⾏）。参考REPLACE 语法
如果指定了IGNORE，与已存在⾏主键值重复的输⼊⾏将被跳过。
如果不指定⼆者中的任⼀个，则操作⾏为将依赖是否指定了LOCAL 关键字。
没有指定LOCAL，则如果发现有重复的键值，将产⽣⼀个错误，并忽略⽂本⽂件的其余部分。
如果指定了LOCAL，则缺省的操作⾏为将与指定了IGNORE 的相同；这是因为，在操作过程中，服务器没有办法终⽌⽂件的传送。
如果希望装载操作中忽略外键约束，可以在执⾏LOAD DATA 之前执⾏SET FOREIGN_KEY_CHECKS=0 语句。
7：加速索引创建：如果⽤户在⼀个空的GsSYS 表上使⽤LOAD DATA INFILE，所有⾮唯⼀索引会以分批⽅式被创建（就像REPAIR）。当有许多索引时，这通常可以使LOAD DATA INFILE 更快⼀些。正常情况下⾮常快，但也有极端的情况，⽤户可以通过在装载⽂件之前使⽤ALTER TABLE .. DISABLE KEYS 关闭它们和在装载⽂件之后使⽤ALTER TABLE .. ENABLE KEYS 重建索引，从⽽加速索引创建。参考管理员⼿册中的加速INSERT 语句 。
8：LOAD DATA INFILE 是SELECT ... INTO OUTFILE 的反操作。参考SELECT 语法。使⽤SELECT ... INTO OUTFILE 将数据从⼀个数据库写到⼀个⽂件中。使⽤LOAD DATA INFILE 读取⽂件到数据库中。
9：FIELDS 和LINES ⼦句的语法是⼀样的。两个⼦句都是可选的，但是如果两个同时被指定，FIELDS ⼦句必须出现在LINES ⼦句之前。
10：如果⽤户指定⼀个FIELDS ⼦句，它的⼦句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和ESCAPED BY) 也是可选的，不过，⽤户必须⾄少指定它们中的⼀个。
11：如果⽤户没有指定⼀个FIELDS ⼦句，缺省时如同使⽤下列语句：
mysql>... FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
12：如果⽤户没有指定⼀个LINES ⼦句，缺省时如同使⽤下列语句：
mysql>... LINES TERMINATED BY '\n' STARTING BY ''
13：当读取输⼊时，缺省的LOAD DATA INFILE 表现如下：
在换⾏符处寻⾏的边界。
不遗漏任何⾏前缀。
在制表符处将⾏分离成字段。
不认为字段由任何引号字符封装。
将有 “\” 开头的定位符、换⾏符或`\' 解释为字段值的⼀个⽂字字符。
14：当写⼊输出时，缺省值导致SELECT ... INTO OUTFILE 表现如下：
在字段值间加上制表符。
不⽤任何引号字符封装字段。
使⽤ “\” 转义出现在字段值中的定位符、换⾏符或`\' 字符实例。
在⾏的结尾处加上换⾏符。
注意：
为了写FIELDS ESCAPED BY '\\'，⽤户必须指定两个反斜线，该值会作为⼀个反斜线被读⼊。
如果是Windows 系统的⽂本⽂件，可能必须使⽤LINES TERMINATED BY '\r\n' 来读取⽂件。
15：忽略⾏前缀可以跳过此前缀（和在它前⾯的任何内容）。如果⼀个⾏没有此前缀，则整个⾏都被跳过
mysql>... LINES STARTING BY 'prefix_string'
注意：prefix_string可以出现在⾏的中间
例如：
mysql>... LOAD DATA INFILE '/' INTO TABLE test LINES STARTING BY "abcdefg";
#cat /
"Row",0
abcdefg"Row",1
something abcdefg"Row",2
得到数据：（“row”，1）（“row”，2）
16：忽略⽂件开头部分前N⾏
mysql>... IGNORE number LINES
17：空字符串（''）
任何字段和⾏处理选项都可以指定⼀个空字符串（''）。如果不是空的，FIELDS [OPTIONALLY] ENC
LOSED BY 和FIELDS ESCAPED BY 值必须是⼀个单个字符。FIELDS TERMINATED BY 和LINES TERMINATED BY 值可以超过⼀个字符。例如，为了写⼊由回车换⾏符终⽌的⾏，或读取包含这样的⾏的⽂件，应该指定⼀个LINES TERMINATED BY '\r\n' ⼦句。
18：FIELDS [OPTIONALLY] ENCLOSED BY中的关键字OPTIONALLY
对于输出（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"
mysql删除重复的数据保留一条
如果⽤户指定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
19：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 处理的规则在本节的后部进⾏说明。
关于更多的 “\” 转义语法信息，查看⽂字值 。
20：在某些情况下，字段与⾏处理相互作⽤：
如果LINES TERMINATED BY 是⼀个空字符串，FIELDS TERMINATED BY 是⾮空的，则各⾏以FIELDS TERMINATED BY作为结尾。 如果FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 值都是空的('')，那么⼀个固定⾏(⽆定界符) 格式将被使⽤。⽤固定⾏格式时，在字段之间不使⽤分隔符（但是⽤户仍然有⼀个⾏终结符）。列值的写⼊和读取使⽤列的“显⽰”宽度。例如，如果⼀个列被定义 为
INT(7)，列的值将使⽤7 个字符的字段被写⼊。对于输⼊，列值通过读取7 个字符来获得。
LINES TERMINATED BY 仍然⽤于分离⾏。如果⼀⾏没有包含所有的字段，那么列的剩余部分被设置为它们的默认值。如果⽤户没有⼀个⾏终结符，⽤户应该设置它为''。在这种情况下，⽂本⽂件必须包含每⾏的所有的字段。
21：固定⾏格式也影响对NULL 值的处理；见下⾯。注意，如果⽤户正在使⽤⼀个多字节的字符集，固定长度格式将不能⼯作。
22：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 值和空字符串均⽆法辨别，这是因为两者都被作为空字符串写⼊。如果⽤户需要在读取⽂件并返回时能够分辨两者，则⽤户不应使⽤固定⾏格式。
23：⼀些不能被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 不知道字段或⾏值在哪⾥结束。
24：Column 列表可以包含列名或者⽤户变量，并且⽀持SET ⼦句。这使得能⽤输⼊值给⽤户变量赋值， 并在赋予列的结果之前对这些值进⾏变换。
SET ⼦句中的⽤户变量有多种⽤途。
下⾯的例⼦将数据⽂件的第⼀列直接作为t1.column1 的值，并将第⼆列赋予⼀个⽤户变量，此变量在作为t1.column2 的值之前进⾏⼀个除法操作
mysql>LOAD DATA INFILE '' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100;
SET ⼦句可以提供不是来源于输⼊⽂件的值。下⾯的语句将column3 设置为当前的⽇期和时间：
mysql>LOAD DATA INFILE '' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;
通过将⼀个输⼊值赋予⼀个⽤户变量，可以将此输⼊值丢弃，并且不将此值赋予表的⼀个列：
mysql>LOAD DATA INFILE '' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
使⽤列/变量列表和SET⼦句要受到⼀下限制：
·    SET ⼦句中的赋值列名应当只能在赋值操作符的左边。
·    在SET 赋值语句中，可以使⽤⼦查询。此查询返回⼀个将被赋予列的值，它可能仅仅是⼀个标量查询。不能⽤⼦查询去查询将被导⼊的表。
·    对于列/变量列表或SET ⼦句，由于使⽤IGNORE ⼦句⽽被忽略的⾏就不会被处理。
·    由于⽤户变量没有显⽰宽度，当导⼊的数据是⾏固定格式时，将不能使⽤⽤户变量。
25：当处理输⼊⾏时，LOAD DATA 将⾏分为域，如果提供了列/变量列表和SET ⼦句，将按照它去使⽤这些值。然后得到的⾏就插⼊到表中。如果此表有BEFORE INSERT 或AFTER INSERT 触发器，则插⼊⾏前后将分别激活它们。
26：如果输⼊的记录⾏有太多的字段，多余的字段将被忽略，并增加警告的数⽬。
27：如果⼀个输⼊⾏的字段较少，没有输⼊字段的列将被设置为缺省值。缺省值赋值在CREATE TABLE 语法 中被描述。
28：⼀个空的字段值不同于字段值丢失的解释：
·    对于字符串类型，列被设置为空字符串。
·    对于数字类型，列被设置为0。
·    对于⽇期和时间类型，列被设置为适合列类型的“零”值。参考⽇期和时间类型 。
29：如果在⼀个INSERT 或UPDATE 语句中明确地将⼀个空字符串赋给⼀个字符串、数字或⽇期或时间类型，⽤户会得到与上⾯相同的结果。
30：只有在两种情况下TIMESTAMP 列被设置为当前⽇期和时间。⼀种情况时当列有⼀个NULL 值（也就是\N）时；另⼀种情况是（仅对于第⼀个TIMESTAMP 列），当⼀个字段清单被指定时，TIMESTAMP 列会从字段清单中被略去。
31：LOAD DATA INFILE 认为所有的输⼊均是字符串，因⽽，对于ENUM 或SET 列，⽤户不能以INSERT 语句的形式为其设置数字值。所有的ENUM 和SET 必须以字符串指定。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。