PHP+MySQL網(wǎng)站開發(fā)MySQL數(shù)據(jù)庫(kù)進(jìn)階

模塊三MySQL數(shù)據(jù)庫(kù)進(jìn)階面向?qū)ο缶幊蘌DO數(shù)據(jù)庫(kù)抽象層MySQL數(shù)據(jù)庫(kù)進(jìn)階MVC開發(fā)模式任務(wù)一任務(wù)二任務(wù)三索引外鍵約束事務(wù)處理目錄簡(jiǎn)介索引就是對(duì)數(shù)據(jù)庫(kù)單列或者多列地值進(jìn)行排序后地一種特殊數(shù)據(jù)庫(kù)結(jié)構(gòu),可以用來快速查詢數(shù)據(jù)庫(kù)表地特定記錄。索引是提高數(shù)據(jù)庫(kù)性能地重要方式,就像書籍地目錄,可以幫助用戶有效地提高查找內(nèi)容地速度。索引分類創(chuàng)建索引刪除索引任務(wù)一:索引索引分類根據(jù)索引地應(yīng)用范圍與查詢需求地不同,MySQL常見地索引大致可以分為7種。分類說明普通索引普通索引就是不應(yīng)用任何限制條件地索引,是MySQL地基本索引類型。它是由關(guān)鍵字KEY或INDEX進(jìn)行定義地。唯一性索引唯一性索引是由UNIQUE定義地索引,該索引所在字段地值需要是唯一地,用于防止創(chuàng)建重復(fù)地值。主鍵索引主鍵索引是一種特殊地唯一索引,它用于根據(jù)主鍵自身地唯一性標(biāo)識(shí)每條記錄。任務(wù)一:索引分類說明單列索引單列索引指地是在表單個(gè)字段上創(chuàng)建索引,它可以是普通索引,唯一索引,主鍵索引或者全文索引,只要保證該索引只對(duì)應(yīng)表一個(gè)字段即可。多列索引多列索引是在表地多個(gè)字段上創(chuàng)建一個(gè)索引,且只有在查詢條件使用了這些字段地第一個(gè)字段時(shí),該索引才會(huì)被使用。全文索引由FULLTEXT定義用于在查詢數(shù)據(jù)量較大地字符串類型字段時(shí),提高查詢速度。字段地?cái)?shù)據(jù)類型僅可以為CHAR,VARCHAR或TEXT地一種?？臻g索引提高系統(tǒng)獲取空間數(shù)據(jù)地效率,由SPATIAL定義在空間數(shù)據(jù)類型字段上地索引。其,MySQL地空間數(shù)據(jù)類型有4種,分別是GEOMETRY,POINT,LINESTRING與POLYGON。索引分類任務(wù)一:索引需要注意地是,全文索引在MySQL5.6以下地版本,只有MyISAM存儲(chǔ)引擎表支持全文檢索;而在MySQL5.6以上地版本,Innodb存儲(chǔ)引擎表也提供支持全文檢索。索引分類任務(wù)一:索引創(chuàng)建索引示例:在itcast數(shù)據(jù)庫(kù),創(chuàng)建數(shù)據(jù)表t1并為其創(chuàng)建普通索引:——普通索引CREATETABLE`t1`(`id`INT,`name`VARCHAR(20),`score`FLOAT,INDEX(`id`));從上述SQL語(yǔ)句可知,在創(chuàng)建數(shù)據(jù)表t1時(shí),為id字段創(chuàng)建了一個(gè)名稱為id地索引。任務(wù)一:索引示例:通過EXPLAIN語(yǔ)句查看索引是否被使用:EXPLAINSELECT*FROM`t1`WHERE`id`=1\G創(chuàng)建索引——普通索引任務(wù)一:索引從圖可以看出,possible_keys與key地值都為id,說明id索引已經(jīng)存在,并且已經(jīng)開始被使用了。創(chuàng)建索引——普通索引任務(wù)一:索引在test數(shù)據(jù)庫(kù),若在t2表地name字段上建立唯一性索引,那么name字段地值就需要是唯一地:——唯一性索引CREATETABLE`t2`(`id`INTNOTNULL,`name`VARCHAR(20)NOTNULL,`score`FLOAT,UNIQUEINDEXunique_id(`id`));上述代碼為數(shù)據(jù)表地id字段創(chuàng)建了一個(gè)唯一性索引,索引地名稱為unique_id。創(chuàng)建索引任務(wù)一:索引主鍵索引雖然也是唯一性索引,但與唯一性索引地不同處在于,每個(gè)表只能有一個(gè)主鍵索引,但是可以有多個(gè)唯一性索引:——主鍵索引CREATETABLE`t3`(`id`INTUNSIGNEDAUTO_INCREMENT,`name`VARCHAR(20)NOTNULL,`score`FLOAT,PRIMARYKEY(`id`));上述代碼為表地自增字段id創(chuàng)建了一個(gè)主鍵索引。一個(gè)表有且僅能有一個(gè)主鍵索引。創(chuàng)建索引任務(wù)一:索引單列索引,顧名思義就是僅對(duì)一個(gè)字段設(shè)定地索引:——單列索引CREATETABLE`t4`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(50)NOTNULL,`score`FLOAT,INDEXsingle(`name`(20)));在上述代碼,表地name字段長(zhǎng)度為50,但為了提高查詢效率,優(yōu)化查詢速度,為該字段創(chuàng)建了一個(gè)長(zhǎng)度為20地單列索引。創(chuàng)建索引任務(wù)一:索引如果在表地id,name與score字段上創(chuàng)建一個(gè)多列索引,那么只有查詢條件使用了id字段時(shí),該索引才會(huì)被使用:——多列索引CREATETABLE`t5`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(50)NOTNULL,`score`FLOAT,INDEXmulti(`id`,`name`(20),`score`));創(chuàng)建索引任務(wù)一:索引在數(shù)據(jù)表,對(duì)于帶有內(nèi)容描述地字段,可以應(yīng)用全文索引方式:——全文索引CREATETABLE`t6`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(50)NOTNULL,`description`VARCHAR(255),FULLTEXTINDEXfname(`description`))ENGINE=MyISAM;創(chuàng)建索引任務(wù)一:索引——全文索引需要注意地是,默認(rèn)情況下全文索引大小寫不敏感,如果索引地列使用二進(jìn)制排序后,可以執(zhí)行大小寫敏感地全文搜索。且全文索引不支持文搜索,使用時(shí)需要進(jìn)行有關(guān)地處理。創(chuàng)建索引任務(wù)一:索引在數(shù)據(jù)表,為數(shù)據(jù)類型為GEOMETRY地字段space創(chuàng)建空間索引:——空間索引CREATETABLE`t7`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`space`GEOMETRYNOTNULL,SPATIALINDEXsp(`space`))ENGINE=MyISAM;需要注意地是,只有MyISAM存儲(chǔ)引擎支持空間索引,且索引字段需要聲明為非空約束NOTNULL。創(chuàng)建索引任務(wù)一:索引——其它索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(字段名[(長(zhǎng)度)][ASC|DESC]);①CREATEINDEX方式ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名[(長(zhǎng)度)][ASC|DESC])②ALTERTABLE方式創(chuàng)建索引任務(wù)一:索引——其它索引UNIQUE,FULLTEXT與SPATIAL都是可選參數(shù),分別用于表示唯一性索引,全文索引與空間索引,INDEX用于指明字段為索引。創(chuàng)建索引任務(wù)一:索引對(duì)于數(shù)據(jù)表已經(jīng)創(chuàng)建但不再使用地索引,應(yīng)該及時(shí)刪除,避免占用系統(tǒng)資源,影響數(shù)據(jù)庫(kù)地性能。MySQL提供了兩種刪除索引地方法。刪除索引任務(wù)一:索引——使用ALTERTABLE刪除索引使用ALTERTABLE刪除索引地基本語(yǔ)法格式如下所示。ALTERTABLE表名DROPINDEX索引名;刪除索引任務(wù)一:索引接下來以刪除數(shù)據(jù)表t7地空間索引為例進(jìn)行演示,具體步驟如下。①使用SHOWCREATETABLE查看該表地結(jié)構(gòu),表t7地space字段地空間索引名稱為sp?！褂肁LTERTABLE刪除索引刪除索引任務(wù)一:索引使用ALTERTBALE刪除該索引,具體SQL語(yǔ)句如下。②執(zhí)行完上述SQL語(yǔ)句后,再次使用SHOWCREATETABLE查看表地結(jié)構(gòu)。ALTERTABLE`t7`DROPINDEX`sp`;——使用ALTERTABLE刪除索引刪除索引任務(wù)一:索引使用DROPINDEX刪除索引地基本語(yǔ)法格式如下。DROPINDEX索引名ON表名;——使用DROPINDEX刪除索引刪除索引任務(wù)一:索引

下面以刪除t6表地全文索引為例,演示如何使用DROPINDEX刪除索引。具體SQL語(yǔ)句如下。DROPINDEX`fname`ON`t6`;——使用DROPINDEX刪除索引刪除索引任務(wù)一:索引執(zhí)行完上述SQL語(yǔ)句后,使用SHOWCREATETABLE語(yǔ)句查看表地結(jié)構(gòu)?！褂肈ROPINDEX刪除索引刪除索引任務(wù)一:索引刪除索引值得一提地是,在項(xiàng)目開發(fā)引入索引后,系統(tǒng)在查詢時(shí)就不必遍歷數(shù)據(jù)表地所有記錄。這樣做地好處是,不但可以提高查詢速度,也可以降低服務(wù)器地負(fù)載;但也有缺點(diǎn),索引會(huì)占用物理空間,會(huì)給數(shù)據(jù)地維護(hù)造成很多麻煩,并且在創(chuàng)建與維護(hù)索引時(shí),其消耗地時(shí)間是隨著數(shù)據(jù)量地增加而增長(zhǎng)地。因此,項(xiàng)目開發(fā)時(shí),是否引入索引需酌情處理。任務(wù)一:索引什么是外鍵外鍵就是指引用另一個(gè)表地一列或多列,被引用地列應(yīng)該具有主鍵約束或唯一性約束,從而保證數(shù)據(jù)地一致性與完整性。其,被引用地表稱為主表;引用外鍵地表稱為從表。任務(wù)二:外鍵約束

添加外鍵約束要想在創(chuàng)建數(shù)據(jù)表（CREATETABLE）或修改數(shù)據(jù)結(jié)構(gòu)（ALTERTABLE）時(shí)添加外鍵約束,在相應(yīng)地位置添加以下SQL語(yǔ)句即可:[CONSTRAINTsymbol]FOREIGNKEY[index_name](index_col_name,...)REFERENCEStbl_name(index_col_name,...)[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]任務(wù)二:外鍵約束添加外鍵約束index_col_name,...:表示從表外鍵名稱列表tbl_name:表示主表index_col_name,...:表示主鍵約束或唯一性約束字段ONDELETE與ONUPDATE:用于設(shè)置主表地?cái)?shù)據(jù)被刪除或修改時(shí),從表對(duì)應(yīng)數(shù)據(jù)地處理辦法任務(wù)二:外鍵約束添加外鍵約束MYSQL可以通過"FOREIGNKEY...REFERENCES"修飾符向數(shù)據(jù)表添加外鍵約束。其,"CONSTRAINTsymbol"是可選參數(shù),用于表示外鍵約束名稱,如果省略,MYSQL將會(huì)自動(dòng)生成一個(gè)名字。"index_name"也是可選參數(shù),表示外鍵索引名稱,即使省略,MySQL也會(huì)在建立外鍵時(shí)自動(dòng)創(chuàng)建一個(gè)外鍵索引,加快查詢速度。任務(wù)二:外鍵約束添加外鍵約束添加外鍵約束地參數(shù)說明如下表所示。參數(shù)名稱功能描述RESTRICT默認(rèn)值。拒絕主表刪除或修改外鍵關(guān)聯(lián)字段CASCADE主表刪除或更新記錄時(shí),同時(shí)自動(dòng)刪除或更新從表對(duì)應(yīng)地記錄SETNULL主表刪除或更新記錄時(shí),使用NULL值替換從表對(duì)應(yīng)地記錄。（不適用于NOTNULL字段）NOACTION與默認(rèn)值RESTRICT相同,拒絕主表刪除或修改外鍵關(guān)聯(lián)字段SETDEFAULT設(shè)默認(rèn)值,但I(xiàn)nnoDB目前不支持任務(wù)二:外鍵約束添加外鍵約束需要注意地是,目前只有InnoDB引擎類型支持外鍵約束。且建立外鍵關(guān)系地兩個(gè)數(shù)據(jù)表地有關(guān)字段數(shù)據(jù)類型需要相似,也就是要求字段地?cái)?shù)據(jù)類型可以相互轉(zhuǎn)換。例如,INT與TINYINT類型地字段可以建立外鍵關(guān)系,而INT與CHAR類型地字段則不可以建立外鍵約束。接下來,在itcast數(shù)據(jù)庫(kù),以欄目表（category）與文章表（article）為例,講解如何添加外鍵約束。任務(wù)二:外鍵約束

添加外鍵約束#創(chuàng)建主表CREATETABLE`category`(`cid`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`ame`VARCHAR(32)NOTNULLMENT'欄目名稱')ENGINE=InnoDBCHARSET=utf8;#創(chuàng)建從表CREATETABLE`article`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`cid`INTUNSIGNEDNOTNULLMENT'文章所屬欄目ID',`name`VARCHAR(32)NOTNULLMENT'文章名稱',CONSTRAINTFK_IDFOREIGNKEY(`cid`)REFERENCES`category`(`cid`)ONDELETERESTRICTONUPDATECASCADE)ENGINE=InnoDBCHARSET=utf8;任務(wù)二:外鍵約束添加外鍵約束為從表article建立外鍵時(shí),設(shè)置拒絕主表category執(zhí)行刪除操作,且主表category進(jìn)行更新操作時(shí),從表article地有關(guān)字段也進(jìn)行更新操作。通過SHOWCREATETABLE可以查看創(chuàng)建外鍵是否成功,具體如圖所示。任務(wù)二:外鍵約束

添加外鍵約束值得一提地是,除了上述在建表時(shí)可以添加外鍵約束,對(duì)于已創(chuàng)建地?cái)?shù)據(jù)表,還可以通過ALTERTABLE地方式添加外鍵約束:ALTERTABLE`article`ADDCONSTRAINTFK_IDFOREIGNKEY(`cid`)REFERENCES`category`(`cid`)ONDELETERESTRICTONUPDATECASCADE;任務(wù)二:外鍵約束

刪除外鍵約束在實(shí)際開發(fā),根據(jù)業(yè)務(wù)邏輯地需求,需要解除兩個(gè)表之間地關(guān)聯(lián)關(guān)系時(shí),就要?jiǎng)h除外鍵約束。刪除外鍵約束地語(yǔ)法格式如下。ALTERTABLE表名DROPFOREIGNKEY外鍵名;任務(wù)二:外鍵約束

刪除外鍵約束下面以解除文章與欄目表之間地外鍵約束為例,具體SQL語(yǔ)句如下。ALTERTABLE`article`DROPFOREIGNKEYFK_ID;通過SHOWCREATETABLE可以查看外鍵約束是否刪除成功,具體如圖所示。值得一提地是,刪除外鍵約束并不會(huì)自動(dòng)刪除外鍵地索引,如果需要可以手動(dòng)刪除。任務(wù)二:外鍵約束關(guān)聯(lián)表操作在實(shí)際開發(fā),關(guān)聯(lián)表最常見地關(guān)系就是一對(duì)多。那么對(duì)于具有外鍵約束地關(guān)聯(lián)表而言,數(shù)據(jù)地插入,更新與刪除操作有哪些需要注意地問題呢？接下來以itcast數(shù)據(jù)庫(kù)地欄目表與文章信息表為例進(jìn)行講解。任務(wù)二:外鍵約束關(guān)聯(lián)表操作當(dāng)在一個(gè)具有外鍵約束地表插入數(shù)據(jù)時(shí),外鍵字段地值需要選取主表有關(guān)聯(lián)字段已經(jīng)存在地?cái)?shù)據(jù)。例如,欄目表與文章表之間是一對(duì)多地關(guān)系,文章表地外鍵cid插入地值只能是欄目表主鍵cid已經(jīng)存在地值?！迦霐?shù)據(jù)任務(wù)二:外鍵約束

關(guān)聯(lián)表操作為了驗(yàn)證兩個(gè)表地關(guān)聯(lián)關(guān)系,在文章表地cid字段添加外鍵約束后,插入數(shù)據(jù)進(jìn)行測(cè)試,具體如下?！迦霐?shù)據(jù)INSERTINTO`category`(`cid`,`ame`)VALUES(1,'PHP');INSERTINTO`article`(`id`,`cid`,`name`)VALUES(1,2,'PHP基礎(chǔ)');任務(wù)二:外鍵約束關(guān)聯(lián)表操作上述SQL語(yǔ)句執(zhí)行后,欄目表只有cid為1地欄目記錄;而在插入文章記錄時(shí),由于文章記錄地cid字段地值為2,在欄目表沒有cid為2地記錄,插入就會(huì)失敗。執(zhí)行效果如圖所示?！迦霐?shù)據(jù)任務(wù)二:外鍵約束關(guān)聯(lián)表操作在數(shù)據(jù)庫(kù),對(duì)于建立外鍵約束地?cái)?shù)據(jù)表來說,若要對(duì)主表進(jìn)行更新操作,從表將按照其建立外鍵約束時(shí)設(shè)置地ONUPDATE參數(shù)自動(dòng)執(zhí)行相應(yīng)地操作。例如,當(dāng)參數(shù)設(shè)置為CASCADE時(shí),如果主表發(fā)生更新,則從表也會(huì)對(duì)相應(yīng)地字段進(jìn)行更新。——更新數(shù)據(jù)任務(wù)二:外鍵約束

關(guān)聯(lián)表操作為了驗(yàn)證關(guān)聯(lián)表地更新操作,在文章表為外鍵約束字段cid設(shè)置ONUPDATECASCADE,然后插入數(shù)據(jù)并進(jìn)行修改測(cè)試,具體如下。——更新數(shù)據(jù)INSERTINTO`category`(`cid`,`ame`)VALUES(2,'MySQL');INSERTINTO`article`(`id`,`cid`,`name`)VALUES(2,2,'MySQL基礎(chǔ)');UPDATE`category`SET`cid`=3WHERE`cid`=2;任務(wù)二:外鍵約束關(guān)聯(lián)表操作執(zhí)行上述SQL語(yǔ)句后,使用SELECT語(yǔ)句查看文章表地變化,具體如圖所示。從圖可以看出,當(dāng)主表（欄目表）地cid修改為3后,從表（文章表）地有關(guān)外鍵cid也同時(shí)修改為3。——更新數(shù)據(jù)任務(wù)二:外鍵約束關(guān)聯(lián)表操作在數(shù)據(jù)庫(kù),對(duì)于建立外鍵約束地?cái)?shù)據(jù)表來說,若要對(duì)主表進(jìn)行刪除操作,從表將按照其建立外鍵約束時(shí)設(shè)置地ONDELETE參數(shù)自動(dòng)執(zhí)行相應(yīng)地操作。例如,當(dāng)參數(shù)設(shè)置為RESTRICT時(shí),如果主表進(jìn)行刪除操作,同時(shí)從表地外鍵字段有關(guān)聯(lián)記錄,就會(huì)阻止主表地刪除操作?！?jiǎng)h除數(shù)據(jù)任務(wù)二:外鍵約束

關(guān)聯(lián)表操作為了驗(yàn)證關(guān)聯(lián)表地刪除操作,在文章表為外鍵約束字段cid設(shè)置ONDELETERESTRICT,然后插入數(shù)據(jù)并進(jìn)行刪除測(cè)試,具體如下?！?jiǎng)h除數(shù)據(jù)INSERTINTO`category`(`cid`,`ame`)VALUES(4,'JavaScript');INSERTINTO`article`(`id`,`cid`,`name`)VALUES(3,4,'JS入門');DELETEFROM`category`WHERE`cid`=4;任務(wù)二:外鍵約束關(guān)聯(lián)表操作上述SQL語(yǔ)句地執(zhí)行結(jié)果如圖所示。從圖可以看出,欄目cid為4地記錄刪除失敗?！?jiǎng)h除數(shù)據(jù)任務(wù)二:外鍵約束

關(guān)聯(lián)表操作當(dāng)需要?jiǎng)h除兩個(gè)具有關(guān)聯(lián)關(guān)系表地?cái)?shù)據(jù)時(shí),一定要先刪除從表地?cái)?shù)據(jù),然后再刪除主表地?cái)?shù)據(jù)。因此,使用如下SQL語(yǔ)句才能夠?qū)崿F(xiàn)欄目刪除。——?jiǎng)h除數(shù)據(jù)DELETEFROM`article`WHERE`cid`=4;DELETEFROM`category`WHERE`cid`=4;任務(wù)二:外鍵約束在項(xiàng)目開發(fā),對(duì)于復(fù)雜地?cái)?shù)據(jù)操作過程,往往需要通過一組SQL語(yǔ)句執(zhí)行多項(xiàng)并行業(yè)務(wù)邏輯,這樣就需要保證所有命令執(zhí)行地同步性。針對(duì)這樣地情況,就需要考慮使用MySQL提供地事務(wù)處理。接下來將對(duì)事務(wù)處理地概念與使用等進(jìn)行詳細(xì)講解。任務(wù)三:事務(wù)處理什么是事務(wù)在MySQL,事務(wù)就是針對(duì)數(shù)據(jù)庫(kù)地一組操作。它可以由一條或多條SQL語(yǔ)句組成,且每個(gè)SQL語(yǔ)句是相互依賴地。只要在程序執(zhí)行過程有一條SQL語(yǔ)句執(zhí)行失敗或發(fā)生錯(cuò)誤,則其它語(yǔ)句都不會(huì)執(zhí)行。也就是說事務(wù)地執(zhí)行要么成功,要么就返回到事務(wù)開始前地狀態(tài),這就保證了同一事務(wù)操作地同步性與數(shù)據(jù)地完整性。任務(wù)三:事務(wù)處理什么是事務(wù)"A,C,I,D"4個(gè)基本原則:原子性（Atomicity）:指地是數(shù)據(jù)庫(kù)地每個(gè)事務(wù)都是完整地,不可分割地最小地工作單元,在執(zhí)行事務(wù)時(shí),只有所有操作執(zhí)行成功,整個(gè)事務(wù)才算執(zhí)行成功,否則撤銷之前所有地操作。一致性（Consistency）:指地就是在事務(wù)處理時(shí),無(wú)論執(zhí)行成功還是失敗,都要保證數(shù)據(jù)庫(kù)系統(tǒng)處于一致地狀態(tài),保證數(shù)據(jù)庫(kù)系統(tǒng)從不返回到一個(gè)未處理地事務(wù)。任務(wù)三:事務(wù)處理什么是事務(wù)"A,C,I,D"4個(gè)基本原則:隔離性（Isolation）:指當(dāng)一個(gè)事務(wù)地在執(zhí)行時(shí),不會(huì)受到其它事務(wù)地影響。保證了未完成事務(wù)地所有操作與數(shù)據(jù)庫(kù)系統(tǒng)地隔離,直到事務(wù)完成為止,才能看到事務(wù)地執(zhí)行結(jié)果。持久性（Durability）:是指事務(wù)一旦提交,其對(duì)數(shù)據(jù)庫(kù)地修改就是永久性地。但是對(duì)于一些外部原因造成地?cái)?shù)據(jù)庫(kù)故障,如硬盤損壞,那么事務(wù)地處理結(jié)果可能都會(huì)丟失。任務(wù)三:事務(wù)處理事務(wù)地使用事務(wù)地使用過程一般分為:開啟事務(wù),創(chuàng)建事務(wù)與提交事務(wù)。其,在提交事務(wù)前,若創(chuàng)建事務(wù)時(shí)地某些操作不合理,則可以撤銷事務(wù),讓數(shù)據(jù)庫(kù)系統(tǒng)恢復(fù)到開始事務(wù)前地狀態(tài)。接下來以銀行轉(zhuǎn)賬為例,講解事務(wù)使用各個(gè)步驟如何進(jìn)行具體地操作。任務(wù)三:事務(wù)處理

事務(wù)地使用在itcast數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)表account,用于完成轉(zhuǎn)賬功能:——準(zhǔn)備測(cè)試數(shù)據(jù)CREATETABLE`account`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(32)NOTNULLUNIQUEMENT'賬戶名',`money`DECIMAL(10,2)NOTNULLMENT'存款金額')ENGINE=InnoDBCHARSET=utf8;需要注意地是,在MySQL數(shù)據(jù)庫(kù),要想對(duì)數(shù)據(jù)表進(jìn)行事務(wù)處理,需要將其存儲(chǔ)引擎設(shè)置為InnoDB。任務(wù)三:事務(wù)處理

事務(wù)地使用在MySQL數(shù)據(jù)庫(kù),使用事務(wù)之前需要先開啟事務(wù),具體SQL語(yǔ)句如下?！_啟事務(wù)STARTTRANSACTION;#或BEGIN;執(zhí)行上述語(yǔ)句后,若MySQL沒有返回警告或錯(cuò)誤信息提示,則初始化事務(wù)成功,可以繼續(xù)執(zhí)行以下地操作了。任務(wù)三:事務(wù)處理

事務(wù)地使用創(chuàng)建事務(wù)就是在事務(wù)開啟后,執(zhí)行地一系列SQL語(yǔ)句。例如,開啟事務(wù)處理后,向表account插入兩條測(cè)試數(shù)據(jù),具體如下?！?jiǎng)?chuàng)建事務(wù)STARTTRANSACTION;INSERTINTO`account`VALUES(NULL,'張三',5000),(NULL,'李四',100);任務(wù)三:事務(wù)處理事務(wù)地使用上述SQL語(yǔ)句執(zhí)行后,通過SELECT查詢執(zhí)行結(jié)果,如圖所示?！?jiǎng)?chuàng)建事務(wù)任務(wù)三:事務(wù)處理事務(wù)地使用在用戶沒有提交事務(wù)前,其它連接MySQL服務(wù)器地用戶進(jìn)程是看不到當(dāng)前事務(wù)地處理結(jié)果地。例如,為數(shù)據(jù)表account添加測(cè)試數(shù)據(jù)地SQL語(yǔ)句,再打開一個(gè)MySQL服務(wù)器進(jìn)程,進(jìn)入到itcast數(shù)據(jù)庫(kù)查看數(shù)據(jù)表account地?cái)?shù)據(jù),結(jié)果如圖所示?！峤皇聞?wù)任務(wù)三:事務(wù)處理

事務(wù)地使用接下來對(duì)上述創(chuàng)建地事務(wù)進(jìn)行提交操作,具體SQL語(yǔ)句如下?！峤皇聞?wù)MIT;執(zhí)行上述語(yǔ)句后,在左圖打開地服務(wù)進(jìn)程進(jìn)行SELECT查詢,即可得到與右圖一樣地查詢結(jié)果。任務(wù)三:事務(wù)處理事務(wù)地使用撤銷事務(wù)也稱事務(wù)回滾,用于創(chuàng)建事務(wù)時(shí)執(zhí)行地SQL語(yǔ)句與業(yè)務(wù)邏輯不符或操作錯(cuò)誤時(shí),通過ROLLBACK命令撤銷對(duì)數(shù)據(jù)庫(kù)地所有操作,或利用ROLLBACKTOSAVEPOINT回滾到指定位置。撤銷事務(wù)地操作在實(shí)際應(yīng)用,有著非常重要地作用?！蜂N事務(wù)任務(wù)三:事務(wù)處理

事務(wù)地使用假設(shè)張三轉(zhuǎn)讓李四一輛二手自行車,李四需要向張三支付199元,則執(zhí)行地SQL語(yǔ)句如下?！蜂N事務(wù)STARTTRANSACTION;UPDATE`account`SET`money`=`money`+199WHERE`name`='張三';UPDATE`account`SET`money`=`money`-199WHERE`name`='李四';①撤銷全部事務(wù)處理任務(wù)三:事務(wù)處理事務(wù)地使用執(zhí)行上述SQL語(yǔ)句后,使用SELECT查詢account數(shù)據(jù)表更新后地變化,如圖所示。——撤銷事務(wù)①撤銷全部事務(wù)處理任務(wù)三:事務(wù)處理

事務(wù)地使用從查詢結(jié)果可知,李四地賬戶余額為負(fù)數(shù),顯然不符合正常地業(yè)務(wù)邏輯。此時(shí)就需要使用ROLLBACK命令進(jìn)行全部事務(wù)地回滾,撤銷剛才對(duì)數(shù)據(jù)表account地更新操作。具體SQL語(yǔ)句如下?！蜂N事務(wù)①撤銷全部事務(wù)處理ROLLBACK;執(zhí)行完事務(wù)回滾后再進(jìn)行SELECT查詢,結(jié)果與開始創(chuàng)建事務(wù)查詢結(jié)果相同。任務(wù)三:事務(wù)處理

事務(wù)地使用使用SAVEPOINT可以在事務(wù)處理使用指定不同地撤銷位置,在需要事務(wù)撤銷時(shí)進(jìn)行回滾。例如,向數(shù)據(jù)表account逐條插入3條記錄,并利用SAVEPOINT在每次插入數(shù)據(jù)后設(shè)置一個(gè)回滾位置,SQL語(yǔ)句如下?！蜂N事務(wù)②撤銷事務(wù)到指定位置STARTTRANSACTION;INSERTINTO`account`VALUES(NULL,'王五',500);SAVEPOINTtest1;INSERTINTO`account`VALUES(NULL,'趙六',6000);SAVEPOINTtest2;INSERTINTO`account`VALUES(NULL,'李七',70);SAVEPOINTtest3;任務(wù)三:事務(wù)處理

事務(wù)地使用上述SQL執(zhí)行后,進(jìn)行SELECT查詢,就會(huì)看到account表已經(jīng)存在新插入地這3條記錄。假設(shè)從"李七"開始地記錄有誤需要撤銷,就可以回滾到test2位置,具體SQL如下?！蜂N事務(wù)②撤銷事務(wù)到指定位置ROLLBACKTOSAVEPOINTtest2;執(zhí)行上述SQL語(yǔ)句后,查詢數(shù)據(jù)表account地?cái)?shù)據(jù),就會(huì)看到新插入地記錄只有"王五"與"趙六"。由此可見,事務(wù)可以回滾到想要地位置。任務(wù)三:事務(wù)處理

事務(wù)地使用需要注意地是,SAVEPOINT指定回滾位置地操作對(duì)于已經(jīng)提交地事務(wù)并不適用,且對(duì)于定義了相同名稱地回滾位置,則后面地定義會(huì)覆蓋之前地定義。此外,對(duì)于不再需要使用地回滾位置標(biāo)識(shí),可以通過RELEASESAVEPOINT命令進(jìn)行刪除。例如,刪除回滾位置test1,SQL語(yǔ)句如下?！蜂N事務(wù)②撤銷事務(wù)到指定位置RELEASESAVEPOINTtest1;任務(wù)三:事務(wù)處理

事務(wù)地使用CHAIN與RELEASE子句可以用來分別定義在事務(wù)提交或者回滾之后地操作,CHAIN會(huì)立即啟動(dòng)一個(gè)新事物,并且與剛才地事務(wù)具有相同地隔離級(jí)別;RELEASE則會(huì)斷開與客戶端地連接。例如,向數(shù)據(jù)表account添加一條數(shù)據(jù)后,提交事務(wù)并開啟新事務(wù),具體SQL語(yǔ)句如下?！罄m(xù)操作STARTTRANSACTION;INSERTINTO`account`VALUES(NULL,'鄭七',70);MITANDCHAIN;任務(wù)三:事務(wù)處理

事務(wù)地使用接著繼續(xù)執(zhí)行插入操作,具體SQL語(yǔ)句如下?！罄m(xù)操作INSERTINTO`account`VALUES(NULL,'謝八',8);執(zhí)行上述SQL語(yǔ)句后,再打開一個(gè)MySQL服務(wù)器進(jìn)程進(jìn)行查詢,可以看到account表新增記錄只有"鄭七"沒有"謝八"。任務(wù)三:事務(wù)處理

事務(wù)地使用接下來提交事務(wù)處理并斷開與客戶端地連接,具體SQL語(yǔ)句如下。——后續(xù)操作MITRELEASE;此時(shí),在查看事務(wù)處理結(jié)果,如圖所示。從查詢結(jié)果可以看出,使用RELEASE在事務(wù)提交后,已經(jīng)斷開與客戶端地連接。任務(wù)三:事務(wù)處理事務(wù)地使用事務(wù)存在地周期是由用戶在命令提示符下輸入STARTTRANSACTION或BEGIN指令開始,直到用戶輸入MIT命令結(jié)束。為了便于理解,下面以一個(gè)簡(jiǎn)單事務(wù)存在地周期流程圖為例進(jìn)行展示。——事務(wù)存在地周期任務(wù)三:事務(wù)處理事務(wù)地使用——事務(wù)存在地周期任務(wù)三:事務(wù)處理事務(wù)地使用——事務(wù)存在地周期需要注意地是,在同一個(gè)進(jìn)程,在未結(jié)束前一個(gè)事務(wù)而又重新打開另一個(gè)事務(wù)時(shí),前一個(gè)事務(wù)會(huì)被自動(dòng)提交,也就是說事務(wù)不支持嵌套功能。任務(wù)三:事務(wù)處理

事務(wù)地使用MySQL默認(rèn)操作就是自動(dòng)提交模式。除非顯示地開啟一個(gè)事務(wù)（STARTTRANSACTION）,否則所有地SQL都會(huì)被當(dāng)做單獨(dú)地事務(wù)自動(dòng)提交（MIT）。因此,如果用戶想要控制事務(wù)地自動(dòng)提交方式,可以通過AUTOMIT來實(shí)現(xiàn),具體如下?！聞?wù)地自動(dòng)提交SETAUTOMIT=0;任務(wù)三:事務(wù)處理事務(wù)地使用——事務(wù)地自動(dòng)提交通過上述語(yǔ)句即可關(guān)閉MySQL地自動(dòng)提交功能,實(shí)現(xiàn)了只有用戶手動(dòng)執(zhí)行提交（MIT）操作,MySQL才會(huì)將事務(wù)提交到數(shù)據(jù)庫(kù)系統(tǒng)。否則,若不執(zhí)行手動(dòng)提交,而終止MySQL會(huì)話,數(shù)據(jù)庫(kù)會(huì)自動(dòng)執(zhí)行回滾操作。任務(wù)三:事務(wù)處理事務(wù)地隔離級(jí)由于數(shù)據(jù)庫(kù)允許多線程并發(fā)訪問,因此用戶可以通過不同地線程執(zhí)行不同地事務(wù)。為了保證這些事務(wù)之間,以及數(shù)據(jù)庫(kù)性能都不受影響,對(duì)事務(wù)設(shè)置隔離級(jí)是十分必要地。在MySQL,事務(wù)地隔離級(jí)分為四種,分別為READUNMITTED,READMITTED,REPEATABLEREAD與SERIALIZABLE。下面將對(duì)這四種隔離級(jí)進(jìn)行介紹。任務(wù)三:事務(wù)處理事務(wù)地隔離級(jí)在READUNMITTED隔離級(jí)下,可以讀取到其它事務(wù)未提交地?cái)?shù)據(jù),這種讀取數(shù)據(jù)地方式也也被稱之為"臟讀"（DirtyRead）。這在程序開發(fā)會(huì)帶來很多問題,除非用戶真地知道自己在做什么,并有很好地理由選擇這樣做。否則,為了保證數(shù)據(jù)地一致性,在實(shí)際應(yīng)用READUNMITTED隔離級(jí)幾乎是不被使用地?！猂EADUNMITTED（讀取未提交內(nèi)容）任務(wù)三:事務(wù)處理事務(wù)地隔離級(jí)READMITTED隔離級(jí)是大多數(shù)地?cái)?shù)據(jù)庫(kù)管理系統(tǒng)地默認(rèn)隔離級(jí)（不包括MySQL）。在該隔離級(jí)下只能讀取其它事務(wù)已經(jīng)提交地?cái)?shù)據(jù),很好地避免了"臟讀"數(shù)據(jù)地現(xiàn)象。但在事務(wù)內(nèi),兩次地查詢結(jié)果可能會(huì)出現(xiàn)不一致地情況,原因就是READMITTED隔離級(jí)支持地是"不可重復(fù)讀"（NonrepeatableRead）。"不可重復(fù)讀"指地就是其它事務(wù)或進(jìn)程做地更新操作造成了當(dāng)前事務(wù)內(nèi)前后查詢結(jié)果地不一致。雖然,此級(jí)別在實(shí)際操作是沒有任何問題地,但是對(duì)于銀行統(tǒng)計(jì)報(bào)表等操作時(shí),就不符合操作需求了,因此可以將隔離級(jí)修改為REPEATABLEREAD（可重復(fù)讀）。——READMITTED（讀取提交內(nèi)容）任務(wù)三:事務(wù)處理事務(wù)地隔離級(jí)REPEATABLEREAD是MySQL地默認(rèn)事務(wù)隔離級(jí),它解決了"臟讀"與"不可重復(fù)讀"地問題,確保了同一事務(wù)地多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí),會(huì)看到同樣地結(jié)果。但在理論上,該隔離級(jí)會(huì)出現(xiàn)"幻讀"（PhantomRead）地現(xiàn)象。簡(jiǎn)單來說,幻讀就是當(dāng)用戶讀取某一范圍地?cái)?shù)據(jù)行時(shí),另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行,當(dāng)用戶再讀取該范圍地?cái)?shù)據(jù)行時(shí),會(huì)發(fā)現(xiàn)有新地"幻影"（Phantom）行。不過,MySQL地InnoDB存儲(chǔ)引擎通過多版本并發(fā)控制機(jī)制解決了"幻讀"地問題。——REPEATABLEREAD（可重復(fù)讀）任務(wù)三:事務(wù)處理事務(wù)地隔離級(jí)SERIALIZABLE是最高級(jí)別地隔離級(jí),它會(huì)對(duì)事務(wù)進(jìn)行強(qiáng)制排序,使之不會(huì)發(fā)生沖突,從而解決了"臟讀","不可重復(fù)讀"與"幻讀"地問題。但是由于SERIALIZABLE隔離級(jí)默認(rèn)會(huì)為每個(gè)SELECT語(yǔ)句都加鎖,自然也會(huì)出現(xiàn)"鎖競(jìng)爭(zhēng)"（LockContention）,死鎖或超時(shí)等現(xiàn)象,因此,SERIALIZABLE隔離級(jí)也是性能最差地一種隔離級(jí)。簡(jiǎn)言之,SERIALIZABLE是在每個(gè)讀地?cái)?shù)據(jù)行上加鎖。在這個(gè)級(jí)別,可能導(dǎo)致大量地超時(shí)（Timeout）現(xiàn)象與鎖競(jìng)爭(zhēng)（LockContention）現(xiàn)象。因此,除非為了數(shù)據(jù)地穩(wěn)定性,需要強(qiáng)制減少并發(fā)地情況時(shí),才會(huì)選擇此種隔離級(jí)。——SERIALIZABLE（可串行化）任務(wù)三:事務(wù)處理

事務(wù)地隔離級(jí)對(duì)于隔離級(jí)地查看,MySQL提供了以下幾種不同地方式,具體使用哪種方式查詢還需根據(jù)實(shí)際需求進(jìn)行選擇,具體如下?！榭锤綦x級(jí)①查看全局隔離級(jí)SELECT@@global.tx_isolation;②查看當(dāng)前進(jìn)程隔離級(jí)SELECT@@session.tx_isolation;③查看下一個(gè)事務(wù)地隔離級(jí)SE