如何使用 MySQL INTO OUTFILE 在文件中导出换行符 '\n' [英] How to export break line '\n' in a file using MySQL INTO OUTFILE

问题描述

我正在尝试使用 MySQL 导出 sql 文件.我使用这种方法是因为我想用 LOAD DATA 函数导入文件.并且文件名必须动态生成.我将通过命令行执行生成的文件.

这是脚本:

SET @filename = 'C:/icl/myfile.CSV';SET @str = CONCAT('加载数据文件',@filename);SET @str = CONCAT(@str,' INTO TABLE icl_process_data.filecontent LINES TERMINATED BY ''\n''');SELECT @str INTO OUTFILE 'C:/icl/tmp_script.sql';

结果如下:

LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\'

我不知道为什么断线'\n'的结果是'\'.我该如何解决这个问题?

解决方案

这是一个转义序列的问题，有几种方法可以解决.我选择只将起始单引号放在靠近第一种方式末尾的外部双引号内(在 concat 中有 3 个块).

和单引号作为第二种方式(在 concat 中有 2 个块):

SET @filename = 'C:/icl/myfile.CSV';-- C:/icl/myfile.CSVSET @str = CONCAT('加载数据文件',@filename);-- 加载数据文件 C:/icl/myfile.CSV-- 第一种方法如下(如果忽略开头的 `--`，则结果为后面的行):SET @str = CONCAT(@str," INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '","\\n'");-- LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\n'-- 第二种方法如下(如果忽略开头的 `--`，则结果为后面的行):SET @str = CONCAT('加载数据文件',@filename);SET @str = CONCAT(@str,' INTO TABLE icl_process_data.filecontent LINES TERMINATED BY \'\\n\'');-- LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\n'

来自

I'm trying to export a sql file using MySQL. I use this method because I want to import files with the LOAD DATA function. And the file name must be dinamically generated. I will execute the file generated throught the command line.

This is the script:

SET @filename = 'C:/icl/myfile.CSV';

SET @str = CONCAT('LOAD DATA INFILE ',@filename);
SET @str = CONCAT(@str,' INTO TABLE icl_process_data.filecontent LINES TERMINATED BY ''\n''');

SELECT @str INTO OUTFILE 'C:/icl/tmp_script.sql';

This is the result:

LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\'

I don't know why the result of the break line '\n' is '\'. How could I solve this problem?

解决方案

It is a question of escape sequences and there are several ways to do it. I chose just putting the starting single quote nestled inside the outer double quotes near the end of the first way (with 3 chunks in concat).

And single quotes as the second way (with 2 chunks in concat):

SET @filename = 'C:/icl/myfile.CSV';
-- C:/icl/myfile.CSV

SET @str = CONCAT('LOAD DATA INFILE ',@filename);
-- LOAD DATA INFILE C:/icl/myfile.CSV

-- First way is below (with the result being the line after it if you ignore the `-- ` at the beginning):
SET @str = CONCAT(@str," INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '","\\n'");
-- LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\n'

-- Second way is below (with the result being the line after it if you ignore the `-- ` at the beginning):
SET @str = CONCAT('LOAD DATA INFILE ',@filename);
SET @str = CONCAT(@str,' INTO TABLE icl_process_data.filecontent LINES TERMINATED BY \'\\n\'');
-- LOAD DATA INFILE C:/icl/myfile.CSV INTO TABLE icl_process_data.filecontent LINES TERMINATED BY '\n'

From the mysql manual page on String Literals:

这篇关于如何使用 MySQL INTO OUTFILE 在文件中导出换行符 '\n'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！