代码详解MySQL中关于事务的处理

这篇文章主要介绍了详解MySQL中关于事务的处理,小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧

步骤：

1.开启事务 start transaction

当我们开启一个事务的时候，我们对sql的操作都发生在内存中，但是没有真正的反馈到数据库磁盘的文件中！

2.回滚 rollback

回滚，就是恢复到事务开启之前的最原始的状态！

注意：回滚操作会自动的关闭一个事务，如果想再次执行事务，需要重新开启事务！

3.提交 commit

 

事务的基本原理

 

普通的执行，之所以是立即执行并生效，因为默认的，MySQL对sql语句的执行是自动提交的！所以，开启一个事务的本质，就是关闭了以前的自动提交的功能，而是由用户手动提交（利用commit语句）！

总结事务的步骤：

1，  开启事务

2，  如果执行成功，就提交commit

3，  如果有任何一条sql语句执行失败，则回滚rollback！

 

事务处理最典型的就是借还钱。下面以张三向李四还1000元为例

首先查看数据库中各自的钱数

下面是处理还钱事务的代码：

<span style='font-size: 14px; font-family: 微软雅黑, "Microsoft YaHei";'><?<span style="font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;">php 

</span><span style="font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);">/**
* MySQL实现事务操作<br/>*/<br/><br/></span><span style="font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);">echo</span> "<meta charset=utf-8>"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;<br><br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>// 1 连接数据库<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$link</span> = @<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_connect</span>('localhost','root','') or <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>die</span>('连接数据库失败'<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_select_db</span>('test',<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$link</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>('set names utf8'<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);<br><br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>// 2  开启事务<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>("start transaction"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>//设置一个变量，用来判断所有sql语句是否成功<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$flag</span> = <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>true</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;<br><br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>// 2.1执行事务中的一组sql语句 <br><br>// 李四的money+1000<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$sql</span> = "update pdo set mone=money+1000 where name='李四'"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$res</span> = <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>(<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$sql</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>if</span> (!<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$res</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>) {   <br>     </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>//若sql语句执行失败，把$falg设置为false</span>
    <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$flag</span> = <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>false</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;
}<br><br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>//张三的money-1000<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$sql</span> = "update pdo set money=money-1000 where name='张三'"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$res</span> = <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>(<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$sql</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>if</span> (!<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$res</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>) {  <br>     </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>//若sql语句执行失败，把$falg设置为false</span>
    <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$flag</span> = <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>false</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;
}<br><br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>// 2.2 判断事务是否执行成功<br></span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>if</span> (<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(128, 0, 128);'>$flag</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>) {   <br>     </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>//所有sql语句执行成功，把sql语句提交</span>
    <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>('commit'<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);    </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>echo</span> "还钱成功！"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;
}</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>else</span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>{  <br>     </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 0);'>// 如其中一条执行失败，则回滚到事务开启之前的状态</span>
    <span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 128, 128);'>mysql_query</span>('rollback'<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>);    </span><span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5; color: rgb(0, 0, 255);'>echo</span> "还钱失败！"<span style='font-family: 微软雅黑, "Microsoft YaHei"; font-size: 14px; margin: 0px; padding: 0px; line-height: 1.5;'>;
}</span></span>

结果：

下面，我们故意把其中一个字段写错，看看事务是否正常处理，数据库中的钱数是否有变化！

<span style='font-size: 14px; font-family: 微软雅黑, "Microsoft YaHei";'>// 李四的money+1000<br>$sql = "update pdo set mone=money+1000 where name='李四'";  //把moeny字段错写成mone<br></span>

结果：

结果是还钱失败，并且数据库中各自的钱数没有变化，说明当某一条语句未执行成功时，事物不会提交，而会回滚，把数据恢复到开始事务之前的原始状态，这也是使用事务的作用，即只有当事务中所有sql语句全部执行成功，事务才会提交，否则会回滚！

Atas ialah kandungan terperinci 代码详解MySQL中关于事务的处理. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!