Perl 数据库访问

Perl - 使用 DBI 进行数据库访问

Perl 通过 DBI（Database Interface，数据库接口）模块提供了一种强大且标准化的方式与数据库交互。DBI 充当一个抽象层（abstraction layer），使您的 Perl 代码在很大程度上独立于您使用的特定数据库系统（例如 MySQL, PostgreSQL, Oracle, SQLite）。您还需要一个数据库特定的驱动程序（DBD）模块，例如用于 MySQL 的 DBD::mysql。

DBI 模块为数据库操作提供了一组一致的方法、变量和约定。

DBI 应用程序的架构

DBI 架构包含三个主要组件：

• 您的 Perl 脚本： 使用 DBI API 发送 SQL 命令并接收结果。
• DBI (Database Interface)： 通用接口模块。它将调用分派给适当的 DBD。
• DBD (Database Driver)： 您选择的数据库的特定驱动程序（例如用于 PostgreSQL 的 DBD::Pg，用于 SQLite 的 DBD::SQLite）。此模块负责与数据库服务器的实际通信。

这种分层方法意味着您通常可以通过更改连接字符串并确保安装了相关的 DBD 模块来切换数据库，而对核心 Perl 逻辑的改动最小。

符号和约定

在 DBI 文档和示例中，您经常会看到这些变量命名约定：

$dsn    # Data Source Name (string defining how to connect)
$dbh    # Database Handle (object representing the connection)
$sth    # Statement Handle (object representing a prepared SQL statement)
$rv     # Return Value (often an integer, e.g., number of rows)
$rc     # Return Code (boolean status, true for success)
@row    # Array to hold a fetched row of data
%attr   # Hash reference for attributes/options

数据库连接

要连接到数据库，您使用 DBI->connect() 方法。确保安装了 DBI 和适当的 DBD::* 模块。在此示例中，我们假设使用 MySQL。

先决条件（示例场景）：

• 存在一个名为 TESTDB 的 MySQL 数据库。
• TESTDB 中存在一个名为 EMPLOYEES 的表，其列包括：ID (INT, PK, AutoIncrement), FIRST_NAME (VARCHAR), LAST_NAME (VARCHAR), AGE (INT), SEX (CHAR(1)), INCOME (DECIMAL)。
• 用户 testuser 的密码为 test123，并对 TESTDB 具有权限。

连接到 MySQL：

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

my $driver   = "mysql";
my $database = "TESTDB";
my $host     = "localhost"; # 或您的数据库主机地址
my $port     = "3306";    # 或您的数据库端口

my $dsn = "DBI:$driver:database=$database;host=$host;port=$port";
my $userid = "testuser";
my $password = "test123";

# 连接时使用错误处理属性
my $dbh = DBI->connect($dsn, $userid, $password, {
    RaiseError => 1, # 在错误时自动中止程序
    PrintError => 0, # 不向 STDERR 打印警告（RaiseError 处理错误）
    AutoCommit => 1  # 自动提交每条语句（可在事务中关闭）
}) or die "数据库连接失败: $DBI::errstr";

print "成功连接到数据库！\n";

# ... 数据库操作 ...

$dbh->disconnect();
print "已从数据库断开连接。\n";

RaiseError => 1 强烈建议使用，因为它通过在 DBI 方法失败时自动中止程序来简化错误处理。$DBI::errstr 包含错误消息。

INSERT 操作

要插入数据，您通常会 prepare（准备）一条 SQL INSERT 语句，然后 execute（执行）它。

步骤：

• 使用 prepare() 准备 SQL INSERT 语句。使用占位符（?）表示值，以防止 SQL 注入。
• 使用 execute() 执行准备好的语句，并传递占位符的值。
• 如果暂时不再使用该语句句柄，可以选择对其调用 finish()（尽管对于简单的 INSERT 操作并且启用了 RaiseError 时，通常不是严格必要的）。

# 假设 $dbh 是一个有效、已连接的数据库句柄

my $sql_insert = q{
    INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
    VALUES (?, ?, ?, ?, ?)
};
my $sth = $dbh->prepare($sql_insert);

# 插入一条记录
my $first_name = 'John';
my $last_name  = 'Doe';
my $age        = 30;
my $sex        = 'M';
my $income     = 50000;

$sth->execute($first_name, $last_name, $age, $sex, $income);
print "记录已插入 (John Doe)。受影响行数: ", $sth->rows, "\n";

# 插入另一条记录
$sth->execute('Jane', 'Smith', 28, 'F', 60000);
print "记录已插入 (Jane Smith)。受影响行数: ", $sth->rows, "\n";

# $sth->finish(); # 如果暂时不再使用 $sth，这是个好习惯

$sth->rows 返回非 SELECT 语句（如 INSERT, UPDATE, DELETE）上次 execute() 操作所影响的行数。

READ (SELECT) 操作

要检索数据，您需要准备一条 SELECT 语句，执行它，然后获取结果。

步骤：

• 使用 prepare() 准备 SQL SELECT 查询。
• 使用 execute() 执行查询（如果需要绑定值则传递）。
• 使用 fetchrow_array()、fetchrow_hashref() 或 fetchall_arrayref() 等方法逐行获取结果。
• 获取完毕后，对语句句柄调用 finish()。

# 假设 $dbh 已连接

my $min_age = 25;
my $sql_select = q{
    SELECT FIRST_NAME, LAST_NAME, AGE
    FROM EMPLOYEES WHERE AGE > ? ORDER BY LAST_NAME
};
my $sth = $dbh->prepare($sql_select);

$sth->execute($min_age);

print "年龄大于 $min_age 的员工:\n";
# 按每行的值数组获取
while (my @row = $sth->fetchrow_array()) {
    my ($fname, $lname, $age_val) = @row;
    print " - $fname $lname, 年龄: $age_val\n";
}
# 另一种方式: 按每行的哈希引用获取
# $sth->execute($min_age); # 如果想再次获取，需要重新执行
# while (my $row_hashref = $sth->fetchrow_hashref()) {
#    print " - $row_hashref->{FIRST_NAME} $row_hashref->{LAST_NAME}, 年龄: $row_hashref->{AGE}\n";
# }

$sth->finish();

UPDATE 操作

更新记录的模式与 INSERT 类似。

# 假设 $dbh 已连接

my $new_income_for_males = 55000;
my $target_sex = 'M';

my $sql_update = q{
    UPDATE EMPLOYEES SET INCOME = ? WHERE SEX = ?
};
my $sth = $dbh->prepare($sql_update);

$sth->execute($new_income_for_males, $target_sex);
print "已更新性别为 '$target_sex' 的员工收入。受影响行数: ", $sth->rows, "\n";

$sth->finish();

DELETE 操作

删除记录也使用 prepare 和 execute。

# 假设 $dbh 已连接

my $age_to_delete = 30;

my $sql_delete = q{
    DELETE FROM EMPLOYEES WHERE AGE = ?
};
my $sth = $dbh->prepare($sql_delete);

$sth->execute($age_to_delete);
print "已删除年龄为 $age_to_delete 的员工。受影响行数: ", $sth->rows, "\n";

$sth->finish();

为非 SELECT 语句使用 $dbh->do()

对于不需要重用准备好的语句或复杂绑定值的简单 INSERT, UPDATE 或 DELETE 语句（尽管它支持简单的绑定值），您可以使用 $dbh->do() 作为快捷方式。它在一个调用中完成准备和执行。

# 假设 $dbh 已连接
my $target_id_to_delete = 100; # 示例 ID

my $rows_affected = $dbh->do('DELETE FROM EMPLOYEES WHERE ID = ?', undef, $target_id_to_delete);
if (defined $rows_affected) {
    print "使用 do() 删除。受影响行数: $rows_affected\n";
} else {
    warn "使用 do() 删除失败: ", $dbh->errstr, "\n";
}

# 不使用占位符 (如果值是动态的且未经净化，安全性较低):
# $rows_affected = $dbh->do("DELETE FROM EMPLOYEES WHERE AGE = 30");

$dbh->do() 返回受影响的行数，如果发生错误（并且 RaiseError 为关闭），则返回 undef。如果 RaiseError 为开启，它将在错误时中止程序。

事务 (COMMIT 和 ROLLBACK)

事务允许您将多个 SQL 语句组合在一起。要么所有语句都成功（COMMIT，提交），要么所有语句都失败且更改被撤销（ROLLBACK，回滚）。要使用事务，请在连接时设置 AutoCommit => 0 或通过 $dbh->{AutoCommit} = 0; 设置。

# 假设 $dbh 已连接，AutoCommit => 0，或设置了 $dbh->{AutoCommit} = 0;

# 如果连接时未设置，则禁用 AutoCommit
# $dbh->{AutoCommit} = 0; # 或使用 $dbh->begin_work;

try {
    # 隐式开始事务，或者对于某些数据库使用 $dbh->begin_work 显式开始
    my $sth1 = $dbh->prepare('UPDATE EMPLOYEES SET INCOME = INCOME * 1.10 WHERE SEX = ?');
    $sth1->execute('F');

    my $sth2 = $dbh->prepare('INSERT INTO AUDIT_LOG (MESSAGE) VALUES (?)');
    $sth2->execute('已更新女性员工工资');

    $dbh->commit; # 最终确定更改
    print "事务已提交。\n";
} catch {
    warn "事务失败: $_\n  正在回滚更改...\n";
    $dbh->rollback; # 撤销更改
    print "事务已回滚。\n";
};

# 如果后续工作不需要事务，可重新启用 AutoCommit
# $dbh->{AutoCommit} = 1;

try/catch 块在较旧版本的 Perl 中需要 Try::Tiny 或类似的模块来进行健壮的异常处理。在现代 Perl (5.34+) 中，可以使用实验性的 try/catch 语法（use feature 'try'）。如果没有它，可以使用 eval { ... }; if ($@) { ... }。

$dbh->begin_work 是一种更显式的开始事务的方式，如果驱动程序和数据库支持的话，它等同于关闭 AutoCommit。

断开数据库连接

使用 $dbh->disconnect() 关闭数据库连接。

$rc = $dbh->disconnect();
# warn $dbh->errstr if !$rc and $dbh->errstr; # 使用 RaiseError 时通常不需要

如果 AutoCommit 关闭且存在未提交的更改，disconnect 的行为取决于驱动程序（有些会提交，有些会回滚）。最好在断开连接前显式地 commit 或 rollback。

处理 NULL 值

Perl 的 undef 用于表示 SQL NULL 值，在插入或更新时使用。查询时，对于 NULL 列，将返回 undef。

$sth->execute('新员工', undef, 42, 'M', undef); # LAST_NAME 和 INCOME 是 NULL

重要提示：WHERE column = ? 并将 undef 绑定到 ? 将不会匹配 column 为 NULL 的行。请明确使用 WHERE column IS NULL 或 WHERE column IS NOT NULL。

my $age_filter = undef;
my $sql;
if (defined $age_filter) {
    $sql = 'SELECT * FROM EMPLOYEES WHERE AGE = ?';
    $sth = $dbh->prepare($sql);
    $sth->execute($age_filter);
} else {
    $sql = 'SELECT * FROM EMPLOYEES WHERE AGE IS NULL';
    $sth = $dbh->prepare($sql);
    $sth->execute(); # 不需要绑定值
}

DBI 实用方法

DBI->available_drivers(): 返回可用 DBD 驱动程序名称的列表。

my @drivers = DBI->available_drivers();
print "可用的驱动程序: @drivers\n";

DBI->data_sources($driver): 返回给定驱动程序可用的数据源（数据库）列表。

# my @sources = DBI->data_sources('mysql'); # 可能需要驱动程序特定的设置

$dbh->quote($value): 引用字符串文字以便安全地包含在 SQL 语句中。然而，强烈建议使用占位符 ? 来防止 SQL 注入。

# my $unsafe_name = "O'Malley";
# my $quoted_name = $dbh->quote($unsafe_name);
# my $sql = "SELECT * FROM users WHERE name = $quoted_name"; # 通常避免这种模式
# 相反，请使用: $dbh->prepare('SELECT * FROM users WHERE name = ?')->execute($unsafe_name);

错误处理方法（作用于句柄 $h，如 $dbh 或 $sth）

$h->err(): 返回本机数据库错误码。（如果 RaiseError 开启，较少需要）。

$h->errstr(): 返回本机数据库错误消息。（如果 RaiseError 开启，较少需要）。

$h->state(): 返回 SQLSTATE 错误码。

跟踪 DBI 活动

DBI 可以生成详细的跟踪信息用于调试。在 DBI 类、驱动程序句柄或数据库/语句句柄上调用 trace 方法。

DBI->trace(2); # 为所有句柄设置跟踪级别 2（SQL 和连接信息）
# $dbh->trace(1); # 为此特定数据库句柄设置跟踪级别 1
# 级别通常从 0（关闭）到 4 或更高（非常详细）。

请始终使用占位符（?），而不是直接将变量内插到 SQL 字符串中，以防止 SQL 注入漏洞。