MySQL 事件预编译查询和Perl DBI简化[MySQL防范]

很多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)

目前,假定我需求通过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();

成立并履行一个带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所示:

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)

目前,假定我要转账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();

在Perl中履行一个事件共有四个基本步骤:

第一步,关闭数据库“自动提交”.(本质上说,自动提交意味着系统保存你所做的改变.)这一步很重要,因为你只应在肯定全部的事件“单元”成功完成后,才保存发生的改变.在上面的例子中,我们在调用connect()办法时将AutoCommit选项设为0,完成关闭操作.

下一步,以普通方法履行INSERT、UPDATE和/或DELETE查询,但将这些查询包含在一个eval{}块中.这样做是为了保证:假如发生错误,程序会在块以外中止,事件就不会被提交.假如一切正常,发生的改变将在事件块中的全部查询履行后,通过调用commit()提交给数据库.

假如在履行事件块中任何一个语句时呈现错误,程序将在eval{}块外中止,并持续履行背面的代码.这些代码首先打印一段错误信息,然后通过rollback()函数将数据库退回到事件前的状况.注意,一旦调用这个函数,事件即无法逆转.

调用disconnect()办法完毕会话.

如你所见,用Perl和MySQL履行事件模子可以使MySQL数据库在碰到查询履行错误时越发安定.但是,在新开始着手用这些新特点重写代码前,必须注意,它们实际上增添了系统的性能损耗;因此,在履行它们之前,最好举行一下本钱效益解析.