Perl - 第二十四章 | Perl 資料庫訪問

Perl 5 中我們可以使用 DBI 模塊來連接資料庫。
DBI 英文全稱：Database Independent Interface，中文稱為資料庫獨立接口。
DBI 作為 Perl 語言中和資料庫進行通訊的標準接口，它定義了一系列的方法，變量和常量，提供一個和具體資料庫平台無關的資料庫持久層。

前置

DBI結構

DBI 和具體資料庫平台，我們可以將其應用在 Oracle、MySQL 或 Informix、等資料庫中。

中 DBI 獲取所有 API（Application Programming Interface：應用圖形程序接口）發送過來的 SQL 資料，然後分發到對應的驅動上執行，最後再獲取資料返回。

變量名約定

以下設置了比較常用的變量名命名方法：

變量名	說明
$dsn	驅動程序對象的句柄
$dbh	一個資料庫對象的句柄
$sth	一個語句或者一個查詢對象的句柄
$h	通用的句柄 ($dbh, $sth, 或 $drh)，依賴於上下文
$rc	操作程式碼返回的布什值（true 或 false）
$rv	操作程式碼返回的整數值
@ary	查詢返回的一行值的數組（列表）
$rows	操作程式碼返回的行數值
$fh	文件句柄
undef	NULL 值表示未定義
\%attr	引用屬性的哈希值並傳到方法上

資料庫連接

假設我們要使用 MySQL 資料庫。在連接到資料庫之前，請確保以下事項。如果你不知道如何在 MySQL 資料庫中創建資料庫和表，你可以藉你我們的 MySQL 教學。

你已經創建了一個名為你TESTDB 的資料庫。
你已經在 TESTDB 中創建了一個名為 TEST_TABLE 的表。
該表包含字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
用戶ID“root”和密碼“root”被設置為訪問TESTDB。
Perl你模塊 DBI 你正確安裝在你的機器上。
1
2
$ cpan DBI
$ cpan DBD::mysql
你已經閱讀了 MySQL 教學以了解 MySQL 基礎知識。

註：可以使用 Docker 建立一個 MySQL資料庫。連結

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for TEST_TABLE
-- ----------------------------
DROP TABLE IF EXISTS `TEST_TABLE`;
CREATE TABLE `TEST_TABLE` (
  `FIRST_NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LAST_NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `AGE` tinyint(1) DEFAULT NULL,
  `SEX` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `INCOME` decimal(10,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of TEST_TABLE
-- ----------------------------
BEGIN;
INSERT INTO `TEST_TABLE` VALUES ('J', 'J', 18, 'male', 99999.0000);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

連接

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "W3CSCHOOL"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "123456"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

如果與資料源建立了連接，則返回一個資料庫句柄並將其保存到 $dbh 中以供進一步使用，否則 $dbh 設置為 undef 值並且 $DBI::errstr 返回錯誤字符串。

SELECT 操作

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "W3CSCHOOL"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "123456"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM Websites"); # 預處理 SQL 語句
$sth->execute(); # 執行 SQL 操作

# 循環輸出所有資料
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}

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

執行以上程序，輸出結果為：

1	J\tJ\t18\tmale\t99999.0000

INSERT 操作

#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("INSERT INTO `TESTDB`.`TEST_TABLE` (`FIRST_NAME`, `LAST_NAME`, `AGE`, `SEX`, `INCOME`) VALUES ('I', 'I' , '99', 'male', '1234.0000')");

$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1 2	commit ineffective with AutoCommit enabled at ./main.pl line 20. Died at ./main.pl line 20.

應用程序還可以綁定輸出和輸入參數，下面例子通過用變量取代?佔位符的位置來執行一條插入查詢：

#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $firstName = "I";
my $lastName = "I";
my $age = 99;
my $sex = "amle";
my $income = 1234.0000;
my $sth = $dbh->prepare("INSERT INTO `TESTDB`.`TEST_TABLE` (`FIRST_NAME`, `LAST_NAME`, `AGE`, `SEX`, `INCOME`) VALUES (?,?,?,? ,?)");

$sth->execute($firstName, $lastName, $age, $sex, $income)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1 2	commit ineffective with AutoCommit enabled at ./main.pl line 27. Died at ./main.pl line 27.

UPDATE 操作

#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh->prepare("UPDATE `TESTDB`.`TEST_TABLE` SET `FIRST_NAME` = 'Z', `LAST_NAME` = 'Z' WHERE `FIRST_NAME` = 'I' AND `LAST_NAME` = 'I '");

$sth->execute() or die $DBI::errstr;
print "更新的記錄數 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1
2
3

commit ineffective with AutoCommit enabled at ./main.pl line 22.
Died at ./main.pl line 22.
1%

DELETE 操作

#!/usr/bin/perl

use strict;
use DBI;

my $host = "127.0.0.1"; # 主機地址
my $driver = "mysql"; # 接口類型 預設為 localhost
my $database = "TESTDB"; # 資料庫
# 驅動程序對象的句柄
my $dsn = "DBI:$driver:database=$database:$host";
my $userid = "root"; # 資料庫用戶名
my $password = "root"; # 資料庫密碼

# 連接資料庫
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh->prepare("DELETE FROM `TESTDB`.`TEST_TABLE` WHERE (`FIRST_NAME` = 'Z' AND `LAST_NAME` = 'Z')");

$sth->execute() or die $DBI::errstr;
print "刪除的記錄數 :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

執行以上程序，輸出結果為：

1
2
3

commit ineffective with AutoCommit enabled at ./main.pl line 22.
Died at ./main.pl line 22.
1%

Do 語句

do 語句可以執行 UPDATE, INSERT, 或 DELETE 操作，使用他比較簡短，執行成功返回true，執行失敗返回 false，範例如下：

1 2	my $c = $dbh->do('DELETE FROM TEST_TABLE WHERE age = 30'); print "Deleted $c rows\n";

執行以上程序，成功刪除，輸出結果為：

1	Deleted 1 rows

執行以上程序，沒有刪除，輸出結果為：

1	Deleted 0E0 rows

COMMIT 操作

commit 為提交事務，完成資料庫的操作：

1	$dbh->commit or die $dbh->errstr;

ROLLBACK 操作

如果在 SQL 執行過程中發生錯誤，可以回滾資料，不做任何改變：

1	$dbh->rollback or die $dbh->errstr;

事務

和其它的語言一樣，perl DBI對資料庫的操作也支持事務處理它的實現方式有兩個：

1、在連接資料庫的時候就開始一個事務

1	$dbh = DBI->connect($dsn, $userid, $password, {AutoCommit => 0}) or die $DBI::errstr;

以上程式碼在連接的時候設置了AutoCommit為false，也就是說當你對資料庫進行更新操作的時候，它不會自動地把那些更新直接寫到資料庫裡，而是要程序通過$dbh->commit來使資料真正地寫到資料庫裡，或$dbh->rollback 來回滾剛才的操作。

2、通過$dbh->begin_work()語句來開始一個事務
這種方式就不需要在連接資料庫的時候設置 AutoCommit ＝ 0。

可以一次資料庫連接進行多次事務操作，不用每一次事務的開始都去連接一次資料庫。

$rc = $dbh->begin_work or die $dbh->errstr;

#####################
##這裡執行一些 SQL 操作
#####################

$dbh->commit; # 成功後操作
-----------------------------
$dbh->rollback; # 失敗後回滾

斷開資料庫連接

如果我們需要斷開資料庫連接，可以使用 disconnect API：

1	$rc = $dbh->disconnect or warn $dbh->errstr;

註：以上參考了
Tutorialspoint, Perl - Database Access

Perl - 第二十三章 | Perl DBD、DBI::mysql模組安裝

Perl - 第二十五章 | Perl 包和模塊

↑
If you enjoy the article, please feel free to donate~ Thx.
若本文對您有幫助，您也願意支持打賞，謝謝您的鼓勵。

本文由J.J. Huang 創作，採用CC BY 3.0 TW協議進行許可。可自由轉載、引用，但需署名作者且註明文章出處。

J.J.'s Blogs

J.J. Huang 2021-06-14 Perl 瀏覽次數：次 {{moment(1623632400000).fromNow()}}

Perl - 第二十四章 | Perl 資料庫訪問

前置

DBI結構

變量名約定

資料庫連接

連接

SELECT 操作

INSERT 操作

UPDATE 操作

DELETE 操作

Do 語句

COMMIT 操作

ROLLBACK 操作

事務

斷開資料庫連接