您当前的位置: 首页 » MySQL/MSSQL编程 » MySQL 事务预编译查询和Perl DBI简化
MySQL 事务预编译查询和Perl DBI简化
论坛链接
许多WEB应用程序内部通常重复运行带不同自变量的相同数据库查询，或以全有或全无块的形式执行一组相关查询。要满足这些要求，大多数数据库系统（包括MySQL）支持预编译查询与事务，多数脚本语言（如PHP和Perl）也拥有内置函数访问这些数据库特性。然而，上述两项特性是MySQL的新功能，因此给那些以前没有见过它们的开发者制造了一些麻烦。
本文旨在解决这一问题。文章讨论了支持MySQL事务和预编译查询的Perl DBI函数，并对其工作原理和应用方法进行说明。
预编译查询
在数据库中执行批量上传时，我们经常见到这样的SQL查询，这些查询仅在提交给INSERT命令的参数上有所不同。在这些情况下，应用所谓的预编译查询是一种常用的优化方法，它为查询建立一个模板，然后再向模板中导入不同的必要值，从而减少了数据库消耗。如果使用得当，这个特性能够显著提高应用程序的性能。
为说明这一点，我们以一个二域表格为例，如下面的列表A所示：mysql> SELECT * FROM users;
+-------+--------+
| fname | lname|
+----------------+
| Joe| Blow|
+-------+--------+
1 row in set (0.09 sec)列表A
现在，假设我需要通过INSERT查询向这个表格中输入一组新记录。很明显，每次运行时，查询的格式保持不变，只有输入的值发生变化。要完成这一操作，最佳方法是为INSERT查询建立一个内置DBI“占位符”的模板，然后在每次运行时用实际值来代替占位符。如列表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();列表B
本文旨在解决这一问题。文章讨论了支持MySQL事务和预编译查询的Perl DBI函数，并对其工作原理和应用方法进行说明。
预编译查询
在数据库中执行批量上传时，我们经常见到这样的SQL查询，这些查询仅在提交给INSERT命令的参数上有所不同。在这些情况下，应用所谓的预编译查询是一种常用的优化方法，它为查询建立一个模板，然后再向模板中导入不同的必要值，从而减少了数据库消耗。如果使用得当，这个特性能够显著提高应用程序的性能。
为说明这一点，我们以一个二域表格为例，如下面的列表A所示：mysql> SELECT * FROM users;
+-------+--------+
| fname | lname|
+----------------+
| Joe| Blow|
+-------+--------+
1 row in set (0.09 sec)
现在，假设我需要通过INSERT查询向这个表格中输入一组新记录。很明显，每次运行时，查询的格式保持不变，只有输入的值发生变化。要完成这一操作，最佳方法是为INSERT查询建立一个内置DBI“占位符”的模板，然后在每次运行时用实际值来代替占位符。如列表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();
#2楼发布者：tzlink时间：2007-09-26 10:01:14
建立并执行一个带Perl DBI预编译SQL查询共分四个简单的步骤：
首先调用connect()方法初始化一个数据库句柄。这个方法以一个字符串为连接参数，这个字符串中包括数据库类型（”mysql”）、主机名称（”localhost”）和数据库名称（”somedb”）；并向connect()方法提供用户名（”user”）和密码（”pass”）作为第二和第三自变量。
调用prepare()函数建立SQL查询模板。prepare()中用到的问号为代替实际值的占位符。
调用execute()方法向查询预编译模板中输入实际数据值，把它提交给占位符代替的自变量。注意，这里自变量的顺序十分重要，在上一步中必须为每个占位符定义一个自变量。每次通过一组不同的自变量调用execute()方法，就用相应的值执行一次INSERT查询。
调用disconnect()方法结束会话。
从一个外部文件中批量插入数据，是上述查询的典型应用。这时，首先调用prepare()方法，然后应用一个循环从文件中读入数据，每运行一次循环，即调用execute()方法在数据库中插入一组值。
事务
事务支持是MySQL的另外一项重要的新特性。简单来说，事务就是一个以全有或全无方式执行的SQL语句块（因为这些语句彼此相互依赖）。事务中的所有语句必须全部成功执行，事务才能成功完成；如果有任何一个语句出现错误，系统就会“退回”到原始状态，以避免数据连接/破坏问题。
在两个银行账户间转账就是一个典型的例子。在数据库中，转账过程包括二个步骤：首先，从源账户余额中提取转账金额，然后将其存入目标账户的余额中。如果第二步发生错误，那么第一步必须倒退到一个前面的“快照”，以避免余额失衡。大部分数据库（包括MySQL）通过一组命令完成这种转账过程。
START TRANSACTION命令标记一个新事务块开始，接着执行一系列SQL命令。
COMMIT命令标记一个事务块结束，表示在事务中发生的所有变化应被“提交”或永久化。
ROLLBACK命令标记一个事务块结束，并表示事务中发生的所有变化必须被撤销。
为说明它的实际应用，我们以一个存储用户账户的表格为例，如列表C所示：
首先调用connect()方法初始化一个数据库句柄。这个方法以一个字符串为连接参数，这个字符串中包括数据库类型（”mysql”）、主机名称（”localhost”）和数据库名称（”somedb”）；并向connect()方法提供用户名（”user”）和密码（”pass”）作为第二和第三自变量。
调用prepare()函数建立SQL查询模板。prepare()中用到的问号为代替实际值的占位符。
调用execute()方法向查询预编译模板中输入实际数据值，把它提交给占位符代替的自变量。注意，这里自变量的顺序十分重要，在上一步中必须为每个占位符定义一个自变量。每次通过一组不同的自变量调用execute()方法，就用相应的值执行一次INSERT查询。
调用disconnect()方法结束会话。
从一个外部文件中批量插入数据，是上述查询的典型应用。这时，首先调用prepare()方法，然后应用一个循环从文件中读入数据，每运行一次循环，即调用execute()方法在数据库中插入一组值。
事务
事务支持是MySQL的另外一项重要的新特性。简单来说，事务就是一个以全有或全无方式执行的SQL语句块（因为这些语句彼此相互依赖）。事务中的所有语句必须全部成功执行，事务才能成功完成；如果有任何一个语句出现错误，系统就会“退回”到原始状态，以避免数据连接/破坏问题。
在两个银行账户间转账就是一个典型的例子。在数据库中，转账过程包括二个步骤：首先，从源账户余额中提取转账金额，然后将其存入目标账户的余额中。如果第二步发生错误，那么第一步必须倒退到一个前面的“快照”，以避免余额失衡。大部分数据库（包括MySQL）通过一组命令完成这种转账过程。
START TRANSACTION命令标记一个新事务块开始，接着执行一系列SQL命令。
COMMIT命令标记一个事务块结束，表示在事务中发生的所有变化应被“提交”或永久化。
ROLLBACK命令标记一个事务块结束，并表示事务中发生的所有变化必须被撤销。
为说明它的实际应用，我们以一个存储用户账户的表格为例，如列表C所示：
#3楼发布者：tzlink时间：2007-09-26 10:01:25
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) 列表C现在，假设我要转账400美元。实际的“事务”通过两个UPDATE语句来执行，一个语句将转账金额从源账户中取出，另一语句将其存入目标账户。如果我只是在账户间进行转账，那么整个过程中，所有账户的总余额（00）应一直保持不变。以下是完成转账的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();
列表D
+----+------------+---------+
| id | label| balance |
+----+------------+---------+
|1 | Savings #1 |1000 |
|2 | Current #1 |2000 |
|3 | Current #2 |3000 |
+----+------------+---------+
3 rows in set (0.34 sec) 列表C现在，假设我要转账400美元。实际的“事务”通过两个UPDATE语句来执行，一个语句将转账金额从源账户中取出，另一语句将其存入目标账户。如果我只是在账户间进行转账，那么整个过程中，所有账户的总余额（00）应一直保持不变。以下是完成转账的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();
列表D
#4楼发布者：tzlink时间：2007-09-26 10:01:37
在Perl中执行一个事务共有四个基本步骤：
第一步，关闭数据库“自动提交”。(本质上说，自动提交意味着系统保存你所做的变化。)这一步很重要，因为你只应在确定所有的事务“单元”成功完成后，才保存发生的变化。在上面的例子中，我们在调用connect()方法时将AutoCommit选项设为0，完成关闭操作。
下一步，以普通方式执行INSERT、UPDATE和/或DELETE查询，但将这些查询包含在一个eval{}块中。这样做是为了保证：如果发生错误，程序会在块以外中断，事务就不会被提交。如果一切正常，发生的变化将在事务块中的所有查询执行后，通过调用commit()提交给数据库。
如果在执行事务块中任何一个语句时出现错误，程序将在eval{}块外中断，并继续执行后面的代码。这些代码首先打印一段错误信息，然后通过rollback()函数将数据库退回到事务前的状态。注意，一旦调用这个函数，事务即无法逆转。
调用disconnect()方法结束会话。
如你所见，用Perl和MySQL执行事务模型能够使MySQL数据库在遇到查询执行错误时更加稳定。但是，在新开始着手用这些新特性重写代码前，必须注意，它们实际上增加了系统的性能消耗；因此，在执行它们之前，最好进行一下成本效益分析。
第一步，关闭数据库“自动提交”。(本质上说，自动提交意味着系统保存你所做的变化。)这一步很重要，因为你只应在确定所有的事务“单元”成功完成后，才保存发生的变化。在上面的例子中，我们在调用connect()方法时将AutoCommit选项设为0，完成关闭操作。
下一步，以普通方式执行INSERT、UPDATE和/或DELETE查询，但将这些查询包含在一个eval{}块中。这样做是为了保证：如果发生错误，程序会在块以外中断，事务就不会被提交。如果一切正常，发生的变化将在事务块中的所有查询执行后，通过调用commit()提交给数据库。
如果在执行事务块中任何一个语句时出现错误，程序将在eval{}块外中断，并继续执行后面的代码。这些代码首先打印一段错误信息，然后通过rollback()函数将数据库退回到事务前的状态。注意，一旦调用这个函数，事务即无法逆转。
调用disconnect()方法结束会话。
如你所见，用Perl和MySQL执行事务模型能够使MySQL数据库在遇到查询执行错误时更加稳定。但是，在新开始着手用这些新特性重写代码前，必须注意，它们实际上增加了系统的性能消耗；因此，在执行它们之前，最好进行一下成本效益分析。