表的导入和导出

表的导入和导出

一、导出

1、方法一: 使用 select…into outfile… 命令

#字段分隔符为 ","，字段引用为 “"”（双引号），记录结束符为回车符
select * from customer into outfile '/home/mysql/tmp/outfile/customer.txt' fields terminated by "," enclosed by '"';

[mysql@localhost outfile]$ more customer.txt
"1","1","MARY","SMITH","MARY.SMITH@sakilacustomer.org","5","1","2006-02-14 22:04:36","2006-02-15 04:57:20"
"2","1","PATRICIA","JOHNSON","PATRICIA.JOHNSON@sakilacustomer.org","6","1","2006-02-14 22:04:36","2006-02-15 04:57:20"
"3","1","LINDA","WILLIAMS","LINDA.WILLIAMS@sakilacustomer.org","7","1","2006-02-14 22:04:36","2006-02-15 04:57:20"
"4","2","BARBARA","JONES","BARBARA.JONES@sakilacustomer.org","8","1","2006-02-14 22:04:36","2006-02-15 04:57:20"

参数说明

FIELDS TERMINATED BY 'string' （字段分隔符，默认制表符 '\t'）

FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加上 OPTIONALLY 选项则只用在 char、varchar 和 text 等字符型字段上，默认不使用引用符）

FIELDS ESCAPED BY 'char' （转义字符，默认为 '\'）

LINES STARTING BY 'string' （每行前都加此字符串，默认为 ''）

LINES TERMINATED BY 'string' （行结束符，默认为 '\n'）

#如果不希望数值型字段两边用引号引起，则可以加上 OPTIONALLY
select * from customer into outfile '/home/mysql/tmp/outfile/customer2.txt' fields terminated by "," optionally enclosed by '"';


[mysql@localhost outfile]$ more customer2.txt
1,1,"MARY","SMITH","MARY.SMITH@sakilacustomer.org",5,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
2,1,"PATRICIA","JOHNSON","PATRICIA.JOHNSON@sakilacustomer.org",6,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
3,1,"LINDA","WILLIAMS","LINDA.WILLIAMS@sakilacustomer.org",7,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"
4,2,"BARBARA","JONES","BARBARA.JONES@sakilacustomer.org",8,1,"2006-02-14 22:04:36","2006-02-15 04:57:20"

注意

	① select...into outfile... 产生的输出文件如果在目标目录下有重命名文件，将不会创建成功，源文件不会被覆盖
	② 当导出命令中包含字段引用时，数据中含有转义字符本身和字段引用符的字符需要被转义
	③ 当导出命令中不包含字段引用时，数据中心含有转义字符本身和字段分隔符的字符需要被转义

2、方法二：mysqldump

mysqldump -uroot -p -S /home/mysql/mysql.sock -T /home/mysql/tmp/outfile test t11 --fields-terminated-by ',' --fields-optionally-enclosed-by='"'


-rw-rw-r--. 1 mysql mysql 1324 Mar  6 09:11 t11.sql
-rw-rw-rw-. 1 mysql mysql   44 Mar  6 09:11 t11.txt

t11.sql 文件，记录正 t11 表的创建脚本

参数说明

--fields-terminated-by=name
                      Fields in the output file are terminated by the given string.
                      字段分隔符
--fields-enclosed-by=name
                      Fields in the output file are enclosed by the given character.
                      字段引用符
--fields-optionally-enclosed-by=name
                      Fields in the output file are optionally enclosed by the given character.
                      字段引用符，只用在 char、varchar 和 text 等字符型字段上
--fields-escaped-by=name
                      Fields in the output file are escaped by the given character.
                      转义字符
--lines-terminated-by=name
                      Lines in the output file are terminated by the given string.
                      记录结束符

二、导入

1、方法一：LOAD DATA INFILE…

#加载数据
load data infile '/home/mysql/tmp/outfile/t11_sel.txt' into table t11 fields terminated by "," optionally enclosed by '"';

#调整加载列顺序对应表顺序，或只想加载部分列
load data infile '/home/mysql/tmp/outfile/t11_new.txt' into table t11 fields terminated by "," optionally enclosed by '"' ignore 2 lines (Sid,name,id);

参数说明

FIELDS TERMINATED BY 'string' （字段分隔符，默认制表符 '\t'）
FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加上 OPTIONALLY 选项则只用在 char、varchar 和 text 等字符型字段上，默认不使用引用符）
FIELDS ESCAPED BY 'char' （转义字符，默认为 '\'）
LINES STARTING BY 'string' （每行前都加此字符串，默认为 ''）
LINES TERMINATED BY 'string' （行结束符，默认为 '\n'）

IGNORE number LINES （忽略输入文件中的前 n 行数据）
(col_name_or_user_var,...) （按照列出的字段顺序和字段数量加载数据）
SELECT col_name=expr,... （将列做一定的数值转换后再加载）

2、方法二：mysqlimport

注意：文件名要与表名相同
mysqlimport -uroot -p -S /home/mysql/mysql.sock test /home/mysql/tmp/outfile/t11.txt --fields-terminated-by=',' --fields-enclosed-by='"'

参数说明

--fields-terminated-by=name
                      Fields in the output file are terminated by the given string.
                      字段分隔符
--fields-enclosed-by=name
                      Fields in the output file are enclosed by the given character.
                      字段引用符
--fields-optionally-enclosed-by=name
                      Fields in the output file are optionally enclosed by the given character.
                      字段引用符，只用在 char、varchar 和 text 等字符型字段上
--fields-escaped-by=name
                      Fields in the output file are escaped by the given character.
                      转义字符
--lines-terminated-by=name
                      Lines in the output file are terminated by the given string.
                      记录结束符
--ignore-lines=number
		      忽略前几行