詳述mysql資料庫中的事務（詳述mysql資料庫中的事務管理）

本文目錄一覽：

• 1、資料庫（mysql）關鍵知識
• 2、MySQL知識點總結
• 3、什麼是事務?具有哪些特性?mysql中
• 4、Mysql中的事務是什麼如何使用
• 5、mysql事務可以做什麼

資料庫（mysql）關鍵知識

Mysql是目前互聯網使用最廣的關係資料庫，關係資料庫的本質是將問題分解為多個分類然後通過關係來查詢。 一個經典的問題是用戶借書，三張表，一個用戶，一個書，一個借書的關係表。當需要查詢某個用戶借書情況或者是書被那些人借了，就用關係查詢來實現。

關係資料庫範式

來自英文Normal form，簡稱NF。要想設計—個好的關係，必須使關係滿足一定的約束條件，滿足這些規範的資料庫是簡潔的、結構明晰的，同時，不會發生插入(insert)、刪除(delete)和更新(update)操作異常。總共有六種範式：第一範式（1NF）、第二範式（2NF）、 第三範式 （3NF）、巴斯-科德範式（BCNF）、 第四範式 (4NF）和 第五範式 （5NF，又稱完美範式）。

1NF是指資料庫表的每一列都是不可分割的原子數據項。2NF必須滿足1NF，要求資料庫表中的每行記錄必須可以被唯一地區分。3NF在2NF基礎上，任何非主 屬性 不依賴於其它非主屬性（在2NF基礎上消除傳遞依賴）。BCNF是在3NF基礎上，任何非主屬性不能對主鍵子集依賴（在3NF基礎上消除對主碼子集的依賴）， 滿足BCNF不再會有任何由於函數依賴導致的異常，但是我們還可能會遇到由於多值依賴導致的異常。4NF的定義很簡單：已經是BC範式，並且不包含多值依賴關係。5NF處理的是無損連接問題，這個範式基本沒有實際意義，因為無損連接很少出現，而且難以察覺。而域鍵範式試圖定義一個終極範式，該範式考慮所有的依賴和約束類型，但是實用價值也是最小的，只存在理論研究中。

Catalog和Schema

是資料庫對象命名空間中的層次，主要用來解決命名衝突的問題。從概念上說，一個資料庫系統包含多個Catalog，每個Catalog又包含多個Schema，而每個Schema又包含多個資料庫對象（表、視圖、欄位等）。但是Mysql的資料庫名就是Schema，不支持Catalog。

Mysql的資料庫引擎主要有兩種MyISAM和InnoDB，MyISAM支持全文檢索，InnoDB支持事務。

SQL中的通配符『%』代表任意字元出現任意次數。『_』代表任意字元出現一次。SQL與正則表達式結合查詢一般用在WHERE table_name REGEXP ‘^12.34’。子查詢是從裡到外執行。

資料庫聯結（join）涉及到外鍵，外鍵是指一個表的列是另一個表的主鍵，那麼它就是外鍵。笛卡爾積聯結（不指定聯結條件時）生成的記錄條目是單純的第一個表的行乘以第二個表的列數。用得最多的是等值聯結也叫內部聯結。

高級聯結還有自連接，是指查詢中的兩張表是同一張表，它通常作為外部語句用來代替從相同表中檢索數據時使用的子查詢。自然聯結使每個列只返回一次。外部聯結是指聯結包含了那些在相關表中沒有關聯行的行。例如列出所有產品及其訂購數量，包括沒有人訂購的產品。LEFT OUTER JOIN指選擇左邊表的所有行。

組合查詢是指採用UNION等將兩個查詢結果取並集。

視圖是查看存儲在別處的數據的一種工具，它本身並不包含數據，因此表的數據修改了，視圖返回的數據也將隨之修改，因此如果使用了複雜或嵌套視圖會對性能有較大的影響。視圖的作用之一是隱藏複雜的SQL通常會涉及到聯結查詢。

存儲過程類似於批處理，包含了一條或多條SQL語句。語法：

CREATE PROCEDURE name（）

BEGIN

SQL

END

————————-

CALL name（）//來調用存儲過程

游標有DECLARE定義，游標與存儲過程是綁定的，存儲過程處理完成，游標就會消失。游標被打開後可以使用FETCH語句訪問每一行。

觸發器是在某個時間發生時自動執行某條SQL語句。語法：

CREATE TRIGGER name AFTER INSERT ON talbe_name FOR EACH ROW

事務處理可以維護資料庫的完整性，保證批量的操作要麼完全執行，要麼完全不執行。包括事務、回退、提交、保留點幾個關鍵術語。ROLLBACK只能在一個事務處理內使用。他不能回退CREATE和DROP操作。使用COMMIT保證事務提交。複雜的事務處理需要部分提交或回退，因此我們需要使用保留點SAVEPOINT。可以使用ROLLBACK TO savepoint_name。保留點越多越好。保留點在事務執行完成後自動釋放。

MySQL知識點總結

只要欄位值還可以繼續拆分，就不滿足第一範式。

範式設計得越詳細，對某些實際操作可能會更好，但並非都有好處，需要對項目的實際情況進行設定。

在滿足第一範式的前提下，其他列都必須完全依賴於主鍵列。 如果出現不完全依賴，只可能發生在聯合主鍵的情況下：

實際上，在這張訂單表中，product_name 只依賴於 product_id ，customer_name 只依賴於 customer_id。也就是說，product_name 和 customer_id 是沒用關係的，customer_name 和 product_id 也是沒有關係的。

這就不滿足第二範式：其他列都必須完全依賴於主鍵列！

拆分之後，myorder 表中的 product_id 和 customer_id 完全依賴於 order_id 主鍵，而 product 和 customer 表中的其他欄位又完全依賴於主鍵。滿足了第二範式的設計！

在滿足第二範式的前提下，除了主鍵列之外，其他列之間不能有傳遞依賴關係。

表中的 customer_phone 有可能依賴於 order_id 、 customer_id 兩列，也就不滿足了第三範式的設計：其他列之間不能有傳遞依賴關係。

修改後就不存在其他列之間的傳遞依賴關係，其他列都只依賴於主鍵列，滿足了第三範式的設計！

查詢每門課的平均成績。

查詢 score 表中至少有 2 名學生選修，並以 3 開頭的課程的平均分數。

分析表發現，至少有 2 名學生選修的課程是 3-105 、3-245 、6-166 ，以 3 開頭的課程是 3-105 、3-245。也就是說，我們要查詢所有 3-105 和 3-245 的 degree 平均分。

查詢所有學生的 name，以及該學生在 score 表中對應的 c_no 和 degree 。

通過分析可以發現，只要把 score 表中的 s_no 欄位值替換成 student 表中對應的 name 欄位值就可以了，如何做呢？

查詢所有學生的 no 、課程名稱 ( course 表中的 name ) 和成績 ( score 表中的 degree ) 列。

只有 score 關聯學生的 no ，因此只要查詢 score 表，就能找出所有和學生相關的 no 和 degree ：

然後查詢 course 表：

只要把 score 表中的 c_no 替換成 course 表中對應的 name 欄位值就可以了。

查詢所有學生的 name 、課程名 ( course 表中的 name ) 和 degree 。

只有 score 表中關聯學生的學號和課堂號，我們只要圍繞著 score 這張表查詢就好了。

只要把 s_no 和 c_no 替換成 student 和 srouse 表中對應的 name 欄位值就好了。

首先把 s_no 替換成 student 表中的 name 欄位：

再把 c_no 替換成 course 表中的 name 欄位：

查詢 95031 班學生每門課程的平均成績。

在 score 表中根據 student 表的學生編號篩選出學生的課堂號和成績：

這時只要將 c_no 分組一下就能得出 95031 班學生每門課的平均成績：

查詢在 3-105 課程中，所有成績高於 109 號同學的記錄。

首先篩選出課堂號為 3-105 ，在找出所有成績高於 109 號同學的的行。

查詢所有成績高於 109 號同學的 3-105 課程成績記錄。

查詢所有和 101 、108 號學生同年出生的 no 、name 、birthday 列。

查詢 ‘張旭’ 教師任課的學生成績表。

首先找到教師編號：

通過 sourse 表找到該教師課程號：

通過篩選出的課程號查詢成績表：

查詢某選修課程多於5個同學的教師姓名。

首先在 teacher 表中，根據 no 欄位來判斷該教師的同一門課程是否有至少5名學員選修：

查看和教師編號有有關的表的信息：

我們已經找到和教師編號有關的欄位就在 course 表中，但是還無法知道哪門課程至少有5名學生選修，所以還需要根據 score 表來查詢：

根據篩選出來的課程號，找出在某課程中，擁有至少5名學員的教師編號：

在 teacher 表中，根據篩選出來的教師編號找到教師姓名：

查詢 「計算機系」 課程的成績表。

思路是，先找出 course 表中所有 計算機系 課程的編號，然後根據這個編號查詢 score 表。

查詢 計算機系 與 電子工程系 中的不同職稱的教師。

查詢課程 3-105 且成績 至少 高於 3-245 的 score 表。

查詢課程 3-105 且成績高於 3-245 的 score 表。

查詢某課程成績比該課程平均成績低的 score 表。

查詢所有任課 ( 在 course 表裡有課程 ) 教師的 name 和 department 。

查詢 student 表中至少有 2 名男生的 class 。

查詢 student 表中不姓 “王” 的同學記錄。

查詢 student 表中每個學生的姓名和年齡。

查詢 student 表中最大和最小的 birthday 值。

以 class 和 birthday 從大到小的順序查詢 student 表。

查詢 “男” 教師及其所上的課程。

查詢最高分同學的 score 表。

查詢和 “李軍” 同性別的所有同學 name 。

查詢和 “李軍” 同性別且同班的同學 name 。

查詢所有選修 “計算機導論” 課程的 “男” 同學成績表。

需要的 “計算機導論” 和性別為 “男” 的編號可以在 course 和 student 表中找到。

建立一個 grade 表代表學生的成績等級，並插入數據：

查詢所有學生的 s_no 、c_no 和 grade 列。

思路是，使用區間 ( BETWEEN ) 查詢，判斷學生的成績 ( degree ) 在 grade 表的 low 和 upp 之間。

準備用於測試連接查詢的數據：

分析兩張表發現，person 表並沒有為 cardId 欄位設置一個在 card 表中對應的 id 外鍵。如果設置了的話，person 中 cardId 欄位值為 6 的行就插不進去，因為該 cardId 值在 card 表中並沒有。

要查詢這兩張表中有關係的數據，可以使用 INNER JOIN ( 內連接 ) 將它們連接在一起。

完整顯示左邊的表 ( person ) ，右邊的表如果符合條件就顯示，不符合則補 NULL 。

完整顯示右邊的表 ( card ) ，左邊的表如果符合條件就顯示，不符合則補 NULL 。

完整顯示兩張表的全部數據。

在 MySQL 中，事務其實是一個最小的不可分割的工作單元。事務能夠 保證一個業務的完整性 。

比如我們的銀行轉賬：

在實際項目中，假設只有一條 SQL 語句執行成功，而另外一條執行失敗了，就會出現數據前後不一致。

因此，在執行多條有關聯 SQL 語句時， 事務 可能會要求這些 SQL 語句要麼同時執行成功，要麼就都執行失敗。

在 MySQL 中，事務的 自動提交 狀態默認是開啟的。

自動提交的作用 ：當我們執行一條 SQL 語句的時候，其產生的效果就會立即體現出來，且不能 回滾 。

什麼是回滾？舉個例子：

可以看到，在執行插入語句後數據立刻生效，原因是 MySQL 中的事務自動將它 提交 到了資料庫中。那麼所謂 回滾 的意思就是，撤銷執行過的所有 SQL 語句，使其回滾到 最後一次提交 數據時的狀態。

在 MySQL 中使用 ROLLBACK 執行回滾：

由於所有執行過的 SQL 語句都已經被提交過了，所以數據並沒有發生回滾。那如何讓數據可以發生回滾？

將自動提交關閉後，測試數據回滾：

那如何將虛擬的數據真正提交到資料庫中？使用 COMMIT :

事務的實際應用 ，讓我們再回到銀行轉賬項目：

這時假設在轉賬時發生了意外，就可以使用 ROLLBACK 回滾到最後一次提交的狀態：

這時我們又回到了發生意外之前的狀態，也就是說，事務給我們提供了一個可以反悔的機會。假設數據沒有發生意外，這時可以手動將數據真正提交到數據表中：COMMIT 。

事務的默認提交被開啟 ( @@AUTOCOMMIT = 1 ) 後，此時就不能使用事務回滾了。但是我們還可以手動開啟一個事務處理事件，使其可以發生回滾：

仍然使用 COMMIT 提交數據，提交後無法再發生本次事務的回滾。

事務的四大特徵：

事務的隔離性可分為四種 ( 性能從低到高 ) ：

查看當前資料庫的默認隔離級別：

修改隔離級別：

測試 READ UNCOMMITTED ( 讀取未提交 ) 的隔離性：

由於小明的轉賬是在新開啟的事務上進行操作的，而該操作的結果是可以被其他事務（另一方的淘寶店）看見的，因此淘寶店的查詢結果是正確的，淘寶店確認到賬。但就在這時，如果小明在它所處的事務上又執行了 ROLLBACK 命令，會發生什麼？

這就是所謂的 臟讀 ，一個事務讀取到另外一個事務還未提交的數據。這在實際開發中是不允許出現的。

把隔離級別設置為 READ COMMITTED ：

這樣，再有新的事務連接進來時，它們就只能查詢到已經提交過的事務數據了。但是對於當前事務來說，它們看到的還是未提交的數據，例如：

但是這樣還有問題，那就是假設一個事務在操作數據時，其他事務干擾了這個事務的數據。例如：

雖然 READ COMMITTED 讓我們只能讀取到其他事務已經提交的數據，但還是會出現問題，就是 在讀取同一個表的數據時，可能會發生前後不一致的情況。* 這被稱為* 不可重複讀現象 ( READ COMMITTED ) 。

將隔離級別設置為 REPEATABLE READ ( 可被重複讀取 ) :

測試 REPEATABLE READ ，假設在兩個不同的連接上分別執行 START TRANSACTION :

當前事務開啟後，沒提交之前，查詢不到，提交後可以被查詢到。但是，在提交之前其他事務被開啟了，那麼在這條事務線上，就不會查詢到當前有操作事務的連接。相當於開闢出一條單獨的線程。

無論小張是否執行過 COMMIT ，在小王這邊，都不會查詢到小張的事務記錄，而是只會查詢到自己所處事務的記錄：

這是 因為小王在此之前開啟了一個新的事務 ( START TRANSACTION ) * ，那麼* 在他的這條新事務的線上，跟其他事務是沒有聯繫的 ，也就是說，此時如果其他事務正在操作數據，它是不知道的。

然而事實是，在真實的數據表中，小張已經插入了一條數據。但是小王此時並不知道，也插入了同一條數據，會發生什麼呢？

報錯了，操作被告知已存在主鍵為 6 的欄位。這種現象也被稱為 幻讀，一個事務提交的數據，不能被其他事務讀取到 。

顧名思義，就是所有事務的 寫入操作 全都是串列化的。什麼意思？把隔離級別修改成 SERIALIZABLE :

還是拿小張和小王來舉例：

此時會發生什麼呢？由於現在的隔離級別是 SERIALIZABLE ( 串列化 ) ，串列化的意思就是：假設把所有的事務都放在一個串列的隊列中，那麼所有的事務都會按照 固定順序執行 ，執行完一個事務後再繼續執行下一個事務的 寫入操作 ( 這意味著隊列中同時只能執行一個事務的寫入操作 ) 。

根據這個解釋，小王在插入數據時，會出現等待狀態，直到小張執行 COMMIT 結束它所處的事務，或者出現等待超時。

轉載：

什麼是事務?具有哪些特性?mysql中

事務是資料庫操作最小單元，把多件事當一件事來處理，是一組不可在分割的操作集合。作為單個邏輯工作單元執行一系列操作，這些操作作為一個整體一起向系統提交，要麼都執行，要麼都不執行。

特性ACID（原子性、一致性、隔離性、持久性）

原子性：原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾，，因此事務操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有一點影響。

一致性：一致性是指事務必須使資料庫從一個一致性狀態轉換到另一個一致性狀態，就是說事務執行前後都必須處在一致性狀態。

隔離性：隔離性使當多個用戶訪問資料庫時，比如操作同一張表，資料庫開啟的每一個事務，不能被其它事務干擾，多個並發事務之間相互隔離。

持久性：持久性是指一個事務一旦提交，對資料庫中數據的改變是永久的，即使是資料庫系統遇到故障也不會丟失提交的事務操作。

Mysql中的事務是什麼如何使用

什麼是事務？

事務是邏輯上的一組操作，組成這組操作的各個單元，要不全都成功要不全都失敗，這個特性就是事務

注意：mysql數據支持事務，但是要求必須是innoDB存儲引擎

解決這個問題：

mysql的事務解決這個問題，因為mysql的事務特性，要求這組操作，要不全都成功，要不全都失敗，這樣就避免了某個操作成功某個操作失敗。利於數據的安全

如何使用：

（1）在執行sql語句之前，我們要開啟事務 start transaction;

（2）正常執行我們的sql語句

（3）當sql語句執行完畢，存在兩種情況：

1，全都成功，我們要將sql語句對資料庫造成的影響提交到資料庫中，committ

2，某些sql語句失敗，我們執行rollback（回滾），將對資料庫操作趕緊撤銷

（注意：mysql數據支持事務，但是要求必須是innoDB存儲引擎）

mysql create table bank(name varchar(20),money decimal(5,1))engine=innodb defau

lt charset=utf8;

mysql inset into bank values(‘shaotuo’,1000),(‘laohu’,5000);

mysql select*from bank;

+———+——–+

| name | money |

+———+——–+

| shaotuo | 1000.0 |

| laohu | 5000.0 |

+———+——–+

——沒有成功「回滾」執行rollback

mysql start transaction; //開啟事務

Query OK, 0 rows affected (0.00 sec)

mysql update bank set money=money+500 where name=’shaotuo’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql update bank set moey=money-500 where name=’laohu’;

ERROR 1054 (42S22): Unknown column ‘moey’ in ‘field list’

mysql rollback; //只要有一個不成功，執行rollback操作

Query OK, 0 rows affected (0.01 sec)

mysql select*from bank;

+———+——–+

| name | money |

+———+——–+

| shaotuo | 1000.0 |

| laohu | 5000.0 |

+———+——–+

——成功之後 進行commit操作

mysql start transaction; //開啟事務

Query OK, 0 rows affected (0.00 sec)

mysql update bank set money=money+500 where name=’shaotuo’;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql update bank set money=money-500 where name=’laohu’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql commit; //兩個都成功後執行commit（只要不執行commit，sql語句不會對真實的資料庫造成影響）

Query OK, 0 rows affected (0.05 sec)

mysql select*from bank;

+———+——–+

| name | money |

+———+——–+

| shaotuo | 1500.0 |

| laohu | 4500.0 |

+———+——–+

mysql事務可以做什麼

MySQL 事務主要用於處理操作量大，複雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！

在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支持事務。

事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。

事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。