`
yuexiaowen
  • 浏览: 124739 次
  • 性别: Icon_minigender_2
  • 来自: 咸阳
社区版块
存档分类
最新评论

MySQL 事务预编译查询和Perl DBI简化

    博客分类:
  • perl
 
阅读更多

许多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

 

建立并执行一个带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)

 

列表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

 

在Perl中执行一个事务共有四个基本步骤:

 

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

下一步,以普通方式执行INSERT、UPDATE和/或DELETE查询,但将这些查询包含在一个eval{}块中。这样做是为了保证:如果发生错误,程序会在块以外中断,事务就不会被提交。如果一切正常,发生的变化将在事务块中的所有查询执行后,通过调用commit()提交给数据库。

如果在执行事务块中任何一个语句时出现错误,程序将在eval{}块外中断,并继续执行后面的代码。这些代码首先打印一段错误信息,然后通过rollback()函数将数据库退回到事务前的状态。注意,一旦调用这个函数,事务即无法逆转。

调用disconnect()方法结束会话。

如你所见,用Perl和MySQL执行事务模型能够使MySQL数据库在遇到查询执行错误时更加稳定。但是,在新开始着手用这些新特性重写代码前,必须注意,它们实际上增加了系统的性能消耗;因此,在执行它们之前,最好进行一下成本效益分析。(T006)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

标签数据库  分类: Perl Script

要导入一批数据到数据库,使用DBI模块还是比较方便的

  1. #!/usr/bin/perl -w
  2. use DBI;
  3. use strict;
  4. my $dbh;
  5. my $INFILE=$ARGV[0];
  6. open LIST ,"<$INFILE" or die "can't open $INFILE\n";
  7. my $table="table_appinfo";
  8. $dbh=&connect();
  9. my $qurey=qq{insert into $table (ip,name,manage,dev,location) values(?,?,?,?,?) };
  10. my $sth=$dbh->prepare($qurey) || die $dbh->errstr;
  11. my $dev="lab";
  12. my $manage="namexxx";
  13. my $location='location';
  14. while(my $line=<LIST>){
  15.     my ($ip,$name)=split /\s+/,$line;
  16.     $sth->execute($ip,$name,$manage,$dev,$location);
  17.     print "insert $ip $name\n";
  18. }
  19. $dbh->disconnect();
分享到:
评论

相关推荐

    MySQL事务预编译查询和Perl DBI简化

    许多WEB应用程序内部通常重复运行带不同自变量的相同数据库查询,或以全有或全无块的形式 执行一组相关查询。...本文讨论了支持MySQL事务和预编译查询的Perl DBI函数,并对其工作原理和应用方法进行说明。

    perl DBI手册 中文翻译版

    - **方法使用**:如`execute`执行预编译的SQL语句,`fetchrow_array`获取单行查询结果。 - **属性设置**:如`bind_param`用于绑定参数值。 #### 十三、其他重要知识点 - **占位符与绑定值**:介绍了如何安全地处理...

    Programming Perl DBI 8

    3. **执行SQL语句**:详细阐述如何通过DBI执行SELECT、INSERT、UPDATE、DELETE等SQL语句,以及如何处理批处理和预编译的SQL语句,以提高性能。 4. **结果集处理**:介绍如何获取和遍历查询返回的结果集,包括获取...

    Perl DBI中文手册 pdf.rar

    此外,还会涉及到事务管理和预编译语句,这些对于高效、安全的数据库操作至关重要。 "DBI类"和"处理器通用方法"章节会涵盖DBI的主要类和方法,比如`connect()`用于建立数据库连接,`prepare()`用于准备SQL语句,`...

    perl的DBI安装包以及安装步骤

    2. **安装DBI**:在Linux环境下,确保你有权限执行安装操作,然后使用`perl Makefile.PL`命令创建Makefile,接着运行`make`和`make test`来编译和测试模块,最后使用`make install`将模块安装到你的Perl库路径中。...

    Perl-DBI编程

    3. **预编译SQL**:提高性能,减少解析时间。 4. **动态SQL**:通过`$sth-&gt;prepare("SELECT * FROM $table")`动态构建SQL语句。 #### 五、错误处理 Perl-DBI提供了强大的错误处理机制,可以通过`$dbh-&gt;errstr`获取...

    关于perl DBI的方法使用

    Perl DBI提供了丰富的功能,简化了数据库操作,并且具有高度的灵活性和可扩展性。通过理解和熟练使用这些方法,开发者可以高效地处理各种数据库任务。无论是在小型脚本还是大型应用程序中,DBI都是Perl数据库编程的...

    perl-DBI-1.40-5.i386.rar

    总之,Perl DBI是Perl开发者与数据库交互的重要工具,它简化了数据库编程,增强了代码的可移植性,并且具有强大的功能和广泛的支持。这个"perl-DBI-1.40-5.i386.rar"压缩包对于需要在32位Linux环境下使用Perl处理...

    DBD-mysql:Perl5数据库接口(DBI)MySQL驱动程序

    在使用DBD-mysql时,你需要先安装这个模块,然后在Perl脚本中通过DBI的connect()方法建立到MySQL服务器的连接,之后可以使用prepare()、execute()等方法来执行SQL查询。 例如,以下是一个简单的使用DBD-mysql的例子...

    通过dbi使用perl连接mysql数据库的方法

    这个基础的Perl DBI示例演示了数据库连接的基本流程,但实际应用中,你可能需要处理更多复杂的情况,例如错误处理、事务管理、预编译的SQL语句等。在Perl中,你可以利用DBI模块的特性来实现这些功能,如使用`prepare...

    linux下perl操作mysql数据库(需要安装DBI)

    在本教程中,我们将详细介绍如何在Linux下安装DBI和其MySQL驱动DBD::mysql,以及编写Perl脚本来操作MySQL数据库。 首先,让我们了解DBI。DBI是Perl中用于数据库操作的核心模块,它定义了一组标准的数据库访问方法和...

    DBI、Data-ShowTable、DBD-mysql

    DBD-mysql-4.046.tar.gz是这个驱动的源码包,它提供了连接、查询、事务处理等功能,使得Perl可以通过DBI调用,无缝地与MySQL进行交互。在Linux环境下,要建立Perl到MySQL的连接,必须先安装DBD-mysql。 安装这些...

    使用Perl DBI操作MySQL的一些建议

    以下是一些使用Perl DBI操作MySQL时的重要建议和注意事项: 1. **字符集设置**: 当MySQL数据库的字符集设置为UTF8时,为了确保数据的正确读取和显示,Perl脚本也需要进行相应的配置。引入`use utf8;`声明源代码中...

    DBD-Mysql模块

    5.事务处理:DBD-Mysql支持事务管理,包括开始事务、提交事务和回滚事务,以确保数据的一致性和完整性。 6. 错误处理和调试:当发生错误时,DBD-Mysql会返回错误代码和消息,帮助开发者定位问题。同时,它还提供了...

    DBI-1.631.tar.gz

    DBI(Database Interface)是Perl编程语言中的标准数据库接口模块,它为Perl程序员提供了一种统一的方式来访问和支持各种类型的数据库。DBI-1.631是DBI的一个特定版本,该版本包含了对数据库操作的各种增强和改进。...

    DBD-mysql-4.019.tar.gz

    DBD-mysql支持各种高级特性,如事务处理、存储过程调用、预编译语句和绑定参数等。这些功能使得在Perl中使用MySQL数据库变得非常灵活和高效。 总的来说,DBD-mysql-4.019.tar.gz是Perl开发人员连接和操作MySQL...

    DBI-1.616.tar.gz

    要安装DBI,首先需要解压"DBI-1.616.tar.gz",然后进入解压后的目录并运行`perl Makefile.PL`,接着执行`make`和`make test`进行编译和测试,最后使用`make install`将DBI安装到系统中。在完成安装后,Perl程序就...

    DBI-1.636.tar.gz

    解压后,你可以找到DBI的源文件,用于编译和安装到你的Perl环境中。通常,这样的源码包会包括如下几个部分: 1. `INSTALL`:提供了安装指南,指导用户如何编译和安装模块。 2. `Makefile.PL`:Perl的Makefile生成...

Global site tag (gtag.js) - Google Analytics