导出文本:
select ...into outfile 导出文件(注:导出的文件存放在mysql的服务器上)
子句:FIELDS TERMINATED BY '*' 指定字段分隔符
子句:OPTIONALLY ENCLOSED BY '*' 指定字段数据包围符
子句:LINES TERMINATED BY '*' 指定行行结束符
如:
select * from ndb_t1 into outfile '/opt/tmpdata/ndb_t1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
参考:http://dev.mysql.com/doc/refman/5.6/en/select.html
导入文本:
load data命令
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (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,...)]
[SET col_name = expr,...]
当指定LOCAL,将从客户端主机上读取文件,并发送到服务器操作系统的临时目录下(Windows:c:\windows\temp;Linux:/tmp )。这儿要注意mysql对目录的权限
没有指定LOCAL,导入的文件必须在mysql服务器上。
例子:
Converting strings into dates while loading data using LOAD DATA INFILE:
In the following example, we are trying to convert the data in the file for date columns col3, col4 in formats 'mm/dd/yyyy', 'dd/mm/yyyy' into MySQL standard YYYY-mm-dd respectively.
load data infile '/tmp/xxx.dat'
into table xxx
fields terminated by '|'
lines terminated by '\n'
(col1,
col2,
@col3,
@col4,
col5)
set
col3 = str_to_date(@col3, '%m/%d/%Y'),
col4 = str_to_date(@col4, '%d/%m/%Y')
;
参考:http://dev.mysql.com/doc/refman/5.6/en/load-data.html
Character | Escape Sequence |
---|---|
\0 | An ASCII NUL (0x00) character |
\b | A backspace character |
\n | A newline (linefeed) character |
\r | A carriage return character |
\t | A tab character. |
\Z | ASCII 26 (Control+Z) |
\N | NULL |
如果希望load data 能够导入null而非空串'',在文本文件中字段相应位置使用\N代替null。
null与''的区别,见理解:MySQL的null与空字符串的不同
附:导出数据到客户端的方法:
1、使用mysql的tee(\T)命令,也就是把MYSQL的所有输出都输入到指定文件。
例:
mysql>tee /*/a.txt
mysql>select * from t1;
2、使用mysql -e "select * from t1" > t1.txt
详见:http://blog.chinaunix.net/uid-167175-id-3621600.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-1083810/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/195110/viewspace-1083810/