mysql - 如何在 Perl 中为 MySQL 查询转义 '\n'？

Question

我正在编写一个 Perl 脚本，使用 OSX 中的 Automator 自动将 Excel 文档上传到 MySQL 数据库表中。我已经写了一个功能查询:

load data
local infile '/pathToFile/file.csv'
replace into table database.table
fields terminated by ','
enclosed by '"'
lines terminated by '\r'
ignore 1 lines;

问题是我的 Perl 脚本因为与 \ 字符冲突而无法运行。我是一个 Perl 新手，所以我不知道如何正确地转义这个字符以使查询工作。

我的 Perl 脚本:

#!/usr/bin/perl

# PERL MODULE
use Mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# Set Variables
$host = "127.0.0.1";
$database = "database";
$tablename = "plugin_status";
$user = "user";
$pw = "password";

# PERL MySQL Connector
$connect = Mysql->connect($host, $database, $user, $pw);

# Run Query to update table
$myquery = "load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '\r' ignore 1 lines;";

# EXECUTE THE QUERY FUNCTION
$execute = $connect->query($myquery);

这是生成的错误:

String found where operator expected at perlfile.pl line 20, near ""load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '"
    (Missing operator before ' lines terminated by '?)
Backslash found where operator expected at perlfile.pl line 20, near "' lines terminated by '\"
    (Missing operator before \?)
syntax error at perlfile.pl line 20, near ""load data local infile '/pathToFile/file.csv' replace into table database.table fields terminated by ',' enclosed by '"' lines terminated by '"
Bad name after r' at perlfile.pl line 20.

Answer 1

如果您希望生成的字符串包含双字节序列 \r，则在将字符串包裹在 " 之间时需要转义反斜杠。

还请记住，如果您使用 " 来包装字符串的内容，则您的字符串不能包含任何免费的 "，如果是这种情况，您将需要转义每个" 您希望成为字符串的一部分。请参见下面的示例。

my $data = "hello, I can write \"quotes\" inside my string";

---

尽管有更简单的替代方案，因此您不必担心转义任何内容¹。

$myquery = q{
  load data local infile '/pathToFile/file.csv'
  replace into table database.table
  fields
    terminated by ','
    enclosed by '"'
    lines terminated by '\r'
  ignore 1 lines;
};

^{1. 除非您计划在字符串中使用 }..}

---

在此处阅读有关字符串和转义序列的更多信息:

• perlmonks.org - String Literals In Perl