mysql: load data与select into outfile

1、从数据库导出数据

• SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
• 输出不能是一个已存在的文件。防止文件数据被篡改。
• 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
• 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。
• LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

 

下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

可以指定文件格式

SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

2、向数据库加载数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS 
TERMINATED BY ':'
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n' (column_name1, column_name2, ...);

• FIELDS TERMINATED BY ","   //  字段以”，“分隔
• FIELDS ENCLOSED BY " "   // 字段闭合标签，例如ENCLOSED BY " "" ",则字段以双引号”“闭合，在双引号内部的字段分隔符都会被忽略
• FIELDS ESCAPED BY " " // FIELDS ESCAPED BY 控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY 字符不是空的，它将被用于做为下列输出字符的前缀：

  •     FIELDS ESCAPED BY 字符

  •     FIELDS [OPTIONALLY] ENCLOSED BY 字符 。

  •     FIELDS TERMINATED BY 和LINES TERMINATED BY 值的第一个字符。

  •     ASCII 0 （实际上在转义字符后写上ASCII '0'，而不是一个零值字节）。

• LINES TERMINATED BY "\r\n" //Windows 系统下每一行的结束标志，Linux系统应该是LINES TERMINATED BY "\n"或<LINES TERMINATED BY "\r"或可以直接省略。
• FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是一个单个字符。
• FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超过一个字符。

FIELDS [OPTIONALLY] ENCLOSED BY控制字段的包围字符。对于输出(SELECT ... INTO OUTFILE)，如果你省略OPTIONALLY，所有的字段由ENCLOSED BY字符包围。对于这样的输出的一个例子(使用一个逗号作为字段分隔符)显示在下面：

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果你指定OPTIONALLY，ENCLOSED BY字符仅被用于包围CHAR和VARCHAR字段：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

如果FIELDS ESCAPED BY 字符为空，那么将没有字符被转义并且NULL 值仍输出为NULL，而不是\N。指定一个空的转义字符可能不是一个好的方法，特别是用户的数据字段值中包含刚才列表中的任何字符时。

对于输入值，如果FIELDS ESCAPED BY 字符不是空字符，则出现这种字符时会被剥离，然后以下字符被作为字段值的一部分。例外情况是，被转义的‘0’或‘N’（例如，\0 或\N，此时转义符为‘\’）。这些序列被理解为ASCII NUL（一个零值字节）和NULL。用于NULL 处理的规则在本节的后部进行说明。

IGNORE 1 LINES //忽略特定行数，CSV文件可以忽略掉第一行标题

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

LOAD DATA LOCAL INFILE 'dump.txt'
INTO TABLE mytbl (b, c, a);

一，在“load data infile”中：

先看看enclosed-by的作用：

 1，在字段值中存在"enclosed by"字符，并且其前一个字符为"escaped by"字符，则导入mysql后，"escaped by"字符会被去除。

 2，对于文本中被"enclosed by"包裹的字段值，如果值内出现连续两个的"enclosed by"字符，则导入后，会变成一个。

 

再看看escaped-by作用：

1，字段值内出现"escaped by"字符，该字符将被去除，同时保留其后一个字符；

2，但是对以下特殊的两个“挨着”字符会进行"反转义"处理

"\\0" -- 反转义成\0（0x00）

"\\b", "\\n", "\\r", "\\t" -- 反转义成\b, \n, \r, \t

"\\Z" -- 反转义成ascii码26

"\\N" -- 反转义成NULL

 

二，在“select into outfile”中：

还是先看看enclosed-by的作用：

1，enclosed-by字句的optionally是有作用的

有optionally，则只对字符串类型的字段使用encloed-by字符“包裹”；

无optionally，则对全部字段使用enclosed-by字符“包裹”。

 

最后看看escaped-by的作用：

1，对以下4中情况的字符进行转义

字段值中有"escaped by"字符

字段值中有"enclosed by"字符

字段值中有"fields terminated by"和"lines terminated by"字符串的首个字符

ascii码0 -> 转义之后变成两个字节

第一个字节 - '\\'

第二个字节 - '0'（字符0，而不是ascii 0）