MySql教程

MySQL - LOAD DATA LOCAL INFILE将数据导入数据表

本文主要是介绍MySQL - LOAD DATA LOCAL 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语句以非常高的速度将文本文件中的行读入表中。 LOAD DATA是补充 SELECT ... INTO OUTFILE。要将表中的数据写入文件,请使用 SELECT ... INTO OUTFILE。要将文件读回表中,请使用 LOAD DATA。两个语句的FIELDS和LINES子句的语法相同,例如:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT ... INTO OUTFILE 语句有以下属性:

  • LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。
  • SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。
  • 输出不能是一个已存在的文件。防止文件数据被篡改。
  • 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
  • 在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除。

二、关键字说明

1 如果你指定关键词low_priority, 那么MySQL将会等到没有其他人读这个表的时候,才把插入数据。可以使用如下的命令:

load data low_priority infile "/home/mark/data sql" into table Orders;

2 如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上。

3 replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。例如:

load data low_priority infile "/home/mark/data sql" replace into table Orders;

4 分隔符
(1) fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:

terminated by: 分隔符,意思是以什么字符作为分隔符
enclosed by:字段括起字符
escaped by:转义字符
lines terminated by: 描述字段的分隔符,默认情况下是tab字符(\t) 
ignore number lines:用来忽略导入文件的开始的行。例如:number=1,则忽略导入文件的第一行数据。

例如:

load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"'; 

(2)lines 关键字指定了每条记录的分隔符默认为'\n'即为换行符
如果两个字段都指定了那fields必须在lines之前。如果不指定fields关键字缺省值与如果你这样写的相同: fields terminated by'\t' enclosed by ’ '' ‘ escaped by'\\'
如果你不指定一个lines子句,缺省值与如果你这样写的相同: lines terminated by'\n'
例如:

load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';

5 load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候
下面的例子显示了如何向指定的栏目(field)中导入数据:

load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID); 

6 当在服务器主机上寻找文件时,服务器使用下列规则:
(1)如果给出一个绝对路径名,服务器使用该路径名。
(2)如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。
(3)如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
例如: /myfile txt”给出的文件是从服务器的数据目录读取,而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取。

三、实战

示例1:LOAD DATA LOCAL INFILE导入txt文件

1.1数据准备

首先我们创建一个milo.xlsx文件,准备数据如下图:

 然后新建milo.txt文件,将数据复制过去:

 接下来,我们需要在自己本地库创建对应的数据表

 **注意:**需要注意表字段需要对应数据中的id和name;

1.2 数据导入
load data local infile 'F:\\milo.txt'
into table test
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines
(id,name);
1.3结果

 

示例2:LOAD DATA LOCAL INFILE导入csv文件

2.1数据准备

首先我们创建milo.csv文件,如下图所示

2.2数据导入
LOAD DATA LOCAL INFILE 'F:\\milo.csv' INTO TABLE test
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name)
2.3结果

 四、拓展

拓展一:mybatis使用load data local infile实现大批量导入数据到mysql数据库

 

这篇关于MySQL - LOAD DATA LOCAL INFILE将数据导入数据表的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!