当前位置：Gxlcms > 数据库问题 > MySQL事务操作

MySQL事务操作

时间：2021-07-01 10:21:17 帮助过：3人阅读

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；

  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

数据库事务操作实例：

技术图片 
  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">use</span><span style="color: #000000;"> RUNOOB;
  2. </span><span style="color: #0000ff;">Database</span><span style="color: #000000;"> changed
  3. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> runoob_transaction_test( id <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">5</span>)) engine<span style="color: #808080;">=</span><span style="color: #000000;">innodb;  # 创建数据表
  4. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.04</span><span style="color: #000000;"> sec)
  5.  
  6. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> runoob_transaction_test;
  7. Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  8.  
  9. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">begin</span><span style="color: #000000;">;  # 开始事务
  10. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  11.  
  12. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> runoob_transaction_test value(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">);
  13. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  14.  
  15. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> runoob_transaction_test value(<span style="color: #800000; font-weight: bold;">6</span><span style="color: #000000;">);
  16. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  17.  
  18. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">commit</span><span style="color: #000000;">; # 提交事务
  19. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  20.  
  21. mysql</span><span style="color: #808080;">></span>  <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> runoob_transaction_test;
  22. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  23. <span style="color: #808080;">|</span> id   <span style="color: #808080;">|</span>
  24. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  25. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">5</span>    <span style="color: #808080;">|</span>
  26. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">6</span>    <span style="color: #808080;">|</span>
  27. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  28. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  29.  
  30. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">begin</span><span style="color: #000000;">;    # 开始事务
  31. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  32.  
  33. mysql</span><span style="color: #808080;">></span>  <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> runoob_transaction_test <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;">);
  34. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  35.  
  36. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">rollback</span><span style="color: #000000;">;   # 回滚
  37. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  38.  
  39. mysql</span><span style="color: #808080;">></span>   <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> runoob_transaction_test;   # 因为回滚所以数据没有插入
  40. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  41. <span style="color: #808080;">|</span> id   <span style="color: #808080;">|</span>
  42. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  43. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">5</span>    <span style="color: #808080;">|</span>
  44. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">6</span>    <span style="color: #808080;">|</span>
  45. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  46. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  47.  
  48. mysql</span><span style="color: #808080;">></span>
数据库事务操作实例

 

MySQL事务操作

标签：event   允许   处理   style   table   repeat   技术   指定   htm   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！