SELECT into outfile备份数据库数据_MySQL
程序员文章站
2024-01-06 10:19:34
...
bitsCN.com
SELECT into outfile 备份数据库数据 select email into outfile "test.txt" from email; select substring(boss,0,2),addr from guest; LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE “/opt/abc.txt" INTO TABLE table_name -- FIELDS TERMINATED BY ',' (column1, column2,colum3); mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM ... 为了将由逗号分隔的文件读回来，正确的语句将是： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ','; -- 相反，如果你试图用下面显示的语句读取文件，它不会工作，因为它命令LOAD DATA INFILE在字段之间 寻找定位符： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '/t'; 可能的结果是每个输入行将被解释为单个的字段。 LOAD DATA INFILE能被用来读取从外部来源获得的文件。例如，以dBASE格式的文件将有由逗号分隔并用 双引号包围的字段。如果文件中的行由换行符终止，下面显示的命令说明你将用来装载文件的字段和行 处理选项： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n'; -- 任何字段或行处理选项可以指定一个空字符串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和 FIELDS ESCAPED BY值必须是一个单个字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超 过一个字符。例如，写入由回车换行符对（CR+LF）终止的行，或读取包含这样行的一个文件，指定一个 LINES TERMINATED BY '/r /n'子句。 -- 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" ================= insert into tabl(id,email) select id, email from guest; 作者 huimai bitsCN.com
SELECT into outfile 备份数据库数据 select email into outfile "test.txt" from email; select substring(boss,0,2),addr from guest; LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE “/opt/abc.txt" INTO TABLE table_name -- FIELDS TERMINATED BY ',' (column1, column2,colum3); mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM ... 为了将由逗号分隔的文件读回来，正确的语句将是： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ','; -- 相反，如果你试图用下面显示的语句读取文件，它不会工作，因为它命令LOAD DATA INFILE在字段之间 寻找定位符： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '/t'; 可能的结果是每个输入行将被解释为单个的字段。 LOAD DATA INFILE能被用来读取从外部来源获得的文件。例如，以dBASE格式的文件将有由逗号分隔并用 双引号包围的字段。如果文件中的行由换行符终止，下面显示的命令说明你将用来装载文件的字段和行 处理选项： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n'; -- 任何字段或行处理选项可以指定一个空字符串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和 FIELDS ESCAPED BY值必须是一个单个字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超 过一个字符。例如，写入由回车换行符对（CR+LF）终止的行，或读取包含这样行的一个文件，指定一个 LINES TERMINATED BY '/r /n'子句。 -- 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" ================= insert into tabl(id,email) select id, email from guest; 作者 huimai bitsCN.com