MySQL進(jìn)階分區(qū)與觸發(fā)器

主講教師：魏巍巍北京信息職業(yè)技術(shù)學(xué)院數(shù)據(jù)庫技術(shù)與應(yīng)用MySQL進(jìn)階-分區(qū)與觸發(fā)器創(chuàng)建采集任務(wù)01分區(qū)01目錄CONTENTS觸發(fā)器01分區(qū)01分區(qū)技術(shù)：操作數(shù)據(jù)表時(shí)根據(jù)給定的算法，將數(shù)據(jù)在邏輯上分到多個(gè)區(qū)域中存儲(chǔ)，在分區(qū)中還可以設(shè)置子分區(qū)，將數(shù)據(jù)存放到更加具體的區(qū)域內(nèi)。分區(qū)分區(qū)技術(shù)：將一張數(shù)據(jù)表中的數(shù)據(jù)存儲(chǔ)在不同物理磁盤中，比單個(gè)磁盤存儲(chǔ)更多的數(shù)據(jù)，如果WHERE子句的條件命中一個(gè)分區(qū)，掃描相關(guān)的一個(gè)分區(qū)而不用全表掃描，提高查詢效率。提示：如果查詢id為3~5的數(shù)據(jù)，掃描磁盤1即可。分區(qū)分區(qū)技術(shù)對(duì)存儲(chǔ)引擎以及鎖的要求：分區(qū)技術(shù)不適用于MERGE、CSV或FEDERATED存儲(chǔ)引擎。InnoDB分區(qū)表不能設(shè)置外鍵，同樣地，與外鍵相關(guān)的主表和從表也不能被分區(qū)。MySQL5.7中InnoDB表不支持子分區(qū)在多個(gè)磁盤中存儲(chǔ)，目前僅MyISAM存儲(chǔ)引擎支持。同一個(gè)分區(qū)表的所有分區(qū)必須使用相同存儲(chǔ)引擎。當(dāng)建表時(shí)未指定存儲(chǔ)引擎，在創(chuàng)建分區(qū)時(shí)就必須設(shè)置存儲(chǔ)引擎。MySQL5.6及更早的版本中，對(duì)分區(qū)的MyISAM表進(jìn)行操作時(shí)會(huì)鎖定所有的分區(qū)，直到操作完成后才會(huì)釋放鎖。而在MySQL5.7中，僅會(huì)鎖定與操作相關(guān)的分區(qū)，不會(huì)影響其他分區(qū)。分區(qū)mysql>CREATETABLE`mydb`.`p_list`(->`id`INTAUTO_INCREMENTCOMMENT'ID編號(hào)',->`name`VARCHAR(50)COMMENT'姓名',->`dpt`INTCOMMENT'部門編號(hào)',->KEY(`id`)->)ENGINE=INNODB->PARTITIONBYLIST(`dpt`)(->PARTITIONp1VALUESIN(1,3),->PARTITIONp2VALUESIN(2,4)

->);案例：創(chuàng)建分區(qū)代碼示例將dpt字段進(jìn)行分區(qū)：值為1或3時(shí)，將記錄放在p1分區(qū)值為2或4時(shí)，將記錄放在p2分區(qū)分區(qū)#在MySQL的data/mydb目錄下查看分區(qū)數(shù)據(jù)文件p_list#p#p1.ibdp_list#p#p2.ibd案例：創(chuàng)建分區(qū)代碼示例p_list是建立分區(qū)的數(shù)據(jù)表名，p1和p2表示分區(qū)名稱。分區(qū)觸發(fā)器02觸發(fā)器：可以看作是一種特殊類型的存儲(chǔ)過程，它與存儲(chǔ)過程的區(qū)別在于存儲(chǔ)過程使用時(shí)需要調(diào)用，而觸發(fā)器是在預(yù)先定義好的事件（如INSERT、DELETE等操作）發(fā)生時(shí)，才會(huì)被MySQL自動(dòng)調(diào)用。觸發(fā)器的優(yōu)點(diǎn)：觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)無痕更改操作。保證數(shù)據(jù)安全，進(jìn)行安全校驗(yàn)。觸發(fā)器的缺點(diǎn)：觸發(fā)器的使用會(huì)影響數(shù)據(jù)庫的結(jié)構(gòu)，同時(shí)增加了維護(hù)的復(fù)雜程度。觸發(fā)器的無痕操作會(huì)造成數(shù)據(jù)在程序（如PHP、Java等）層面不可控。觸發(fā)器CREATETABLE`my_shopcart`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENTCOMMENT'購物車id',`user_id`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'用戶id',`goods_id`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'商品id',`goods_price`DECIMAL(10,2)UNSIGNEDNOTNULLDEFAULT0COMMENT'單價(jià)',`goods_num`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'購買件數(shù)',`is_select`TINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'是否選中',`create_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時(shí)間',`update_time`DATETIMEDEFAULTNULLCOMMENT'更新時(shí)間')ENGINE=InnoDBDEFAULTCHARSET=utf8;案例：觸發(fā)器的使用代碼示例STEP01創(chuàng)建my_shopcart購物車表。以my_goods和my_shopcart為例，演示添加商品到購物車后自動(dòng)減少商品庫存。觸發(fā)器mysql>DELIMITER$$mysql>CREATETRIGGERinsert_triBEFOREINSERT->ONmy_shopcartFOREACHROW->BEGIN->DECLAREstocksINTDEFAULT0;->SELECTstockINTOstocksFROMmy_goodsWHEREid=new.goods_id;->IFstocks<=new.goods_numTHEN->SETnew.goods_num:=stocks;->UPDATEmy_goodsSETstock=0WHEREid=new.goods_id;->ELSE->UPDATEmy_goodsSETstock=stocks-new.goods_numWHERE->id=new.goods_id;->ENDIF;->END;->$$QueryOK,0rowsaffected(0.01sec)mysql>DELIMITER;案例：觸發(fā)器的使用代碼示例STEP02為my_shopcart購物車表創(chuàng)建觸發(fā)器。觸發(fā)器mysql>SELECT`id`,`stock`FROM`my_goods`WHERE`id`=1;+----+-------+|id|stock|+----+-------+|1|500|+----+-------+案例：觸發(fā)器的使用代碼示例STEP03觸發(fā)insert_tri觸發(fā)器。查看my_goods表中商品編號(hào)為1的庫存量stock。觸發(fā)器mysq1>INSERTINTO`my_shopcart`(`user_id`,`goods_id`,`goods_num`,->`goods_price`)VALUES(3,1,1000,0.50);案例：觸發(fā)器的使用代碼示例STEP04觸發(fā)insert_tri觸發(fā)器。向購物車表my_shopcart中插入數(shù)據(jù)。觸發(fā)器mysql>SELECT`id`,`stock`FROM`my_goods`WHERE`id`=1;+----+-------+|id|stock|+----+-------+|1|0|+----+-------+mysql>SELECT`id`,`user_id`,`goods_id`,`goods_num`,`goods_price`FROM`my_shopcart`;+----+---------+----------+------------+--------------+|id|user_id|goods_id|goods_num|goods_price|+----+---------+----------+------------+--------------+|1|3|1|500|0.50|+----+---------+----------+------------+--------------+案例：觸發(fā)器的使用代碼示例STEP05觸發(fā)insert_tri觸發(fā)器。查看my_goods和my_shopcart表在觸