Perl 数据库连接

DBI 基础

连接数据库

use DBI;

# 连接 MySQL
my $dbh = DBI->connect(
    "DBI:mysql:database=test;host=localhost",
    "username",
    "password",
    {
        RaiseError => 1,
        PrintError => 0,
        AutoCommit => 1
    }
) or die "Cannot connect: $DBI::errstr";

# 连接 PostgreSQL
my $dbh = DBI->connect(
    "DBI:Pg:dbname=test;host=localhost",
    "username",
    "password"
);

# 连接 SQLite
my $dbh = DBI->connect(
    "dbi:SQLite:dbname=test.db",
    "",
    ""
);

查询操作

执行简单查询

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test", "user", "pass");

# 准备 SQL
my $sth = $dbh->prepare("SELECT * FROM users");
$sth->execute();

# 获取所有行
my @rows = @{ $sth->fetchall_arrayref({}) };

foreach my $row (@rows) {
    print "$row->{name}, $row->{email}\n";
}

$sth->finish();
$dbh->disconnect();

使用占位符

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test", "user", "pass");

# 使用占位符防止 SQL 注入
my $sth = $dbh->prepare("SELECT * FROM users WHERE name = ?");
$sth->execute("Alice");

while (my $row = $sth->fetchrow_hashref) {
    print "$row->{name}, $row->{email}\n";
}

$sth->finish();

获取单行

# 获取单行（数组形式）
my ($name, $email) = $dbh->selectrow_array(
    "SELECT name, email FROM users WHERE id = ?",
    undef,
    1
);

# 获取单行（哈希形式）
my $row = $dbh->selectrow_hashref(
    "SELECT * FROM users WHERE id = ?",
    undef,
    1
);

插入操作

插入单条记录

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test", "user", "pass");

# 插入数据
my $sth = $dbh->prepare(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
);

$sth->execute("Alice", "alice@example.com", 25);
$sth->execute("Bob", "bob@example.com", 30);

$sth->finish();

# 获取最后插入的 ID
my $last_id = $dbh->{mysql_insertid};
print "Inserted ID: $last_id\n";

批量插入

my @users = (
    ["Charlie", "charlie@example.com", 35],
    ["David", "david@example.com", 28],
    ["Eve", "eve@example.com", 32]
);

my $sth = $dbh->prepare(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
);

foreach my $user (@users) {
    $sth->execute(@$user);
}

$sth->finish();

更新操作

更新记录

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test", "user", "pass");

# 更新单条记录
my $rows_affected = $dbh->do(
    "UPDATE users SET age = ? WHERE name = ?",
    undef,
    26,  # 新年龄
    "Alice"
);

print "Updated $rows_affected rows\n";

# 批量更新
$dbh->do("UPDATE users SET age = age + 1");

删除操作

删除记录

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=test", "user", "pass");

# 删除单条记录
my $rows_affected = $dbh->do(
    "DELETE FROM users WHERE id = ?",
    undef,
    5
);

print "Deleted $rows_affected rows\n";

# 批量删除
$dbh->do("DELETE FROM users WHERE age < 18");

事务处理

基本事务

use DBI;

my $dbh = DBI->connect(
    "DBI:mysql:database=test",
    "user",
    "pass",
    { AutoCommit => 0 }  # 禁用自动提交
);

eval {
    # 执行多个操作
    $dbh->do("INSERT INTO accounts (user_id, amount) VALUES (1, 100)");
    $dbh->do("UPDATE accounts SET amount = amount - 100 WHERE user_id = 2");
    
    # 提交事务
    $dbh->commit();
    print "Transaction committed\n";
};

if ($@) {
    # 回滚事务
    $dbh->rollback();
    print "Transaction rolled back: $@\n";
}

$dbh->disconnect();

保存点

$dbh->begin_work();

eval {
    $dbh->do("INSERT INTO users (name) VALUES ('Alice')");
    
    # 创建保存点
    $dbh->savepoint("sp1");
    
    $dbh->do("INSERT INTO users (name) VALUES ('Bob')");
    
    # 回滚到保存点
    $dbh->rollback_to("sp1");
    
    $dbh->commit();
};

高级查询

JOIN 查询

my $sth = $dbh->prepare(qq{
    SELECT u.name, o.order_date, o.total
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = ?
});

$sth->execute(1);

while (my $row = $sth->fetchrow_hashref) {
    printf "%s: %s - \$%.2f\n",
           $row->{name},
           $row->{order_date},
           $row->{total};
}

$sth->finish();

分页查询

my $page = 1;
my $per_page = 10;
my $offset = ($page - 1) * $per_page;

my $sth = $dbh->prepare(
    "SELECT * FROM users LIMIT ? OFFSET ?"
);
$sth->execute($per_page, $offset);

my @users = @{ $sth->fetchall_arrayref({}) };

$sth->finish();

聚合查询

# 计数
my ($count) = $dbh->selectrow_array(
    "SELECT COUNT(*) FROM users"
);

# 求和
my ($total) = $dbh->selectrow_array(
    "SELECT SUM(amount) FROM orders"
);

# 分组
my $sth = $dbh->prepare(
    "SELECT category, COUNT(*) as count FROM products GROUP BY category"
);
$sth->execute();

while (my $row = $sth->fetchrow_hashref) {
    print "$row->{category}: $row->{count}\n";
}

实践示例

示例 1：用户管理系统

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

package UserManager;
use DBI;

sub new {
    my ($class, $db_config) = @_;
    my $self = {
        dbh => DBI->connect(
            "DBI:mysql:database=$db_config->{database}",
            $db_config->{username},
            $db_config->{password}
        )
    };
    bless $self, $class;
    return $self;
}

sub add_user {
    my ($self, $name, $email, $age) = @_;
    my $sth = $self->{dbh}->prepare(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
    );
    $sth->execute($name, $email, $age);
    $sth->finish();
}

sub get_user {
    my ($self, $id) = @_;
    return $self->{dbh}->selectrow_hashref(
        "SELECT * FROM users WHERE id = ?",
        undef,
        $id
    );
}

sub update_user {
    my ($self, $id, %fields) = @_;
    my @set_clauses = map { "$_ = ?" } keys %fields;
    my $sql = "UPDATE users SET " . join(", ", @set_clauses) . " WHERE id = ?";
    
    $self->{dbh}->do($sql, undef, values %fields, $id);
}

sub delete_user {
    my ($self, $id) = @_;
    $self->{dbh}->do("DELETE FROM users WHERE id = ?", undef, $id);
}

1;

package main;
use UserManager;

my $manager = UserManager->new({
    database => "test",
    username => "user",
    password => "pass"
});

$manager->add_user("Alice", "alice@example.com", 25);
my $user = $manager->get_user(1);
print "User: $user->{name}\n";

示例 2：订单处理系统

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

package OrderProcessor;
use DBI;

sub new {
    my ($class, $db_config) = @_;
    my $self = {
        dbh => DBI->connect(
            "DBI:mysql:database=$db_config->{database}",
            $db_config->{username},
            $db_config->{password},
            { AutoCommit => 0 }
        )
    };
    bless $self, $class;
    return $self;
}

sub create_order {
    my ($self, $user_id, @items) = @_;
    
    eval {
        # 创建订单
        $self->{dbh}->do(
            "INSERT INTO orders (user_id, total) VALUES (?, 0)",
            undef,
            $user_id
        );
        
        my $order_id = $self->{dbh}->{mysql_insertid};
        my $total = 0;
        
        # 添加订单项
        my $sth = $self->{dbh}->prepare(
            "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)"
        );
        
        foreach my $item (@items) {
            $sth->execute(
                $order_id,
                $item->{product_id},
                $item->{quantity},
                $item->{price}
            );
            $total += $item->{quantity} * $item->{price};
        }
        
        # 更新订单总价
        $self->{dbh}->do(
            "UPDATE orders SET total = ? WHERE id = ?",
            undef,
            $total,
            $order_id
        );
        
        # 提交事务
        $self->{dbh}->commit();
        
        return $order_id;
    };
    
    if ($@) {
        $self->{dbh}->rollback();
        die "Order creation failed: $@";
    }
}

1;

package main;
use OrderProcessor;

my $processor = OrderProcessor->new({
    database => "test",
    username => "user",
    password => "pass"
});

my @items = (
    { product_id => 1, quantity => 2, price => 10.00 },
    { product_id => 2, quantity => 1, price => 20.00 }
);

my $order_id = $processor->create_order(1, @items);
print "Order created: $order_id\n";

小结

本章节学习了 Perl 的数据库连接：

1. ✅ DBI 基础
2. ✅ 查询操作
3. ✅ 插入操作
4. ✅ 更新操作
5. ✅ 删除操作
6. ✅ 事务处理
7. ✅ 高级查询
8. ✅ 实践示例

接下来，我们将学习 Perl 包和模块。