一.概述
上篇讲到的表的数据导出(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是导入数据最快的方法,在管理维护中使用很也频繁,应此要重点掌握。