MySQL事務

類別: IT
標籤: sql mysql

使用資料庫事務可以確保除事務性單元內的所有操作都成功完成。MySQL中的InnoDB引擎的表才支援transaction。在一個事務裡,如果出現一個資料庫操作失敗了,事務內的所有操作將被回滾,資料庫將會回到事務前的初始狀態。有一些不能被回滾的語句:將在本文的最後討論。

在一個web應用中,會很經常遇到需要使用事務的地方,要麼希望若干語句都執行成功,要麼都不執行,如果出現有些執行成功,而其他的失敗將會導致資料損壞。

在這篇文章的例子中,我們使用下面的兩張表"employee"和"telephone",下面是SQL語句(作為參考):

建立 employee表:

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中插入資料

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表
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表插入資料

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');
id first_name last_name job_title salary notes
1 Robin Jackman Software Engineer 5500  
2 Taylor Edward Software Architect 7200  
3 Vivian Dickens Database Administrator 6000  
4 Harry Clifford Database Administrator 6800  
5 Eliza Clifford Software Engineer 4750  
6 Nancy Newman Software Engineer 5100  
7 Melinda Clifford Project Manager 8500  
8 Harley Gilbert Software Architect 8000  

id employee_id type no
1 1 mobile 245-249697
2 2 mobile 270-235969
3 2 land 325-888885
4 3 mobile 270-684972
5 4 mobile 245-782365
6 4 land 325-888886
7 5 mobile 245-537891
8 6 mobile 270-359457
9 7 mobile 245-436589
10 7 land 325-888887
11 8 mobile 245-279164
12 8 land 325-888888

設想你需要一個新的叫做Grace Williams僱員,並帶有他的電話號碼資訊。你可能會執行下面兩句sql: 

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事務中,如果第一條語句失敗,那麼第二條語句也將回滾,從而不會造成這種問題。

PHPPHP參考文件)中我們可以使用如下的方式啟用事務:

<?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手冊檢視哪些語句不能回滾。

MySQL事務原文請看這裡