圖解MySQL數據庫的存儲過程及存儲引擎

我們經常會對數據表進行插入、刪除、更新及查找的工作，即我們常説的CURD。其實，當我們輸入命令時，MySQL引擎會按照下圖進行操作。如果我們省略了分析和編譯的環節，那麼執行效率將大大提高。
本篇將會和大家介紹MySQL數據庫的存儲過程及存儲引擎的相關知識，希望對大家的工作和學習有所幫助！

存儲過程

（01）存儲過程是SQL語句和控制語句的預編譯集合，以一個名稱存儲並作為一個單元處理。存儲過程存儲在數據庫內，可以由應用程序調用執行，允許用户聲明明變量以及進行流程控制。存儲過程可以接收參數（輸入類型參數、輸出類型參數），可以存在多個返回值。所以，存儲過程的執行效率高於單一SQL命令的執行效率。

（02）1、增強SQL語句的功能和靈活性。2、實現較快的執行速度。客户端第一次調用存儲過程時，MySQL引擎會對其進行語法分析、編譯等操作，然後將編譯結果存儲到內存中，所以第一次和之前的效率一樣，然而以後會直接調用內存中的編譯結果，效率提高。3、減少網絡流量。單條SQL語句字符量較大，而通過調用存儲過程則只需要傳存儲過程的名稱及相關參數即可，提交給服務器的數據量相對較少。

（03）CREATE[DEFINER = { user | CURRENT_USER }]  PROCEDURE sp_name([proc_parameter[,...]])  [characteristic ...] routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeIN表示該參數的值必須在調用存儲過程時指定，不能返回。OUT表示該參數的值可以被存儲過程改變，並且可以返回。INOUT表示該參數在調用時指定，並且可以被改變和返回。

（04）COMMENT 'string'|{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY {DEFINER | INVOKER}COMMENT:註釋CONTAINS SQL:包含SQL語句，但不包含讀或寫數據的語句。NO SQL:不包含SQL語句。READS SQL DATA:包含讀數據的語句。MODIFIES SQL DATA:包含寫數據的語句。SQL SECURITY {DEFINER | INVOKER}:指明誰有權限來執行。

（05）1.過程體由合法的SQL語句構成；2.過程體可以是“任意”SQL語句（這裏的任意主要是指對記錄的增刪改查，多表連接）；3.過程體如果為複合結構，則使用語句；4.複合結構可以包含聲明，循環，控制結構。

（06）CREATE PROCEDURE sp1() SELECT VERSION();

（07）方式一：CALL sp_name([parameter[,...]]) 如果存儲過程包含參數，則必須有小括號。方式二：CALL sp_name[()] 如果存儲過程不包含參數，則小括號可有可無。

（08）ALTER PROCEDURE sp_name [characteristic ...]COMMENT 'string'|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }只能修改存儲過程中的註釋、當前內容的類型，並不能修改過程體。要修改過程體的話，需要先刪除存儲過程，然後重建。

（09）DROP PROCEDURE [IF EXISTS] sp_name

（10）DELIMITER //CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;END//DELIMITER;

（11）下面來調用存儲過程，如下圖所示：

（12）DELIMITER //CREATE PROCEDURE removeAndReturnUsersNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;SELECT count(id) FROM users INTO userNums;END//DELIMITER ;

（13）1.用户變量：以"@"開始，形式為"@變量名"用户變量跟mysql客户端是綁定的，設置的變量，只對當前用户使用的客户端生效SET @i = 7;2.全局變量：定義時，以如下兩種形式出現，set GLOBAL 變量名或者set @@global.變量名對所有客户端生效。只有具有super權限才可以設置全局變量3.會話變量：只對連接的客户端有效4.局部變量：作用範圍在begin到end語句塊之間。在該語句塊裏設置的變量declare語句專門用於定義局部變量。set語句是設置不同類型的變量，包括會話變量和全局變量下面來調用存儲過程CALL removeAndReturnUsersNums(1,@nums);

（14）DELIMITER //CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SAMLLINT UNSIGNED)BEGINDELETE FROM users WHERE age = p_age;SELECT ROW_COUNT() INTO deleteUsers;SELECT COUNT(id) FROM users INTO userCounts;END//DELIMITER ;[注意]ROW_COUNT()函數用來得到插入、刪除以及更新的被影響的記錄總數。

（15）下面來調用存儲過程CALL removeUserByAgeAndReturnInfos(20,@a,@b);[注意]@a表示刪除的記錄數，@b表示剩餘的記錄數SELECT @a,@b;

（16）1.存儲過程實現的功能要複雜一些；而函數的針對性更強。2.存儲過程可以返回多個值；函數只能有一個返回值。3.存儲過程一般獨立的來執行；而函數主要作為其他SQL語句的組成部分來出現。

存儲引擎

（01）MySQL可以將數據以不同的技術存儲在文件（內存）中，這種技術就稱為存儲引擎。每一種存儲引擎使用不同的存儲機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能在關係型數據庫中，數據的存儲是以表的形式來實現的。所以，存儲引擎也可以稱為表類型。所以，實際上，存儲引擎就是一種存儲數據、查詢數據的技術MySQL支持的存儲引擎包括MyISAM、InnoDB、Memory、CSV、Archive。

（02）併發控制是指當多個連接對記錄進行修改時保證數據的一致性和完整性。例如：兩個用户同時登錄並操作數據庫，其中一個用户刪除某條記錄，而另一個用户讀取該條記錄，這就需要併發控制，否則會報錯或返回無效信息。在處理併發'讀'或'寫'操作時，MySQL通過鎖系統實現併發控制，包括共享鎖和排他鎖。-共享鎖(讀鎖)：在同一時間段內，多個用户可以讀取同一個資源，讀取過程中數據不會發生任何變化。-排他鎖(寫鎖)：在任何時候只能有一個用户寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。鎖顆粒(也稱為鎖力度)是指鎖定時的單位。只需要對修改的數據精確加鎖就可以，而無需對所有資源都加鎖。加鎖會增加系統開銷，所以需要通過鎖策略，在鎖開銷和系統安全之間尋找平衡。mysql鎖策略包括表鎖和行鎖兩種策略。- 表鎖，是一種開銷最小的鎖策略。- 行鎖，是一種開銷最大的鎖策略。

（03）事務是數據庫區別於文件系統的重要特徵之一，事務主要用於保證數據庫的完整性。事務特性包括：原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持久性(Durability)，簡寫為ACID。

（04）索引是對數據表中一列或多列的值進行排序的一種結構，使用索引可以快速訪問數據表的特定信息。索引是記錄快速定位的一種方法，類似於書的目錄。索引包括普通索引、唯一索引、全文索引、btree之索引、hash索引等。

（05）除了上面這幾種存儲引擎之外，還有下面幾種不太常見的引擎。CSV存儲引擎不支持索引，逗號分隔值(Comma-Separated Values，CSV，有時也稱為字符分隔值，因為分隔字符也可以不是逗號)，其文件以純文本形式存儲表格數據（數字和文本）。純文本意味着該文件是一個字符序列，不含必須像二進制數字那樣被解讀的數據。CSV文件由任意數目的記錄組成，記錄間以某種換行符分隔。BlackHole也叫黑洞引擎，寫入的數據都會消失，一般用於做數據複製的中繼。MyISAM引擎適合於事物處理不多的情況。

（06）1、通過修改MySQL的配置文件實現default-storage-engine = engine

（07）2、通過創建數據表命令實現CREATE TABLE table_name(  ... ) ENGINE = engine;

（08）3、通過修改數據表命令實現ALTER TABLE table_name ENGINE [=] engine_name;