ホームページ >php教程 >php手册 >php操作mysql事务深度测试

php操作mysql事务深度测试

WBOY
WBOYオリジナル
2016-06-06 19:51:551028ブラウズ

一 事务是什么? 正常的sql语句是一条一条的执行,各个sql之间互不影响。 事务的机制类于一个黑盒,黑盒里的sql语句(多条)要么全部成功了,要么全部失败了。(土话就这么讲,术语我就不说了) 二 如何应用事务? 一个事务有三个关键步骤:开始、提交、回滚

一 事务是什么?

  正常的sql语句是一条一条的执行,各个sql之间互不影响。

  事务的机制类似于一个黑盒,黑盒里的sql语句(多条)要么全部成功了,要么全部失败了。(土话就这么讲,术语我就不说了) 

二 如何应用事务?

  一个事务有三个关键步骤:开始、提交、回滚。

  开始就是要告诉数据库我要开始事务了,那么数据库就会给你提供一个黑盒,接下来你就开始执行sql,如果sql都没问题,那么就执行提交,那么黑盒里操作的结果就会真正写入数据库,如果有失败的,那么执行以下回滚,数据库没有任何改变。

  从这个角度看,黑盒更像是一个“沙盒”。

  php想对应的是操作mysqli库的三个方法:  mysqli::autocommit,  mysqli::commit(),  mysqli::rollback().

三 单元测试

  1.创建mysql测试表(MyISAM引擎不支持事务,InnoDB引擎可以支持,所以将测试表建为InnoDB引擎

  表名:transaction_test,两个字段:id主键自增,content字符串类型。

 

<span>CREATE</span> <span>TABLE</span><span> `transaction_test`(
    `id` </span><span>int</span>(<span><strong>11</strong></span>) <span>NOT</span> <span>NULL</span><span> AUTO_INCREMENT,
    `content` </span><span>varchar</span>(<span><strong>256</strong></span>) <span>DEFAULT</span> <span>NULL</span><span>,
    </span><span>PRIMARY</span> <span>KEY</span><span> (`id`)
) ENGINE</span><span>=</span>InnoDB <span>DEFAULT</span> CHARSET<span>=</span>utf8 COMMENT<span>=</span><span>'</span><span>php事务测试表</span><span>'</span>;

 

  2.插入初始化数据

<span>insert</span> <span>into</span> transaction_test(content) <span>values</span>(<span>'</span><span>北京</span><span>'</span>),(<span>'</span><span>上海</span><span>'</span>),(<span>'</span><span>广州</span><span>'</span>);

  如图:

  php操作mysql事务深度测试

  3.php连接数据库并插入数据测试

    创建transaction_test.php

    

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('天津')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

    运行效果及数据库结果:

    php操作mysql事务深度测试      php操作mysql事务深度测试

       插入OK。

    4.测试mysqli::autocommit()。

      autocommit()方法是设置数据库是否自动提交,参数为true和false,默认true。默认状态下每执行一次query(),数据库就会提交一次,那么如果设置为false,数据库就不会自动提交,那么就可以开始事务了,具体用法看下面的测试:

      在上面代码的基础上,在插入数据的sql语句query之前,关闭自动提交。

    

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>false</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('重庆')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

      运行效果及数据库结果:

      php操作mysql事务深度测试    php操作mysql事务深度测试

      分析:php返回id为5,但数据库却没有这条记录,说明开始事务之后所执行的query只是在一个“沙盒”里,并没有真正的写入到数据库。

    5.测试mysqli::commit()

      在上面代码中插入代码的后面加上commit()方法。

    

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>false</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('哈尔滨')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>/*</span><span> 事务提交 </span><span>*/</span>
<span>$mysqli</span>-><span>commit();

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

      运行效果及数据库结果:

      php操作mysql事务深度测试    php操作mysql事务深度测试

      分析:执行提交后数据就真正写入数据库了,另外通过id是6可以看出,刚才没有提交的事务虽然没有写入数据库,但是却消耗了一个自增的id。

    6.测试mysqli::rollback()

      将上面的commit()换成rollback()

      

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>false</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('沈阳')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>/*</span><span> 事务回滚 </span><span>*/</span>
<span>$mysqli</span>-><span>rollback();

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

      运行效果及数据库结果:

      php操作mysql事务深度测试  php操作mysql事务深度测试

       分析:同样是返回的id是7,但数据库没有插入这条记录。那这跟上面的不写rollback()的结果不是一样吗?肯定不一样,继续测试。

    7.继续测试rollback()

      把上面代码的rollback()去掉,在上面的insert语句的query之后,修改autocommit为true,因为一个事务完成之后还要执行其他的sql语句,要把自动提交恢复到默认状态,然后再执行一次insert语句。

      

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>false</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('沈阳')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>/*</span><span> 事务回滚 
$mysqli->rollback();
 </span><span>*/</span>

<span>/*</span><span> 恢复自动提交 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>true</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_2</span> = "insert into transaction_test(`content`) values('长春')"<span>;
</span><span>$query2</span> = <span>$mysqli</span>->query(<span>$sql_2</span><span>);
</span><span>if</span>(<span>$query2</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query2 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id two: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

      运行效果及数据库结果:

      php操作mysql事务深度测试  php操作mysql事务深度测试

      分析:虽然前面开启了事务,但是自动提交恢复默认之后,第一条insert语句同样写进了数据库,所以不写rollback()并没有回滚。

    8 继续测试rollback()

      将上面代码中的rollback()的注释去掉

      

php操作mysql事务深度测试

<span>php
</span><span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);

</span><span>/*</span><span> check connection </span><span>*/</span>
<span>if</span> (<span>mysqli_connect_errno</span><span>()) {
    </span><span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
    </span><span>exit</span><span>();
}

</span><span>/*</span><span> set charset </span><span>*/</span>
<span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>if</span>(<span>$query</span>===<span>false</span><span>) {
    </span><span>echo</span> 'set charset failed <br>'<span>;
    </span><span>exit</span>(0<span>);
}

</span><span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>false</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_1</span> = "insert into transaction_test(`content`) values('沈阳')"<span>;
</span><span>$query1</span> = <span>$mysqli</span>->query(<span>$sql_1</span><span>);
</span><span>if</span>(<span>$query1</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query1 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id one: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>/*</span><span> 事务回滚  </span><span>*/</span>
<span>$mysqli</span>-><span>rollback();


</span><span>/*</span><span> 恢复自动提交 </span><span>*/</span>
<span>$mysqli</span>->autocommit(<span>true</span><span>);

</span><span>/*</span><span> insert </span><span>*/</span>
<span>$sql_2</span> = "insert into transaction_test(`content`) values('长春')"<span>;
</span><span>$query2</span> = <span>$mysqli</span>->query(<span>$sql_2</span><span>);
</span><span>if</span>(<span>$query2</span>===<span>false</span><span>) {
    </span><span>echo</span> 'query2 failed <br>'<span>;
} </span><span>else</span><span> {
    </span><span>printf</span>("insert_id two: %d <br>", <span>$mysqli</span>-><span>insert_id);
}

</span><span>$mysqli</span>-><span>close();
</span>?> 

php操作mysql事务深度测试

      运行效果及数据库结果:

      php操作mysql事务深度测试  php操作mysql事务深度测试

      分析:id为10的记录没有插入到数据库,这次执行回滚了。

--------------------------------------------------------------------------------------------------------------

    9 基于以上的测试,应该对autocommit(),commit(),rollback()有一个比较深的认识了,最后贴一段代码模拟一下真实环境

 

php操作mysql事务深度测试

<span> 1</span> <span>php
</span><span> 2</span> <span>$mysqli</span> = <span>new</span> mysqli("localhost", "root", "", "test"<span>);
</span><span> 3</span> 
<span> 4</span> <span>/*</span><span> check connection </span><span>*/</span>
<span> 5</span> <span>if</span> (<span>mysqli_connect_errno</span><span>()) {
</span><span> 6</span>     <span>printf</span>("Connect failed: %s\n", <span>mysqli_connect_error</span><span>());
</span><span> 7</span>     <span>exit</span><span>();
</span><span> 8</span> <span>}
</span><span> 9</span> 
<span>10</span> <span>/*</span><span> set charset </span><span>*/</span>
<span>11</span> <span>$sql_set_charset</span> = "set names utf8"<span>;
</span><span>12</span> <span>$query</span> = <span>$mysqli</span>->query(<span>$sql_set_charset</span><span>);
</span><span>13</span> <span>if</span>(<span>$query</span>===<span>false</span><span>) {
</span><span>14</span>     <span>echo</span> 'set charset failed <br>'<span>;
</span><span>15</span>     <span>exit</span>(0<span>);
</span><span>16</span> <span>}
</span><span>17</span> 
<span>18</span> <span>/*</span><span> 关闭自动提交,开始事务 </span><span>*/</span>
<span>19</span> <span>$mysqli</span>->autocommit(<span>false</span><span>);
</span><span>20</span> 
<span>21</span> <span>/*</span><span> insert </span><span>*/</span>
<span>22</span> <span>$commit</span> = <span>true</span><span>;
</span><span>23</span> <span>$sql_array</span> = <span>array</span>("insert into transaction_test(`content`) values('石家庄')","insert into transaction_test(`content2`) values('郑州')"<span>);
</span><span>24</span> <span>foreach</span>(<span>$sql_array</span> <span>as</span> <span>$key</span>=><span>$item</span><span>) {
</span><span>25</span>     <span>$query</span> = <span>$mysqli</span>->query(<span>$item</span><span>);
</span><span>26</span>     <span>if</span>(<span>$query</span>===<span>false</span><span>) {
</span><span>27</span>         <span>/*</span><span> 事务回滚  </span><span>*/</span>
<span>28</span>         <span>printf</span>("query failed : %d <br>", <span>$key</span><span>);
</span><span>29</span>         <span>$mysqli</span>-><span>rollback();
</span><span>30</span>         <span>$commit</span> = <span>false</span><span>;
</span><span>31</span>         <span>break</span><span>;
</span><span>32</span>     } <span>else</span><span> {
</span><span>33</span>         <span>printf</span>("insert_id <span>$key</span>: %d <br>", <span>$mysqli</span>-><span>insert_id);
</span><span>34</span> <span>    }
</span><span>35</span> <span>}
</span><span>36</span> 
<span>37</span> <span>/*</span><span> 提交事务 </span><span>*/</span>
<span>38</span> <span>if</span>(<span>$commit</span><span>) {
</span><span>39</span>     <span>$mysqli</span>-><span>commit();
</span><span>40</span> <span>}
</span><span>41</span> 
<span>42</span> <span>/*</span><span> 恢复自动提交 </span><span>*/</span>
<span>43</span> <span>$mysqli</span>->autocommit(<span>true</span><span>);
</span><span>44</span> 
<span>45</span> <span>/*</span><span> insert </span><span>*/</span>
<span>46</span> <span>$sql_2</span> = "insert into transaction_test(`content`) values('最后插入')"<span>;
</span><span>47</span> <span>$query2</span> = <span>$mysqli</span>->query(<span>$sql_2</span><span>);
</span><span>48</span> <span>if</span>(<span>$query2</span>===<span>false</span><span>) {
</span><span>49</span>     <span>echo</span> 'query2 failed <br>'<span>;
</span><span>50</span> } <span>else</span><span> {
</span><span>51</span>     <span>printf</span>("insert_id two: %d <br>", <span>$mysqli</span>-><span>insert_id);
</span><span>52</span> <span>}
</span><span>53</span> 
<span>54</span> <span>$mysqli</span>-><span>close();
</span><span>55</span> ?> 

php操作mysql事务深度测试

      分析:上面的代码中23行的两条sql语句都是正确的,测试的时候可以这样测:两条都正确,1正确2不正确,1不正确2正确,1和2都不正确。

      结果:(只有两条都正确的时候才会真正写入数据库,其他3种情况都回滚了)

    更广阔的的天地就靠大家发挥了。

    貌似这里php的人少,.net人要多一点,希望写的有用。

作者:老默http://www.cnblogs.com/laomo/

声明:
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。