ZetCode

Perl DBI

最后修改于 2023 年 8 月 24 日

Perl DBI教程展示了如何使用DBI模块在Perl中进行数据库编程。

在本文中，我们使用MariaDB数据库。大多数示例只需稍作修改即可适用于其他数据库。

注意：虽然Perl DBI是一个数据库无关的接口，但有几个属性和SQL语句是MariaDB特有的。

有些属性是MariaDB特有的；例如 mariadb_multi_statements 属性，它允许在单个查询中使用多个语句。此外，还有MariaDB独有的SQL语句，如 SELECT VERSION() 或 USE dbname。

本教程通过命令行示例介绍Perl DBI模块。其中大多数都使用了DBI提供的自动错误处理。这对于加载数据从数据库生成报告的脚本来说已经足够了。另一方面，在修改关键数据、处理7x24系统以及同时处理数千用户的应用程序时，对错误处理、代码正确性和健壮性的要求会有很大不同。

总的来说，错误消息不应呈现给用户。它们应该写入日志文件。所有可能的错误消息都应该被记录下来；即使它们被认为是无害的。用户名和密码应该从配置文件或环境变量中检索。

Perl DBI

Perl DBI（数据库接口）是Perl编程语言的一个数据库访问模块。它定义了一组方法、变量和约定，提供了一个标准的数据库接口。

其他语言也创建了类似的用于数据库工作的通用接口。Java有JDBC，PHP有PDO。

DBI还负责驱动程序的动态加载、错误检查和处理、为方法提供默认实现以及许多其他非数据库特定的任务。DBI将方法调用分派给相应的数据库驱动程序。DBD（数据库驱动程序）是一个Perl模块，它将DBI方法转换为特定数据库引擎的命令。数据库驱动程序由数据库供应商提供。

$ sudo cpanm DBD::MariaDB

在本文中，我们使用MariaDB数据库。我们安装了 DBD::MariaDB 模块。该模块是Perl5数据库接口的MariaDB和MySQL驱动程序。

drivers.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my @drvs = DBI->available_drivers;
say join "\n", @drvs;

该示例列出了我们系统上所有可用的驱动程序。

$ ./drivers.pl
DBM
ExampleP
File
Gofer
MariaDB
Mem
Proxy
Sponge
mysql

Perl常用DBI方法

下表列出了一些常用的DBI方法。

方法名描述
available_drivers返回所有可用驱动程序的列表
connect建立到请求数据源的连接
disconnect断开与数据库服务器的连接
prepare准备SQL语句以供执行
execute执行准备好的语句
do准备并执行SQL语句
bind_param将值与准备好的语句中的占位符关联
bind_col将Perl变量绑定到SELECT语句的输出字段
begin_work开始一个新事务
commit将最近一系列未提交的数据库更改写入数据库
rollback撤销最近一系列未提交的数据库更改
quote引用字符串字面量，以便在SQL语句中作为字面量值使用
dump_results获取所有行并打印它们
fetchrow_array获取下一行作为字段数组
fetchrow_arrayref获取下一行作为字段引用数组
fetchrow_hashref获取下一行作为哈希表引用
fetchall_arrayref获取所有数据作为数组的数组
finish完成一个语句并让系统释放资源
rows返回受影响的行数
column_info提供有关列的信息
table_info提供有关表的信息
primary_key_info提供有关表中主键的信息
foreign_key_info提供有关表中外键的信息

Perl DBI约定

Perl程序员在处理Perl DBI时通常使用以下变量名。

变量名描述
$dbh数据库句柄对象
$sth语句句柄对象
$drh驱动程序句柄对象（在应用程序中很少见到或使用）
$h以上任何句柄类型（$dbh、$sth或$drh）
$rc通用返回码（布尔值：true=成功，false=错误）
$rv通用返回值（通常是整数）
@ary从数据库返回的值列表，通常是一行数据
$rows处理的行数（如果可用，否则为-1）
$fh文件句柄
undefNULL值在Perl中用未定义值表示
\%attr传递给方法的属性值哈希的引用

Perl DBI数据库连接

connect方法用于建立到数据库的连接。disconnect方法用于关闭数据库连接。

$dbh = DBI->connect($dsn, $username, $password)
    or die $DBI::errstr;

$dbh = DBI->connect($dsn, $username, $password, \%attr)
    or die $DBI::errstr;

connect方法建立到请求数据源的数据库连接。如果连接成功，它将返回一个数据库句柄对象。我们使用disconnect方法终止连接。

$dsn是数据源名称。它是一个字符串，告诉DBI应该加载哪种驱动程序以及要创建连接的数据库的位置。

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

上面的字符串是Perl DBI中数据源名称的示例。

dbi:MariaDB:dbname=testdb

dsn总是以 dbi: 子字符串开头。然后是驱动程序名称。在我们的例子中，驱动程序名称是 MariaDB。第三部分是数据库名称。在本教程中，我们一直使用 testdb。

$username和 $password 是进行身份验证所需的用户名称和密码。最后一个参数是对哈希的引用，我们可以在其中设置属性来更改连接的默认设置。例如，RaiseError属性可用于强制错误引发异常，而不是返回错误代码。HandleError属性可用于提供一个在发生错误时调用的子程序。AutoCommit属性设置或取消自动提交模式。

$DBI::errstr是一个DBI动态属性，它返回本地数据库引擎的错误消息。如果连接失败，将显示此消息并中止脚本。

Perl DBI ping

ping方法尝试确定数据库服务器是否处于活动状态。它会以“合理有效的方式”尝试连接到数据库。

pinging.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';

my $dbh = DBI->connect($dsn, $user, $password)
    or die "failed to connect to the database $DBI::errstr";

if ($dbh->ping) {

    say 'database is up';
} else {

    say 'database is down';
}

$dbh->disconnect;

该示例使用 ping 方法检查数据库服务器是否正在运行。

my $dsn = "dbi:MariaDB:";

我们指定了数据源名称。我们不指定数据库名称，因为在此示例中我们只检查服务器的可用性。

my $dbh = DBI->connect($dsn, $user, $password)
     or die "failed to connect to the database $DBI::errstr";

connect方法建立到请求数据源的数据库连接或会话。如果连接成功，它将返回一个数据库句柄对象。最后，我们调用disconnect来终止连接。如果失败，它返回undef并设置$DBI::err和$DBI::errstr。我们使用die检查返回的值。

第一个参数是数据源名称。在该字符串中，我们指定数据库驱动程序和数据库名称。第二个参数是用户名。第三个参数是用户密码。

Perl DBI get_info

get_info方法返回数据库的详细信息。

version.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';

my $dbh = DBI->connect($dsn, $user, $password)
    or die "failed to connect to the database $DBI::errstr";

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

$dbh->disconnect;

该示例打印数据库服务器的名称和版本。

use DBI::Const::GetInfoType;

use语句导入一个 %GetInfoType 哈希，该哈希将GetInfo类型代码的名称映射到其对应的数值。

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

我们获取数据库服务器的名称和版本。

$ ./version.pl 
MariaDB
10.03.2400

下一个示例连接到PostgreSQL数据库并获取其版本。

$ sudo apt install postgresql-server-dev-13

在安装驱动程序之前，我们需要安装PostgreSQL开发文件。

$ sudo cpanm DBD::Pg

我们安装 DBD::Pg 驱动程序。

version2.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $dsn = "dbi:Pg:dbname=testdb";
my $user = "user7";
my $password = 's$cret';

my $dbh = DBI->connect($dsn, $user, $password)
    or die "failed to connect to the database $DBI::errstr";

say $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $dbh->get_info($GetInfoType{SQL_DBMS_VER});

$dbh->disconnect;

PostgreSQL的驱动程序名称是 Pg。与 MariaDB 驱动程序不同，Pg 在其数据源中需要数据库名称。

$ ./version2.pl 
PostgreSQL
11.00.0500

Perl DBI DBI_DSN

如果DSN参数为 undef 或为空，DBI会检查 DBI_DSN 环境变量。

dsn_env.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

my $user = "user7";
my $password = 's$cret';

my $dbh = DBI->connect('', $user, $password)
    or die "failed to connect to the database $DBI::errstr";

my $db_server_name = $dbh->get_info($GetInfoType{SQL_DBMS_NAME});
say $db_server_name;

在该示例中，我们将DSN设置为空字符串。数据源详细信息从 DBI_DSN 环境变量中检索。

$ export DBI_DSN=dbi:MariaDB:
$ ./dsn_env.pl 
MariaDB

$ export DBI_DSN=dbi:Pg:dbname=testdb
$ ./dsn_env.pl 
PostgreSQL

Perl DBI获取单个字段

在下一个示例中，我们使用 SELECT VERSION() 语句确定数据库的版本。此SQL语句是MariaDB特有的。该语句返回单个数据单元。

mariadb_select_version.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attr = (PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attr)
    or die "failed to connect to the database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT VERSION()")
    or die "failed to prepare statement $DBI::errstr";

$sth->execute or die "failed to execute statement $DBI::errstr";

my $row = $sth->fetch or die "failed to fetch data $DBI::errstr";

say $row->[0];

$dbh->disconnect;

该示例执行一个SQL语句，该语句返回MariaDB数据库的版本。默认情况下，每个DBI方法都返回一个错误代码；我们使用 die 方法检查代码。

my $dsn = "dbi:MariaDB:";

执行 SELECT VERSION() 语句时，不需要数据库名称。

my %attr = (PrintError => 0);

PrintError属性可用于强制错误生成警告（使用warn），并以默认方式返回错误代码。我们将其设置为0以避免重复的错误消息。（我们使用die生成自己的错误）。

my $dbh = DBI->connect($dsn, $user, $password, \%attr)
     or die "failed to connect to the database: $DBI::errstr";

连接属性作为第四个（可选）参数传递给connect。

my $sth = $dbh->prepare("SELECT VERSION()")
     or die "failed to prepare statement $DBI::errstr";

$sth->execute or die "failed to execute statement $DBI::errstr";

prepare方法准备SQL语句以供以后执行。execute方法执行SQL语句。

my $row = $sth->fetch or die "failed to fetch data $DBI::errstr";

数据通过fetch检索；它获取下一行数据并返回一个数组的引用。

say $row->[0];

我们打印行中的第一个字段，其中包含数据库的版本。Perl数组解引用语法用于获取字段。

$dbh->disconnect;

我们关闭与数据库的连接。

$ ./mariadb_select_version.pl
10.3.24-MariaDB-2

创建表

do方法执行SQL语句。它将两个方法调用：prepare和execute合并为一个单独的调用。do方法用于非SELECT语句。

create_cars.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

$dbh->do("DROP TABLE IF EXISTS cars");
$dbh->do("CREATE TABLE cars(id INT PRIMARY KEY, name VARCHAR(255), price INT)");
$dbh->do("INSERT INTO cars VALUES(1,'Audi',52642)");
$dbh->do("INSERT INTO cars VALUES(2,'Mercedes',57127)");
$dbh->do("INSERT INTO cars VALUES(3,'Skoda',9000)");
$dbh->do("INSERT INTO cars VALUES(4,'Volvo',29000)");
$dbh->do("INSERT INTO cars VALUES(5,'Bentley',350000)");
$dbh->do("INSERT INTO cars VALUES(6,'Citroen',21000)");
$dbh->do("INSERT INTO cars VALUES(7,'Hummer',41400)");
$dbh->do("INSERT INTO cars VALUES(8,'Volkswagen',21600)");

$dbh->disconnect;

say 'table cars created';

该示例创建了cars表。它包含非SELECT语句——所有语句都通过do方法执行。

my %attr = (RaiseError => 1, PrintError => 0);

将 RaiseError 设置为1会强制错误引发异常，而不是返回错误代码。因此，我们不为每个语句使用die函数。现在，在发生错误时，脚本将被终止，错误将打印到控制台。

$ ./create_cars.pl 
table cars created
     
MariaDB [testdb]> SELECT * FROM cars;
+----+------------+--------+
| id | name       | price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.000 sec)

我们执行程序并检查表中的数据。

Perl DBI预编译语句

预编译语句使用占位符，而不是直接将值写入语句。预编译语句提高了安全性和性能。

my $id = shift;

my $query1 = "SELECT * FROM cars WHERE id=$id";
my $query2 = "SELECT * FROM cars WHERE id=" . $id;
my $query3 = sprintf "SELECT * FROM cars WHERE id=%s", $id;

这些SQL语句容易受到SQL注入攻击。绝不应使用字符串插值、格式化或连接来构造SQL查询。为避免SQL注入攻击，我们必须使用预编译语句，在其中将变量绑定到预定义的占位符，如下例所示。

prepared.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $id1 = shift || 1;
my $id2 = shift || 2;
my $id3 = shift || 3;

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");
$sth->execute($id1, $id2, $id3);

while (my($id, $name, $price) = $sth->fetchrow()) {
    
    print "$id $name $price\n";
}

$dbh->disconnect;

该示例从cars表中选择三行。

my $dsn = "dbi:MariaDB:dbname=testdb";

我们将要创建一个新的数据库表，因此我们需要在连接时提供一个数据库名称。在我们的例子中，数据库名称是testdb，在数据源中用dbname指定。

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");

我们为将要绑定的值使用了三个占位符；占位符用问号（?）表示。

$sth->execute($id1, $id2, $id3);

传递给execute方法的参数被绑定到占位符。

$ ./prepared.pl 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
$ ./prepared.pl 2 4 7
2 Mercedes 57127
4 Volvo 29000
7 Hummer 41400

预编译语句的另一种语法是使用bind_param方法。

prepared2.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attr = (RaiseError => 1, PrintError => 0);

my $id1 = shift || 1;
my $id2 = shift || 2;
my $id3 = shift || 3;

my $dbh = DBI->connect($dsn, $user, $password, \%attr);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id IN (?, ?, ?)");

$sth->bind_param(1, $id1);
$sth->bind_param(2, $id2);
$sth->bind_param(3, $id3);

$sth->execute;

while (my($id, $name, $price) = $sth->fetchrow) {

    say "$id $name $price";
}

$dbh->disconnect;

在此示例中，参数不是通过传递给execute方法来绑定的，而是通过bind_param方法绑定的。

Perl DBI错误处理

在某些情况下，适当的错误处理可能比实际数据处理花费更多的时间和精力。Perl DBI提供的自动错误处理对于命令行程序来说已经足够了，前提是它们是由有知识的用户运行的。

但是，当我们处理关键数据、7x24运行的系统或同时被数千用户使用的应用程序时，对错误处理的要求会大不相同。

总的来说，错误从不直接报告给用户，而是写入日志文件。错误永远不应被忽略，即使它们被认为是无害的。用户名和密码不写在程序中，而是从配置文件或环境变量中检索。

方法名描述
$h->err返回最后调用的驱动程序方法的本地数据库引擎错误代码。
$h->errstr返回最后调用的DBI方法的本地数据库引擎错误消息。
$h->state以标准的 SQLSTATE 五字符格式返回一个状态码。

上面三个方法处理错误消息。

DBI动态属性描述
$DBI::err等同于 $h->err
$DBI::errstr等同于 $h->errstr
$DBI::state等同于 $h->state

第二个表列出了与错误处理相关的DBI动态属性。这些属性的生命周期很短。它们应该在可能导致错误的那个方法之后立即使用。

默认情况下，Perl DBI使用warn函数报告错误。这由 PrintError 选项控制，该选项设置为1。发生错误时，它实际上会执行 warn("$class $method failed: $DBI::errstr)，其中 $class 是驱动程序类，$method 是失败的方法名。

RaiseError属性可用于强制错误引发异常，而不是以默认方式返回错误代码。它默认为0。当设置为1时，任何导致错误的都会导致DBI执行 die("$class $method failed: $DBI::errstr")。

当前最佳实践是将RaiseError设置为1，并使用eval方法或Try::Tiny模块来处理错误。

Perl DBI错误子例程

通过HandleError连接句柄属性，我们可以设置一个指向子例程的引用，当检测到错误时会调用该子例程。该子例程使用三个参数调用：RaiseError和PrintError将使用的错误消息字符串、正在使用的DBI句柄，以及失败的方法返回的第一个值（通常是undef）。

如果子例程返回一个假值，则会正常检查并处理RaiseError或PrintError属性。

AutoCommit选项控制是自动提交每个语句，还是在事务中运行语句。然后，每个事务都以commit或rollback方法结束。

注意：在DBI中，AutoCommit选项默认设置为1。

begin_work函数启用事务（通过关闭AutoCommit），直到下次调用commit或rollback。在下次commit或rollback之后，AutoCommit将自动重新开启。

err_sub.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 1, AutoCommit => 0, 
    HandleError => \&handle_error);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

$dbh->do("UPDATE cars SET price=52000 WHERE id=1");
$dbh->do("UPDATE car SET price=22000 WHERE id=8");

$dbh->commit;
$dbh->disconnect;

sub handle_error {
    
    $dbh->rollback; 

    my $error = shift;

    say "An error occurred in the script";
    say "Message: $error";

    return 1;
}

我们有两个SQL UPDATE语句；第二个语句有一个错误：表名不正确。我们在handle_error子例程中处理错误。

my %attrs = (RaiseError => 1, PrintError => 0, AutoCommit => 0, 
     HandleError => \&handle_error);

HandleError属性提供了一个指向handle_error子例程的引用，当检测到错误时会调用该子例程。AutoCommit被关闭，这意味着我们使用事务。

$dbh->do("UPDATE car SET price=22000 WHERE id=8");

SQL语句中存在错误：没有cars表。

sub handle_error {
    
    $dbh->rollback; 

    my $error = shift;

    say "An error occurred in the script";
    say "Message: $error";

    return 1;
}

这是handle_error子例程。我们打印错误消息并返回1。如果我们返回0，则会出现其他错误消息。返回1会抑制与RaiseError属性相关的错误消息。

$ ./err_sub.pl 
An error occurred in the script
Message: DBD::MariaDB::db do failed: Table 'testdb.car' doesn't exist

程序因错误而失败。由于两个语句都在单个事务中运行，因此没有行被更新。

Perl DBI使用eval进行错误处理

eval函数执行一些Perl代码，捕获可能的错误而不使程序崩溃。

eval_error.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use DBI qw(:sql_types);

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

eval {

    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");

    $dbh->commit;

} or do {

    warn "Database error: $DBI::errstr\n";
    $dbh->rollback;
};

$dbh->disconnect;

使用begin_work，我们将两个更新语句放入一个事务中。

eval {

    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");

    $dbh->commit;

} or do {
...

语句被放入eval块中，该块捕获可能的错误并防止脚本崩溃。

} or do {

    warn "Database error: $DBI::errstr\n";
    $dbh->rollback;
};

当eval失败时，我们回滚事务。

Perl DBI使用Try::Tiny进行错误处理

Try::Tiny模块允许我们使用try/catch/finally块来处理错误。这是在C#或Java等语言中处理异常的传统方式。

该模块将$@复制到$_。

catching.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;
use Try::Tiny;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

try {
    $dbh->begin_work;

    $dbh->do("UPDATE cars SET price=52000 WHERE id=1");
    $dbh->do("UPDATE cars SET price=22000 WHERE id=8");
    
    $dbh->commit;

} catch {

    $dbh->rollback;
    warn "failed to update data\n$_";

} finally {

    $dbh->disconnect;
}

容易出错的代码被放在try块中。如果发生错误，则执行catch块。finally块始终执行，无论是否有错误。在那里我们放置清理资源的代码。

Perl DBI获取数据

数据通过SELECT语句从数据库检索。在DBI中，我们首先使用prepare方法准备SQL语句。SQL字符串被发送到数据库引擎，数据库引擎会检查语句的有效性、语法，在某些数据库中还会检查用户执行特定查询的权限。如果一切正常，则返回语句句柄的引用。

下一步是调用execute方法。该方法在数据库中执行查询。此时，结果仍保留在数据库中。Perl脚本尚未包含数据。对于非SELECT语句，如果已知，execute方法将返回受影响的行数。在最后一步，数据从数据库中获取。数据逐行提取并填充到Perl数据结构中。

Perl DBI有几种从数据库表中获取数据的方法。

方法描述
fetchrow_arrayref获取下一行数据并返回数组引用。
fetchrow_array获取下一行数据并作为列表返回。
fetchrow_hashref获取下一行数据并返回哈希引用。
fetchall_arrayref获取所有数据并返回数组引用，其中每行有一个引用。
fetch该方法是fetchrow_arrayref的别名。
fetchrow该方法是fetchrow_array的别名。

在SQL语句准备好并执行后，我们调用其中一个可用的fetch方法。

方法描述
selectrow_arrayref将prepare、execute和fetchrow_arrayref合并为一个单独的调用。
selectrow_hashref将prepare、execute和fetchrow_hashref合并为一个单独的调用。
selectrow_array将prepare、execute和fetchrow_array合并为一个单独的调用。
selectall_arrayref将prepare、execute和fetchall_arrayref合并为一个单独的调用。
selectall_hashref将prepare、execute和fetchall_hashref合并为一个单独的调用。
selectcol_arrayref将prepare、execute和从所有行中获取一列合并为一个单独的调用。

在第二个表中，我们列出了一些实用方法，它们将三个方法合并为一个调用。它们是便利方法。

Perl DBI fetchrow

fetchrow方法获取下一行数据并将其作为列表返回。

fetch_single_row.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:mysql:dbname=mydb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 1);

my $id = shift || 1;

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars WHERE id=?");
$sth->execute($id);
      
my ($id, $name, $price) = $sth->fetchrow;
say "$id $name $price";

my $n_fields = $sth->{NUM_OF_FIELDS};
say "query contains $n_fields field(s)";

my $n_rows = $sth->rows;
say "We have selected $n_rows row(s)";

$dbh->disconnect;

该示例获取并打印cars表中的一行。此外，我们还打印了检索到的字段数和行数。

my ($id, $name, $price) = $sth->fetchrow;
say "$id $name $price";

我们将行作为列表获取，并将其分配给三个变量。

my $n_fields = $sth->{NUM_OF_FIELDS};

我们使用 NUM_OF_FIELDS 属性获取检索到的字段数。

my $n_rows = $sth->rows;

我们使用 rows 方法获取检索到的行数。

$ ./fetch_single_row.pl 3
3 Skoda 9000
query contains 3 field(s)
We have selected 1 row(s)

在下一个示例中，我们获取表中的所有行。

fetch_all_rows.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars");
$sth->execute;

while (my($id, $name, $price) = $sth->fetchrow) {
    
    say "$id $name $price";
}

$dbh->disconnect;

fetchrow被放在while循环中以获取所有行。当没有更多行可读时，它返回false。

while (my @row = $sth->fetchrow) {

     say "$row[0] $row[1] $row[2]";
}

这是使用fetchrow获取行的另一种语法。

$ ./fetch_all_rows.pl 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

Perl DBI fetchrow_hashref

fetchrow_hashref方法获取下一行作为哈希表引用。

fetch_all_rows2.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars");
$sth->execute;

while (my $row = $sth->fetchrow_hashref) {

    say "$row->{id} $row->{name} $row->{price}";
}

$dbh->disconnect;

该示例使用fetchrow_hashref检索所有行。

while (my $row = $sth->fetchrow_hashref) {

     say "$row->{id} $row->{name} $row->{price}";
}

我们将每一行作为哈希引用获取。我们使用箭头运算符解引用行的字段。

Perl DBI select_row_array

select_all_array是便利方法之一；它将prepare、execute和fetchrow_array合并为一个单独的调用。

select_all_array.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my @rows = $dbh->selectall_array("SELECT * FROM cars");

foreach my $row (@rows) {
    
    my ($id, $name, $price) = @$row;
    say "$id $name $price";
}

$dbh->disconnect;

该示例使用select_all_array方法从cars表中检索所有行。

Perl DBI将列绑定到变量

SELECT语句的列字段可以通过bind_col和bind_columns方法绑定到变量。

bind_col.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my ($id, $name, $price);

my $sth = $dbh->prepare("SELECT * FROM cars");
$sth->execute;

$sth->bind_col(1, \$id);
$sth->bind_col(2, \$name);
$sth->bind_col(3, \$price);

while ($sth->fetch) {

    say "$id $name $price";
}

$dbh->disconnect;

在该示例中，我们使用bind_col将三个列字段绑定到$id、$name和$price变量。

我们可以使用bind_columns一次性绑定所有字段。

bind_columns.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars");
$sth->execute;

$sth->bind_columns(\my($id, $name, $price));

while ($sth->fetch) {

    say "$id $name $price";
}

$dbh->disconnect;

bind_columns为SELECT语句的每一列调用bin_col方法。它以变量引用的列表作为参数。变量引用的数量应与SELECT语句中的列数匹配。

Perl DBI多查询

MariaDB允许在一个查询中执行多个SQL语句。该功能默认是关闭的；我们需要使用 mariadb_multi_statements 选项启用它。

multiple_queries.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb;mariadb_multi_statements=1";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $query = qq{
    SELECT * FROM cars WHERE id=1;
    SELECT * FROM cars WHERE id=2;
    SELECT * FROM cars WHERE id=3;
};

my $sth = $dbh->prepare($query);
$sth->execute;

do  {

    while (my @row = $sth->fetchrow)
    {
        say "@row";
    }

} while ($sth->more_results);

$dbh->disconnect;

在该示例中，我们在一个查询中执行了三个SELECT语句。

do  {

     while (my @row = $sth->fetchrow)
     {
          say "@row";
     }
     
} while ($sth->more_results);

数据在do/while循环中检索。我们使用more_results方法检查是否有其他结果集。

Perl DBI插入图像

在下一个示例中，我们将图像插入表中。

CREATE TABLE images(id INT PRIMARY KEY AUTO_INCREMENT, data MEDIUMBLOB);

我们创建了images表。对于图像，我们使用了MEDIUMBLOB数据类型，它存储二进制对象。

insert_image.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $filename = 'sid.jpg';

open(my $fh,  $filename)
  or die "failed not open file $filename $!";

my ($img_data, $buff);

while (read $fh, $buff, 1024) {

    $img_data .= $buff;
}

my $stm = $dbh->prepare("INSERT INTO images(data) VALUES (?)");
$stm->bind_param(1, $img_data, DBI::SQL_BLOB);
$stm->execute;

$fh->close;
$dbh->disconnect;

我们将图像数据从文件读取并写入images表中。

$stm->bind_param(1, $img_data, DBI::SQL_BLOB);

我们在bind_param方法中告知DBI我们的数据是 DBI::SQL_BLOB 类型。

Perl DBI读取图像

下一个示例从数据库表中读取图像并将其存储到文件中。

read_image.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $stm = $dbh->prepare("SELECT data FROM images WHERE id=1");
$stm->execute;

my $img_data = $stm->fetch;

my $filename = 'sid2.jpg';

open(my $fh, '>', $filename)
  or die "failed writing to file $!";

print $fh @$img_data;

$fh->close;
$dbh->disconnect;

该示例使用SELECT语句检索二进制数据。

Perl DBI元数据

元数据是数据库中关于数据的信息。数据库中的元数据包含我们存储数据的表和列的信息。SQL语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。

方法名描述
column_info提供有关列的信息
table_info提供有关表的信息
primary_key_info提供有关表中主键的信息
foreign_key_info提供有关表中外键的信息

上表列出了用于检索元数据的四个Perl DBI方法。

pripary_key_info.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:mysql:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->primary_key_info(undef, "testdb", "cars");
my @ary = $sth->fetchrow;

say "@ary";

$dbh->disconnect;

primary_key_info返回一个活动语句句柄，可用于获取关于构成表主键的列的信息。

$ ./primary_key_info.pl 
testdb cars id 1 PRIMARY

table_info方法返回一个活动语句句柄，可用于获取关于数据库中存在的表和视图的信息。

$sth = $dbh->table_info($catalog, $schema, $table, $type);

参数可以使用 % 字符接受搜索模式。

list_tables.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->table_info('', 'testdb', '', 'TABLE');

while (my $row = $sth->fetchrow_hashref) {

  say $row->{TABLE_NAME};
}

$dbh->disconnect;

我们使用table_info获取testdb数据库中的所有表。

my $sth = $dbh->table_info('', 'testdb', '', 'TABLE');

我们指定我们想要testdb数据库中的表名。

$ ./list_tables.pl 
cars
cities
countries
friends
images
writers
list_databases.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->table_info('', '%', '', '%');

while (my $row = $sth->fetchrow_hashref) {

  say $row->{TABLE_SCHEM};
}

$dbh->disconnect;

该示例列出了所有数据库名称。

$ ./list_databases.pl 
information_schema
mydb
mysql
performance_schema
testdb

last_insert_id在可能的情况下返回标识刚插入行的值。通常，这将是数据库服务器分配给具有auto_increment或serial类型的列的值。如果驱动程序不支持该方法或无法确定该值，则返回undef。

last_insert_id.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

$dbh->do("DROP TABLE IF EXISTS friends");
$dbh->do("CREATE TABLE friends(id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))");
$dbh->do("INSERT INTO friends(name) VALUES ('Tom')");
$dbh->do("INSERT INTO friends(name) VALUES ('Rebecca')");
$dbh->do("INSERT INTO friends(name) VALUES ('Jim')");
$dbh->do("INSERT INTO friends(name) VALUES ('Robert')");
$dbh->do("INSERT INTO friends(name) VALUES ('Julian')");
    
my $id = $dbh->last_insert_id('', '', 'friends', '');
say "The last Id of the inserted row is $id";

$dbh->disconnect;

我们创建一个新表。然后我们确定最后插入的id。

$ ./last_insert_id.pl 
The last Id of the inserted row is 5

在下面的示例中，我们使用列名打印cars表中的所有行。

column_names.pl 
#!/usr/bin/perl

use 5.30.0;
use warnings;
use DBI;

my $dsn = "dbi:MariaDB:dbname=testdb";
my $user = "user7";
my $password = 's$cret';
my %attrs = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect($dsn, $user, $password, \%attrs);

my $sth = $dbh->prepare("SELECT * FROM cars");  
$sth->execute;

my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;

while (my $row = $sth->fetchrow_hashref) {

    printf "%d %-10s %d\n", $row->{id}, $row->{name}, $row->{price};
}

$dbh->disconnect;

记录与列名对齐。

my $headers = $sth->{NAME};

NAME语句句柄属性返回一个包含每个列的字段名称的数组的引用。

$ ./column_names.pl 
id name       price
 1 Audi       52000
 2 Mercedes   57127
 3 Skoda      9000
 4 Volvo      29000
 5 Bentley    350000
 6 Citroen    21000
 7 Hummer     41400
 8 Volkswagen 22000

在本文中，我们使用了Perl DBI模块。

作者

我叫Jan Bodnar，我是一名热情的程序员，拥有丰富的编程经验。自2007年以来，我一直在撰写编程文章。至今，我已撰写了1400多篇文章和8本电子书。我在编程教学方面有十多年的经验。

列出 所有 Perl 教程。