Like Share Discussion Bookmark Smile

J.J. Huang   2021-06-14   Perl   瀏覽次數:

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資料庫。連結

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;

連接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/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 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/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 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 $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.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/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 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/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 操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/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。

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

1
2
3
4
5
6
7
8
9
$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