MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，删除了一个人员，需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

 

• 原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做 
• 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。 
• 隔离性：一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。 
• 持续性：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

MySQL中的InnoDB引擎的表才支持transaction。在一个事务里，如果出现一个数据库操作失败了，事务内的所有操作将被回滚，数据库将会回到事务前的初始状态。

在Mysql控制台使用事务来操作

 

1、开始一个事务

start transaction

2、做保存点

savepoint 保存点名称

3、操作

4、可以回滚，可以提交，没有问题，就提交，有问题就回滚。

PHP中使用事务实例

1、首先创建相关数据表telephone和employee，脚本和数据如下：

1. MariaDB[RUNOOB]> CREATE TABLE `employee`(
2. ->`id`int NOT NULL AUTO_INCREMENT,
3. ->`first_name` varchar(100) NOT NULL,
4. ->`last_name` varchar(100) NOT NULL,
5. ->`job_title` varchar(100) DEFAULT NULL,
6. ->`salary`double DEFAULT NULL,
7. ->`notes` text,
8. -> PRIMARY KEY (`id`)
9. ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10. Query OK,0 rows affected (0.11 sec)
12. MariaDB[RUNOOB]> INSERT INTO `employee`(`first_name`,`last_name`,`job_title`,`salary`) VALUES
13. ->('Robin','Jackman','Software Engineer',5500),
14. ->('Taylor','Edward','Software Architect',7200),
15. ->('Vivian','Dickens','Database Administrator',6000),
16. ->('Harry','Clifford','Database Administrator',6800),
17. ->('Eliza','Clifford','Software Engineer',4750),
18. ->('Nancy','Newman','Software Engineer',5100),
19. ->('Melinda','Clifford','Project Manager',8500),
20. ->('Harley','Gilbert','Software Architect',8000);
21. Query OK,8 rows affected (0.01 sec)
22. Records:8Duplicates:0Warnings:0
24. MariaDB[RUNOOB]> CREATE TABLE `telephone`(
25. ->`id`int NOT NULL AUTO_INCREMENT,
26. ->`employee_id`int DEFAULT NULL,
27. ->`type` varchar(20) NOT NULL,
28. ->`no` varchar(50) NOT NULL,
29. -> PRIMARY KEY (`id`)
30. ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31. Query OK,0 rows affected (0.04 sec)
33. MariaDB[RUNOOB]> INSERT INTO `telephone`(`employee_id`,`type`,`no`) VALUES
34. ->(1,'mobile','245-249697'),
35. ->(2,'mobile','270-235969'),
36. ->(2,'land','325-888885'),
37. ->(3,'mobile','270-684972'),
38. ->(4,'mobile','245-782365'),
39. ->(4,'land','325-888886'),
40. ->(5,'mobile','245-537891'),
41. ->(6,'mobile','270-359457'),
42. ->(7,'mobile','245-436589'),
43. ->(7,'land','325-888887'),
44. ->(8,'mobile','245-279164'),
45. ->(8,'land','325-888888');
46. Query OK,12 rows affected (0.02 sec)
47. Records:12Duplicates:0Warnings:0
49. MariaDB[RUNOOB]>select*from employee;
50. +----+------------+-----------+------------------------+--------+-------+
51. | id | first_name | last_name | job_title | salary | notes |
52. +----+------------+-----------+------------------------+--------+-------+
53. |1|Robin|Jackman|SoftwareEngineer|5500| NULL |
54. |2|Taylor|Edward|SoftwareArchitect|7200| NULL |
55. |3|Vivian|Dickens|DatabaseAdministrator|6000| NULL |
56. |4|Harry|Clifford|DatabaseAdministrator|6800| NULL |
57. |5|Eliza|Clifford|SoftwareEngineer|4750| NULL |
58. |6|Nancy|Newman|SoftwareEngineer|5100| NULL |
59. |7|Melinda|Clifford|ProjectManager|8500| NULL |
60. |8|Harley|Gilbert|SoftwareArchitect|8000| NULL |
61. +----+------------+-----------+------------------------+--------+-------+
62. 8 rows inset(0.00 sec)
64. MariaDB[RUNOOB]>select*from telephone;
65. +----+-------------+--------+------------+
66. | id | employee_id | type |no|
67. +----+-------------+--------+------------+
68. |1|1| mobile |245-249697|
69. |2|2| mobile |270-235969|
70. |3|2| land |325-888885|
71. |4|3| mobile |270-684972|
72. |5|4| mobile |245-782365|
73. |6|4| land |325-888886|
74. |7|5| mobile |245-537891|
75. |8|6| mobile |270-359457|
76. |9|7| mobile |245-436589|
77. |10|7| land |325-888887|
78. |11|8| mobile |245-279164|
79. |12|8| land |325-888888|
80. +----+-------------+--------+------------+
81. 12 rows inset(0.00 sec)

2、在下面第一个语句中，employee_id是在第一条语句中指定的，设想一下，当第一条语句失败，而第二条语句成功的状况。在这种状况下，telephone表中就会有一条employee_id为9的记录，而employee表中并没有id为9的记录，而如果将这两个语句放在MySQL事务中，如果第一条语句失败，那么第二条语句也将回滚，从而不会造成这种问题。

1. INSERT INTO `employee`(`id`,`first_name`,`last_name`,`job_title`,`salary`) VALUES (9,'Grace','Williams','Softwaree Engineer',5000);
2. INSERT INTO `telephone`(`id`,`employee_id`,`type`,`no`) VALUES (13,9,'mobile','270-598712');

3、PHP脚本如下

我们试着通过PHP插入上面的两条数据，并故意将第1条语句写错。

1. <?php
2. //$salary = 5000; //这句话才是正确的，下面这句是错误的。
3. $salary ='$5000';
5. /* Change database details according to your database */
6. $dbConnection = mysqli_connect('localhost','root','password','RUNOOB');
8. mysqli_autocommit($dbConnection,false);
10. $flag =true;
12. $query1 ="INSERT INTO `employee` (`id`, `first_name`, `last_name`, `job_title`, `salary`) VALUES (9, 'Grace', 'Williams', 'Softwaree Engineer', $salary)";
13. $query2 ="INSERT INTO `telephone` (`id`, `employee_id`, `type`, `no`) VALUES (13, 9, 'mobile', '270-598712')";
15. $result = mysqli_query($dbConnection, $query1);
17. if(!$result){
18. $flag =false;
19. echo "Error details: ". mysqli_error($dbConnection).". ";
20. }
22. $result = mysqli_query($dbConnection, $query2);
24. if(!$result){
25. $flag =false;
26. echo "Error details: ". mysqli_error($dbConnection).". ";
27. }
29. if($flag){
31. mysqli_commit($dbConnection);
32. echo "All queries were executed successfully";
34. }else{
36. mysqli_rollback($dbConnection);
37. echo "All queries were rolled back";
39. }
41. mysqli_close($dbConnection);
43. ?>

4、运行结果如下：

 

5、运行后的数据表如下，数据表都回滚了：

1. MariaDB[RUNOOB]>select*from employee;
2. +----+------------+-----------+------------------------+--------+-------+
3. | id | first_name | last_name | job_title | salary | notes |
4. +----+------------+-----------+------------------------+--------+-------+
5. |1|Robin|Jackman|SoftwareEngineer|5500| NULL |
6. |2|Taylor|Edward|SoftwareArchitect|7200| NULL |
7. |3|Vivian|Dickens|DatabaseAdministrator|6000| NULL |
8. |4|Harry|Clifford|DatabaseAdministrator|6800| NULL |
9. |5|Eliza|Clifford|SoftwareEngineer|4750| NULL |
10. |6|Nancy|Newman|SoftwareEngineer|5100| NULL |
11. |7|Melinda|Clifford|ProjectManager|8500| NULL |
12. |8|Harley|Gilbert|SoftwareArchitect|8000| NULL |
13. +----+------------+-----------+------------------------+--------+-------+
14. 8 rows inset(0.02 sec)
16. MariaDB[RUNOOB]>select*from telephone;
17. +----+-------------+--------+------------+
18. | id | employee_id | type |no|
19. +----+-------------+--------+------------+
20. |1|1| mobile |245-249697|
21. |2|2| mobile |270-235969|
22. |3|2| land |325-888885|
23. |4|3| mobile |270-684972|
24. |5|4| mobile |245-782365|
25. |6|4| land |325-888886|
26. |7|5| mobile |245-537891|
27. |8|6| mobile |270-359457|
28. |9|7| mobile |245-436589|
29. |10|7| land |325-888887|
30. |11|8| mobile |245-279164|
31. |12|8| land |325-888888|
32. +----+-------------+--------+------------+
33. 12 rows inset(0.00 sec)

6、注意：

1、当执行mysqli_query函数的时候，结果被立即提交到了数据库。使用mysqli_autocommit函数，可以关闭自动提交，执行结果只有当你想提交的时候才提交。

2、如果任何语句执行失败我们都可以设置$flag变量为false。如果有很多语句要执行，可以考虑将他们放在for循环中。

3、最后，如果flag是true（也就是没有错误发生），我们使用mysqli_commit提交事务。否则我们使用mysqli_rollback回滚事务。所以事务可以在某种程度上帮助我们维护数据的完整和正确，另外，为了保证数据无误，我们还推荐使用外键。

4、并不是所有的语句都是支持事务的，例如，CREATE TABLE或者ALTER TABLE语句。