load data 简介

  • 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 -- 可选,指定导入文件的编码,避免中文乱码问题。假如这里文件 my_user_info.txt 的编码为 gbk,那么这里编码就应该设为 gbk 了
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 关键字,则表示文件放在客户端主机上,从客户端读取文本文件;如果没指定,则表示从服务器主机读取文本文件

  • replaceignore 关键字

    • replaceignore 关键字用于控制与唯一键重复的记录的处理
    • 如果指定 replace ,与唯一键重复的行将被覆盖更新。对于任意记录覆盖更新时,如果唯一键外的各个字段其实都没有变化,那么执行操作时受影响行数为1;如果除唯一键外的任意字段有变化,那么执行操作时受影响行数为2
    • 如果指定 ignore ,与唯一键重复的行将被忽略,默认指定 ignore
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐