outfile

SELECT ... INTO OUTFILE

--- 查询输出到一个文件
select * from my_t1 into outfile "d:/my_t1.txt";

--- 指定分隔符
select id from test where id > 0
 into outfile 'c:\\test'
 FIELDS TERMINATED by ',' ENCLOSED by "" LINES TERMINATED by '\n';

-- 控制格式
-- TERMINATED 生成的数据分隔符, 默认是制表符
-- ENCLOSED 包裹
-- ESCAPED 转义字符

-- FIELDS 子句的缺省值是,
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

-- LINES 控制行格式, 子句的缺省值是
LINES TERMINATED BY '\n'

load data  [low_priority] [local] infile 'file_name.txt' [replace | ignore] into table t_name [控制格式]

-- local 在客户端查找文件, 未添加时在服务器查找文件
-- replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理

load data infile "d:/home_my_email" into table wait.my_email;
load data infile "d:/home_my_email" into table wait.my_email(name);    -- 指定字段
load data local infile "F:/account_data/971.txt" into table t_name;

-- 指定使用,号 和 换行做分隔符；
load data infile '/home/data.txt' into table t_name fields terminated by ',' lines terminated by '\n';

-- 实例
mysql -uroot -p$pw -Dcmcc \
-e "load data local infile '/home/mysql/data/t1.txt' into table t_name fields terminated by ','";