深入理解MySQL中的事務機制

更新時間：2015年06月26日 10:23:50 投稿：goldensun

這篇文章主要介紹了MySQL中的事務機制,事務機制在各大MySQL教程中均為重要知識,需要的朋友可以參考下

使用數(shù)據(jù)庫事務可以確保除事務性單元內的所有操作都成功完成。MySQL中的InnoDB引擎的表才支持transaction。在一個事務里，如果出現(xiàn)一個數(shù)據(jù)庫操作失敗了，事務內的所有操作將被回滾，數(shù)據(jù)庫將會回到事務前的初始狀態(tài)。有一些不能被回滾的語句：將在本文的最后討論。

在一個web應用中，會很經(jīng)常遇到需要使用事務的地方，要么希望若干語句都執(zhí)行成功，要么都不執(zhí)行，如果出現(xiàn)有些執(zhí)行成功，而其他的失敗將會導致數(shù)據(jù)損壞。

在這篇文章的例子中，我們使用下面的兩張表"employee"和"telephone"，下面是SQL語句（作為參考）：

創(chuàng)建 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中插入數(shù)據(jù)

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);

創(chuàng)建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表插入數(shù)據(jù)

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');

2015626102143340.jpg (447×584)

設想你需要一個新的叫做Grace Williams雇員，并帶有他的電話號碼信息。你可能會執(zhí)行下面兩句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是在第一條語句中指定的，設想一下，當?shù)谝粭l語句失敗，而第二條語句成功的狀況。在這種狀況下，telephone表中就會有一條employee_id為9的記錄，而employee表中并沒有id為9的記錄，而如果將這兩個語句放在MySQL事務中，如果第一條語句失敗，那么第二條語句也將回滾，從而不會造成這種問題。

在PHP（PHP參考文檔）中我們可以使用如下的方式啟用事務：

<?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);
 
?>

    當你執(zhí)行mysqli_query函數(shù)的時候，結果被立即提交到了數(shù)據(jù)庫。使用mysqli_autocommit函數(shù)，可以關閉自動提交，執(zhí)行結果只有當你想提交的時候才提交。
    如果任何語句執(zhí)行失敗我們都可以設置$flag變量為false。如果有很多語句要執(zhí)行，可以考慮將他們放在for循環(huán)中。
    最后，如果flag是true（也就是沒有錯誤發(fā)生），我們使用mysqli_commit提交事務。否則我們使用mysqli_rollback回滾事務。

所以，事務可以在某種程度上幫助我們維護數(shù)據(jù)的完整和正確，另外，為了保證數(shù)據(jù)無誤，我們還推薦使用外鍵。

并不是所有的語句都是支持事務的，例如，如果使用CREATE TABLE或者ALTER TABLE語句，需要了解更多可以參考MySQL手冊查看哪些語句不能回滾。

您可能感興趣的文章: