Linux Perl 操作 MySQL 完整指南：DBI/DBD::mysql 安裝、連線、CRUD、預處理、異常處理

環境準備

# 系統依賴
yum install -y perl-DBI perl-DBD-MySQL
# 或 Ubuntu
apt-get install -y libdbi-perl libdbd-mysql-perl

# CPAN 手動安裝（離線/特定版本）
cpan DBI
cpan DBD::mysql
# 若報 mysql_config not found
yum install -y mysql-devel  # 提供 mysql_config

連線配置

use strict;
use warnings;
use DBI;

my $dsn = "DBI:mysql:database=perl_test;host=localhost;port=3306;mysql_enable_utf8=1";
my $user = "root";
my $pass = "RootPass#2024";

my $dbh = DBI->connect($dsn, $user, $pass, {
  RaiseError => 1,           # 異常拋出 die
  PrintError => 0,           # 不自動 warn
  AutoCommit => 1,           # 自動提交
  mysql_auto_reconnect => 1, # 自動重連
}) or die "Connect failed: $DBI::errstr";

基礎 CRUD

查詢（預處理 + 參數綁定）

my $sth = $dbh->prepare("SELECT app_name, receivers FROM alert_rules WHERE status = ?");
$sth->execute(1);
while (my @row = $sth->fetchrow_array) {
  print "$row[0]: $row[1]\n";
}
$sth->finish();

插入

my $sth = $dbh->prepare("INSERT INTO alert_rules (app_name, receivers, status) VALUES (?, ?, ?)");
$sth->execute('new_app', 'admin@example.com', 1);
my $new_id = $dbh->{mysql_insertid};
print "Inserted ID: $new_id\n";

更新

my $sth = $dbh->prepare("UPDATE alert_rules SET receivers = ? WHERE app_name = ?");
$sth->execute('new_admin@example.com', 'new_app');

刪除

my $sth = $dbh->prepare("DELETE FROM alert_rules WHERE app_name = ?");
$sth->execute('old_app');

事務控制

$dbh->{AutoCommit} = 0;
eval {
  $dbh->do("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
  $dbh->do("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
  $dbh->commit();
  1;
} or do {
  my $err = $@ || $DBI::errstr;
  $dbh->rollback();
  die "Transaction failed: $err";
};
$dbh->{AutoCommit} = 1;

異常處理最佳實踐

# 連線級 RaiseError=1 自動 die
# 手工檢查
$sth->execute() or die "Execute failed: " . $sth->errstr();

# 批量操作錯誤收集
my @errors;
for my $item (@items) {
  my $sth = $dbh->prepare("INSERT INTO log (msg) VALUES (?)");
  unless ($sth->execute($item)) {
    push @errors, "Insert '$item' failed: " . $sth->errstr();
  }
}
die join("\n", @errors) if @errors;

實用工具函數

# 獲取表結構
my $cols = $dbh->column_info(undef, undef, 'alert_rules', '%');
while (my $ref = $cols->fetchrow_hashref) {
  print "$ref->{COLUMN_NAME} $ref->{TYPE_NAME}($ref->{COLUMN_SIZE})\n";
}

# 最後插入 ID
my $id = $dbh->{mysql_insertid};

# 影響行數
my $rows = $sth->rows;

關閉連線

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

完整範例腳本

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

my $dbh = DBI->connect(
  "DBI:mysql:database=perl_test;host=localhost;mysql_enable_utf8=1",
  "root", "RootPass#2024",
  { RaiseError => 1, AutoCommit => 1, mysql_auto_reconnect => 1 }
) or die $DBI::errstr;

# 查詢演示
my $sth = $dbh->prepare("SELECT app_name, receivers FROM alert_rules WHERE status = ?");
$sth->execute(1);
print "Active alerts:\n";
while (my ($app, $rcv) = $sth->fetchrow_array) {
  printf "  %-20s %s\n", $app, $rcv;
}
$sth->finish;

# 插入演示
$sth = $dbh->prepare("INSERT INTO alert_rules (app_name, receivers, status) VALUES (?, ?, ?)");
$sth->execute('perl_demo', 'perl@example.com', 1);
print "Inserted ID: " . $dbh->{mysql_insertid} . "\n";

$dbh->disconnect;