SELECT [列名] FROM table_name [WHERE 语句]
INTO OUTFILE 'path' [OPTION];
1.普通的select语句查出所需要的数据。
2.option可选参数,取值如下:
【
FIELDS TERMINATED BY '字符串':设置字段之间的分割符号。默认值是“\t”。
FIELDS ENCLOSED BY '字符':设置字符来包裹字段的值,只能为单个字符。默认情况下不使用任何符号。
FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来包裹住CHAR、VARCHAR和TEXT等字符型字段。默认无符号。
FIELDS ESCAPED BY '字符':设置转义字符。默认值为“\”。
LINES STARTING BY '字符串':设置每行数据开头的字符。默认情况下不使用任何字符。
LINES TERMINATED BY '字符串':设置每行数据结尾的字符。默认值是“\n”。
】
FIELDS和LINES两个子句都是自选的,但是如果两个子句都被指定了,FIELDS必须位于LINES的前面。
举个栗子
SELECT * FROM log_tb
INTO OUTFILE "F://d.txt"
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES
STARTING BY '!'
TERMINATED BY '\n';
如果包含中文符号乱码加入CHARACTER SET 编码
SELECT * FROM log_tb
INTO OUTFILE "F://d.txt"
CHARACTER SET 编码
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES
STARTING BY '!'
TERMINATED BY '\n';
数据恢复:
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
IGNORE number LINES 忽略输入文件中的前n行数据;
col_name 指定列
SELECT ... INTO OUTFILE 和 LOAD DATA INFILE 将数据从一个数据库写到一个文件中,然后再从文件中将它读入数据库中时,两个命令的字段和行处理选项必须匹配。否则,LOAD DATA INFILE 将不能正确地解释文件内容
INTO OUTFILE 'path' [OPTION];
1.普通的select语句查出所需要的数据。
2.option可选参数,取值如下:
【
FIELDS TERMINATED BY '字符串':设置字段之间的分割符号。默认值是“\t”。
FIELDS ENCLOSED BY '字符':设置字符来包裹字段的值,只能为单个字符。默认情况下不使用任何符号。
FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来包裹住CHAR、VARCHAR和TEXT等字符型字段。默认无符号。
FIELDS ESCAPED BY '字符':设置转义字符。默认值为“\”。
LINES STARTING BY '字符串':设置每行数据开头的字符。默认情况下不使用任何字符。
LINES TERMINATED BY '字符串':设置每行数据结尾的字符。默认值是“\n”。
】
FIELDS和LINES两个子句都是自选的,但是如果两个子句都被指定了,FIELDS必须位于LINES的前面。
举个栗子
SELECT * FROM log_tb
INTO OUTFILE "F://d.txt"
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES
STARTING BY '!'
TERMINATED BY '\n';
如果包含中文符号乱码加入CHARACTER SET 编码
SELECT * FROM log_tb
INTO OUTFILE "F://d.txt"
CHARACTER SET 编码
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES
STARTING BY '!'
TERMINATED BY '\n';
数据恢复:
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
IGNORE number LINES 忽略输入文件中的前n行数据;
col_name 指定列
SELECT ... INTO OUTFILE 和 LOAD DATA INFILE 将数据从一个数据库写到一个文件中,然后再从文件中将它读入数据库中时,两个命令的字段和行处理选项必须匹配。否则,LOAD DATA INFILE 将不能正确地解释文件内容