mysql 开发进阶篇系列 50 表的数据导入(load data infile，mysqlimport )

mysql 开发进阶篇系列 50 表的数据导入(load data infile，mysqlimport ) - 花阴偷移

MrHSR 2018-10-16 原文

mysql 开发进阶篇系列 50 表的数据导入(load data infile，mysqlimport )

一.概述

　　上篇讲到的表的数据导出(select .. into outfile 或者mysqldump)，这篇继续讲表的数据导入,导入也同样有二个方法，分别是load data infile… 和mysqlimport。本质是一样的，区别在于前者是mysql内部执行，后者是mysql 外部执行。

　　1. load data infile 方法1

-- 格式语法如下：
load data [local] infile \'filename\' info table tablename[option]

Option选项	说明
Fields terminated by \’string\’	字段分隔符, 默认为制表符 \’ \t\’
Fields [optionally] enclosed by \’char\’	字段引用符, 如果加了optionally，则只用在char， varcahr, text 等字符型字段上。
Fields escaped by \’char\’	转义字符, 默认为 \’\n\’
Lines starting by \’string\’	每行前都加此字符串, 默认\’\’
Lines terminated by \’string\’	行结束符，默认为\’\n\’
Ignore number lines	忽略输入文件中的前n 行数据
(col_name_or_user_var, ..)	按照列出的字段顺序和字段数量加载数据
Set col_name=expr, …	将列做一定的数值转换后再加载

　　　　下面将/tmp/ testbackup1.txt 中的数据加载到表testbackup中，案例如下：

-- 先备份
CREATE TABLE testbackup2 SELECT * FROM  testbackup;
-- 删除
DELETE FROM testbackup
-- 全部导入(12行受影响)
LOAD DATA INFILE \'/tmp/testbackup1.txt\' INTO TABLE testbackup FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\'
-- 导入过滤前二行(10行受影响)
DELETE FROM testbackup
LOAD DATA INFILE \'/tmp/testbackup1.txt\' INTO TABLE testbackup FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\'  IGNORE 2 LINES
-- 在上面的基本上，指定加载部分列和列顺序
DELETE FROM testbackup
LOAD DATA INFILE \'/tmp/testbackup1.txt\' INTO TABLE testbackup FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\'  IGNORE 2 LINES(id,`name`)
-- 将id列的内容+10后再加载到表中(此时id从11开始)
DELETE FROM testbackup
LOAD DATA INFILE \'/tmp/testbackup1.txt\' INTO TABLE testbackup FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' SET id=id+10

　　2. mysqlimport 方法2

--格式语法如下:
mysqlimport -uroot -p  [-- local] dbname order_tab.txt [option]

Option选项	说明
–fields-terminated-by =name	字段分隔符
–fields-enclosed-by=name	字段引用符
–fields-optionally-enclosed-by=name	字段引用符, 加了optionally，则只用在char， varcahr, text 等字符型字段上。
–fields-escaped-by=name	转义字符
–lines-terminated-by =name	记录结束符
–Ignore-lines=number	忽略输入文件中的前n 行数据


-- 注意文件名(testbackup.txt)要与test库中的testbackup表名一致,导入脚本如下：
[root@hsr ~]# mysqlimport -uroot -p test /tmp/testbackup.txt --fields-terminated-by=\',\' --fields-enclosed-by=\'"\'
Enter password: 
test.testbackup: Records: 12  Deleted: 0  Skipped: 0  Warnings: 0

　　注意: 如果导入导出是跨平台操作的(windows和linux)，那么参数–lines-terminated-by的值在windows上为–lines-terminated-by=\’\r\n\’ 。在linux上为–lines-terminated-by=\’\n\’。上面的案例中–lines-terminated-by的值默认是\’\n\’ 。对于load data infile是导入数据最快的方法，在管理维护中使用很也频繁，应此要重点掌握。