MySQL事務(wù)預(yù)編譯查詢和PerlDBI簡化_MySQL

許多WEB應(yīng)用程序內(nèi)部通常重復(fù)運(yùn)行帶不同自變量的相同數(shù)據(jù)庫查詢，或以全有或全無塊的形式執(zhí)行一組相關(guān)查詢。要滿足這些要求，大多數(shù)數(shù)據(jù)庫系統(tǒng)（包括MySQL）支持預(yù)編譯查詢與事務(wù)，多數(shù)腳本語言（如PHP和Perl）也擁有內(nèi)置函數(shù)訪問這些數(shù)據(jù)庫特性。然而，上述兩項(xiàng)特性是MySQL的新功能，因此給那些以前沒有見過它們的開發(fā)者制造了一些麻煩。

本文旨在解決這一問題。文章討論了支持MySQL事務(wù)和預(yù)編譯查詢的Perl DBI函數(shù)，并對其工作原理和應(yīng)用方法進(jìn)行說明。

預(yù)編譯查詢

在數(shù)據(jù)庫中執(zhí)行批量上傳時，我們經(jīng)常見到這樣的SQL查詢，這些查詢僅在提交給INSERT命令的參數(shù)上有所不同。在這些情況下，應(yīng)用所謂的預(yù)編譯查詢是一種常用的優(yōu)化方法，它為查詢建立一個模板，然后再向模板中導(dǎo)入不同的必要值，從而減少了數(shù)據(jù)庫消耗。如果使用得當(dāng)，這個特性能夠顯著提高應(yīng)用程序的性能。

為說明這一點(diǎn)，我們以一個二域表格為例，如下面的列表A所示：

mysql> SELECT * FROM users;

+-------+--------+

| fname | lname|

+----------------+

| Joe| Blow|

+-------+--------+

1 row in set (0.09 sec)

現(xiàn)在，假設(shè)我需要通過INSERT查詢向這個表格中輸入一組新記錄。很明顯，每次運(yùn)行時，查詢的格式保持不變，只有輸入的值發(fā)生變化。要完成這一操作，最佳方法是為INSERT查詢建立一個內(nèi)置DBI“占位符”的模板，然后在每次運(yùn)行時用實(shí)際值來代替占位符。如列表B所示：

#!/usr/bin/perl

use DBI;

# create database connection

my $dbh = DBI->connect("DBI:mysql:database=somedb;
host=localhost", "user", "pass", {'RaiseError' => 1});

# prepare template query

my $sth = $dbh->prepare
("INSERT INTO users (fname, lname) VALUES (?, ?)");

# execute query with first set of parameters

$sth->execute('John', 'Doe');

# execute query with second set of parameters

$sth->execute('Jane', 'Low');

# close connection

$dbh->disconnect();

建立并執(zhí)行一個帶Perl DBI預(yù)編譯SQL查詢共分四個簡單的步驟：

首先調(diào)用connect()方法初始化一個數(shù)據(jù)庫句柄。這個方法以一個字符串為連接參數(shù)，這個字符串中包括數(shù)據(jù)庫類型（”mysql”）、主機(jī)名稱（”localhost”）和數(shù)據(jù)庫名稱（”somedb”）；并向connect()方法提供用戶名（”user”）和密碼（”pass”）作為第二和第三自變量。

調(diào)用prepare()函數(shù)建立SQL查詢模板。prepare()中用到的問號為代替實(shí)際值的占位符。

調(diào)用execute()方法向查詢預(yù)編譯模板中輸入實(shí)際數(shù)據(jù)值，把它提交給占位符代替的自變量。注意，這里自變量的順序十分重要，在上一步中必須為每個占位符定義一個自變量。每次通過一組不同的自變量調(diào)用execute()方法，就用相應(yīng)的值執(zhí)行一次INSERT查詢。

調(diào)用disconnect()方法結(jié)束會話。

從一個外部文件中批量插入數(shù)據(jù)，是上述查詢的典型應(yīng)用。這時，首先調(diào)用prepare()方法，然后應(yīng)用一個循環(huán)從文件中讀入數(shù)據(jù)，每運(yùn)行一次循環(huán)，即調(diào)用execute()方法在數(shù)據(jù)庫中插入一組值。

事務(wù)

事務(wù)支持是MySQL的另外一項(xiàng)重要的新特性。簡單來說，事務(wù)就是一個以全有或全無方式執(zhí)行的SQL語句塊（因?yàn)檫@些語句彼此相互依賴）。事務(wù)中的所有語句必須全部成功執(zhí)行，事務(wù)才能成功完成；如果有任何一個語句出現(xiàn)錯誤，系統(tǒng)就會“退回”到原始狀態(tài)，以避免數(shù)據(jù)連接/破壞問題。

在兩個銀行賬戶間轉(zhuǎn)賬就是一個典型的例子。在數(shù)據(jù)庫中，轉(zhuǎn)賬過程包括二個步驟：首先，從源賬戶余額中提取轉(zhuǎn)賬金額，然后將其存入目標(biāo)賬戶的余額中。如果第二步發(fā)生錯誤，那么第一步必須倒退到一個前面的“快照”，以避免余額失衡。大部分?jǐn)?shù)據(jù)庫（包括MySQL）通過一組命令完成這種轉(zhuǎn)賬過程。

START TRANSACTION命令標(biāo)記一個新事務(wù)塊開始，接著執(zhí)行一系列SQL命令。

COMMIT命令標(biāo)記一個事務(wù)塊結(jié)束，表示在事務(wù)中發(fā)生的所有變化應(yīng)被“提交”或永久化。

ROLLBACK命令標(biāo)記一個事務(wù)塊結(jié)束，并表示事務(wù)中發(fā)生的所有變化必須被撤銷。

為說明它的實(shí)際應(yīng)用，我們以一個存儲用戶賬戶的表格為例，如列表C所示：

mysql> SELECT * FROM accounts;

+----+------------+---------+

| id | label| balance |

+----+------------+---------+

|1 | Savings #1 |1000 |

|2 | Current #1 |2000 |

|3 | Current #2 |3000 |

+----+------------+---------+

3 rows in set (0.34 sec)

現(xiàn)在，假設(shè)我要轉(zhuǎn)賬400美元。實(shí)際的“事務(wù)”通過兩個UPDATE語句來執(zhí)行，一個語句將轉(zhuǎn)賬金額從源賬戶中取出，另一語句將其存入目標(biāo)賬戶。如果我只是在賬戶間進(jìn)行轉(zhuǎn)賬，那么整個過程中，所有賬戶的總余額（00）應(yīng)一直保持不變。以下是完成轉(zhuǎn)賬的DBI代碼（列表D）：

#!/usr/bin/perl

use DBI;

# create database connection

my $dbh = DBI->connect
("DBI:mysql:database=somedb;host=localhost", 
"user", "pass", {'RaiseError' => 1, 'AutoCommit' => 0});

# trap errors using eval{}

eval {

# debit account #1

$dbh->do("UPDATE accounts SET balance = balance-400 WHERE id=1");

# credit account #2

$dbh->do("UPDATE accounts SET balance = balance+400 WHERE id=2");

# no errors so far

# commit changes

$dbh->commit();

};

# any errors

# rollback

if ($@) {

print "Transaction aborted: $@";

$dbh->rollback();

}

# close connection

$dbh->disconnect();

在Perl中執(zhí)行一個事務(wù)共有四個基本步驟：

第一步，關(guān)閉數(shù)據(jù)庫“自動提交”。(本質(zhì)上說，自動提交意味著系統(tǒng)保存你所做的變化。)這一步很重要，因?yàn)槟阒粦?yīng)在確定所有的事務(wù)“單元”成功完成后，才保存發(fā)生的變化。在上面的例子中，我們在調(diào)用connect()方法時將AutoCommit選項(xiàng)設(shè)為0，完成關(guān)閉操作。

下一步，以普通方式執(zhí)行INSERT、UPDATE和/或DELETE查詢，但將這些查詢包含在一個eval{}塊中。這樣做是為了保證：如果發(fā)生錯誤，程序會在塊以外中斷，事務(wù)就不會被提交。如果一切正常，發(fā)生的變化將在事務(wù)塊中的所有查詢執(zhí)行后，通過調(diào)用commit()提交給數(shù)據(jù)庫。

如果在執(zhí)行事務(wù)塊中任何一個語句時出現(xiàn)錯誤，程序?qū)⒃趀val{}塊外中斷，并繼續(xù)執(zhí)行后面的代碼。這些代碼首先打印一段錯誤信息，然后通過rollback()函數(shù)將數(shù)據(jù)庫退回到事務(wù)前的狀態(tài)。注意，一旦調(diào)用這個函數(shù)，事務(wù)即無法逆轉(zhuǎn)。

調(diào)用disconnect()方法結(jié)束會話。

如你所見，用Perl和MySQL執(zhí)行事務(wù)模型能夠使MySQL數(shù)據(jù)庫在遇到查詢執(zhí)行錯誤時更加穩(wěn)定。但是，在新開始著手用這些新特性重寫代碼前，必須注意，它們實(shí)際上增加了系統(tǒng)的性能消耗；因此，在執(zhí)行它們之前，最好進(jìn)行一下成本效益分析。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com