mysql - Perl 中的多行 MySQL

Question

我试图将命令传递到我的数据库(MariaDB/MySQLi)，但无论我尝试什么，Perl 的 DBI 都会给我一个错误。问题是我可以在 phpMyAdmin 中正常执行 MySQL，不会出现错误。我收到的错误是:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near SELECT DISTINCT t.bf FROM wrpec t JOIN wrpec r ON t.path LIKE r.path || '%' at line 2 at retrieve-includes.pl line 20.

DBD::mysql::st fetchrow_array failed: fetch() without execute() at retrieve-includes.pl line 22.

完整的测试文件(显然是假数据库)如下。

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use Data::Dumper;

my $dsn = "DBI:mysql:database=mydatabase;host=machine";
my $dbh = DBI->connect($dsn, 'root', '') or die "Couldn't connect to database: " . DBI->errstr;;

my $request_includes = "
SET sql_mode = PIPES_AS_CONCAT;
SELECT DISTINCT t.bf
FROM `wrpec` t
JOIN `wrpec` r
ON t.path LIKE r.path || '%'
WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree;";

my $sth = $dbh->prepare($request_includes) or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute();

while (my @row = $sth->fetchrow_array()) {
   print Dumper(\@row);
}

$sth->finish();

我还尝试了一些我在网上找到的想法:使用清理子将所有空白字符替换为硬空格，以确保您传递的是字符串，并且我也尝试了这种表示法(我忘记了名称) )但它也不起作用(同样的错误)。

my $request_includes = <<"REQUEST_INCLUDES";

SET sql_mode = PIPES_AS_CONCAT;
SELECT DISTINCT t.bf
FROM `wrpec` t
JOIN `wrpec` r
ON t.path LIKE r.path || '%'
WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree;
REQUEST_INCLUDES

Answer 1

您收到 SQL 语法错误，因为默认情况下 DBD::mysql doesn't allow you to execute multiple statements at once 。您应该运行两个单独的 DBI 命令:

$dbh->do( q{SET sql_mode = 'PIPES_AS_CONCAT'} );

my $sth = $dbh->prepare(q{
    SELECT DISTINCT t.bf
    FROM `wrpec` t
    JOIN `wrpec` r
    ON t.path LIKE r.path || '%'
    WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree
});

<小时/>

I was trying to create a script that works on MySQL as wel as PostgreSQL without changes.

启用 MySQL 的 ANSI mode并双引号您的标识符:

$dbh->do( q{SET sql_mode = 'ANSI'} ) if $mysql;

my $sth = $dbh->prepare(q{
    SELECT DISTINCT t.bf
    FROM "wrpec" t
    JOIN "wrpec" r
    ON t.path LIKE r.path || '%'
    WHERE r.bf = 'WRPECsmain\%hd' AND t.has_tree
});

但是还有其他一些问题:

• 未转义 _和%在r.path将被解释为通配符
• 'foo' LIKE 'foo%'是真的

仅获取 t.path 所在的行是 r.path 的前缀，但它们不相等，请执行以下操作:

SELECT DISTINCT t.bf
FROM "wrpec" t
JOIN "wrpec" r
  ON POSITION(t.path IN r.path) = 1
  AND t.path != r.path
WHERE r.bf = 'WRPECsmain\%hd'
  AND t.has_tree