使用数据库事务可以确保除事务性单元内的所有操作都成功完成。MySQL中的InnoDB引擎的表才支持transaction。在一个事务里,如果出现一个数据库操作失败了,事务内的所有操作将被回滚,数据库将会回到事务前的初始状态。有一些不能被回滚的语句:将在本文的最后讨论。
在一个web应用中,会很经常遇到需要使用事务的地方,要么希望若干语句都执行成功,要么都不执行,如果出现有些执行成功,而其他的失败将会导致数据损坏。
在这篇文章的例子中,我们使用下面的两张表"employee"和"telephone",下面是SQL语句(作为参考):
创建 employee表:
1
2
3
4
5
6
7
8
9
|
CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar (100) NOT NULL ,
`last_name` varchar (100) NOT NULL ,
`job_title` varchar (100) DEFAULT NULL ,
`salary` double DEFAULT NULL ,
`notes` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
向employee中插入数据
1
2
3
4
5
6
7
8
9
|
INSERT INTO `employee` (`first_name`, `last_name`, `job_title`, `salary`) VALUES
( 'Robin' , 'Jackman' , 'Software Engineer' , 5500),
( 'Taylor' , 'Edward' , 'Software Architect' , 7200),
( 'Vivian' , 'Dickens' , 'Database Administrator' , 6000),
( 'Harry' , 'Clifford' , 'Database Administrator' , 6800),
( 'Eliza' , 'Clifford' , 'Software Engineer' , 4750),
( 'Nancy' , 'Newman' , 'Software Engineer' , 5100),
( 'Melinda' , 'Clifford' , 'Project Manager' , 8500),
( 'Harley' , 'Gilbert' , 'Software Architect' , 8000);
|
创建telephone表
1
2
3
4
5
6
7
|
CREATE TABLE `telephone` (
`id` int NOT NULL AUTO_INCREMENT,
`employee_id` int DEFAULT NULL ,
`type` varchar (20) NOT NULL ,
` no ` varchar (50) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
向telephone表插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
|
INSERT INTO `telephone` (`employee_id`, `type`, ` no `) VALUES
(1, 'mobile' , '245-249697' ),
(2, 'mobile' , '270-235969' ),
(2, 'land' , '325-888885' ),
(3, 'mobile' , '270-684972' ),
(4, 'mobile' , '245-782365' ),
(4, 'land' , '325-888886' ),
(5, 'mobile' , '245-537891' ),
(6, 'mobile' , '270-359457' ),
(7, 'mobile' , '245-436589' ),
(7, 'land' , '325-888887' ),
(8, 'mobile' , '245-279164' ),
(8, 'land' , '325-888888' );
|
设想你需要一个新的叫做Grace Williams雇员,并带有他的电话号码信息。你可能会执行下面两句sql:
1
2
3
4
5
6
|
INSERT INTO `employee` (`id`, `first_name`, `last_name`,
`job_title`, `salary`) VALUES (9, 'Grace' , 'Williams' ,
'Softwaree Engineer' , 5000);
INSERT INTO `telephone` (`id`, `employee_id`, `type`,
` no `) VALUES (13, 9, 'mobile' , '270-598712' );
|
让我们看看第二个语句,在第一个语句中,employee_id是在第一条语句中指定的,设想一下,当第一条语句失败,而第二条语句成功的状况。在这种状况下,telephone表中就会有一条employee_id为9的记录,而employee表中并没有id为9的记录,而如果将这两个语句放在MySQL事务中,如果第一条语句失败,那么第二条语句也将回滚,从而不会造成这种问题。
在PHP(PHP参考文档)中我们可以使用如下的方式启用事务:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
<?php
//$salary = 5000;
$salary = '$5000' ;
/* Change database details according to your database */
$dbConnection = mysqli_connect( 'localhost' , 'robin' , 'robin123' , 'company_db' );
mysqli_autocommit( $dbConnection , false);
$flag = true;
$query1 = "INSERT INTO `employee` (`id`, `first_name`, `last_name`, `job_title`, `salary`) VALUES (9, 'Grace', 'Williams', 'Softwaree Engineer', $salary)" ;
$query2 = "INSERT INTO `telephone` (`id`, `employee_id`, `type`, `no`) VALUES (13, 9, 'mobile', '270-598712')" ;
$result = mysqli_query( $dbConnection , $query1 );
if (! $result ) {
$flag = false;
echo "Error details: " . mysqli_error( $dbConnection ) . ". " ;
}
$result = mysqli_query( $dbConnection , $query2 );
if (! $result ) {
$flag = false;
echo "Error details: " . mysqli_error( $dbConnection ) . ". " ;
}
if ( $flag ) {
mysqli_commit( $dbConnection );
echo "All queries were executed successfully" ;
} else {
mysqli_rollback( $dbConnection );
echo "All queries were rolled back" ;
}
mysqli_close( $dbConnection );
?>
|
当你执行mysqli_query函数的时候,结果被立即提交到了数据库。使用mysqli_autocommit函数,可以关闭自动提交,执行结果只有当你想提交的时候才提交。
如果任何语句执行失败我们都可以设置$flag变量为false。如果有很多语句要执行,可以考虑将他们放在for循环中。
最后,如果flag是true(也就是没有错误发生),我们使用mysqli_commit提交事务。否则我们使用mysqli_rollback回滚事务。
所以,事务可以在某种程度上帮助我们维护数据的完整和正确,另外,为了保证数据无误,我们还推荐使用外键。
并不是所有的语句都是支持事务的,例如,如果使用CREATE TABLE或者ALTER TABLE语句,需要了解更多可以参考MySQL手册查看哪些语句不能回滚。