MySQL load data 快速导入大批量数据
程序员文章站
2022-06-11 11:30:03
...
- MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中(一般文本的一行对应表的一条记录)。
- 数据库应用程序开发中,涉及大批量数据需要插入时,使用 load data 语句的效率比一般的 insert 语句的高很多
- 可以看成select … into outfile语句的反操作,select … into outfile将数据库表中的数据导出保存到一个文件中。参考MySQL 5.7 官方手册 SELECT … INTO Syntax
load data 语法
load data 语法如下,详细情况可参考 MySQL 5.7 官方手册 LOAD DATA Syntax
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 使用细节
假如有一个用户信息表
CREATE TABLE `user_info` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(36) NULL COMMENT '姓名',
`age` int NULL COMMENT '年龄',
`address` varchar(255) NULL COMMENT '地址',
`create_date` datetime NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
)
COMMENT='用户信息表'
;
假如待导入用户信息表的文本文件名为 my_user_info.txt,内容如下所示,换行符采用 \n ,每行代表对应表的一行记录,其中 || 作为字段分隔符,而 \N 表示对应字段为空值 null
3||张三||22||北京||2012-09-19 00:00:00
4||李明||32||\N||2017-05-12 00:00:00
5||孙权||12||广州||\N
执行 load data 将用户信息导入表user_info中
load data local infile 'D:/my_user_info.txt' into table user_info
CHARACTER SET utf8 -- 可选,避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符,每个字段(列)以什么字符分隔,默认是 \t
OPTIONALLY ENCLOSED BY '' -- 文本限定符,每个字段被什么字符包围,默认是空字符
ESCAPED BY '\\' -- 转义符,默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符,如字段本身也含\n,那么应先去除,否则load data 会误将其视作另一行记录进行导入
(id, name, age, address, create_date) -- 每一行文本按顺序对应的表字段,建议不要省略
执行成功后,查询表user_info数据,如下图所示
注意事项
-
字段中的空值 null 需要使用 \N 表示,如果用空字符串表示,那么根据不同的数据类型,MySQL 处理也各异
- 数据库字段如果是varchar/char,插入空时,load data 默认导入 空字符串
- 数据库字段如果是decimal,插入空时,load data 默认导入 0.00000000
- 数据库字段如果是datetime,插入空时,load data 默认导入 0000-00-00 00:00:00
-
字段类型如果是datetime,应该严格把控相应文本数据的格式,建议采用类似这种 yyyy-MM-dd HH:mm:ss 同时有日期、时间的格式,否则难以保证数据导入的正确性
- 数据库字段如果是datetime,插入yyyy-MM-dd时,load data 默认导入 yyyy-MM-dd 00:00:00,数据正确性能够保证
- 数据库字段如果是datetime,插入HH:mm:ss时,load data 默认导入 0000-00-00 00:00:00,数据正确性不能够保证
-
local关键字
如果 load data 使用时指定了 local 关键字,则表示文件放在客户端主机上,从客户端读取文本文件;如果没指定,则表示从服务器主机读取文本文件
-
replace 和 ignore 关键字
- replace 和 ignore 关键字用于控制与唯一键重复的记录的处理
- 如果指定 replace ,与唯一键重复的行将被覆盖更新。对于任意记录覆盖更新时,如果唯一键外的各个字段其实都没有变化,那么执行操作时受影响行数为1;如果除唯一键外的任意字段有变化,那么执行操作时受影响行数为2
- 如果指定 ignore ,与唯一键重复的行将被忽略,默认指定 ignore
-
关键字 low_priority
- 如果你指定关键词low_priority,那么MySQL将会等到没有其他人读这个表的时候,才把数据插入
-
关键字 local
- 如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上。
-
文件寻找规则
- 当在服务器主机上寻找文件时,服务器使用下列规则:
(1)如果给出一个绝对路径名,服务器使用该路径名。
(2)如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。
(3)如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
例如: /myfile txt”给出的文件是从服务器的数据目录读取,而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取
- 当在服务器主机上寻找文件时,服务器使用下列规则:
-
关键字 load data infile
- load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候
下面的例子显示了如何向指定的栏目(field)中导入数据:
load data infile “/home/Order txt” into table Orders(Order_Number, Order_Date, Customer_ID);
- load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候
-
分隔符
-
fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
terminated by分隔符:意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符
terminated by描述字段的分隔符,默认情况下是tab字符(\t)
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠(backslash:\ )
例如:load data infile “/home/mark/Orders txt” replace into table Orders fields terminated by’,’ enclosed by ‘"’ - 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’;
-
fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
参考文章
https://blog.csdn.net/u012815136/article/details/88953289
上一篇: 云服务器项目部署
推荐阅读
-
mysql遇到load data导入文件数据出现1290错误的解决方案
-
MySQL数据库迁移快速导出导入大量数据
-
MySQL通过 LOAD DATA INFILE 批量导入数据
-
MySQL如何快速导入数据
-
mysql使用load data infile导入数据错误
-
Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL
-
mysql导入大批量数据出现MySQL server has gone away的解决方法
-
mysql导入大批量数据出现MySQL server has gone away的解决方法
-
算法:将table标识的树形结构文本数据快速导入Mysql邻接表
-
Mysql错误积累001-load data导入文件数据出现1290错误