前面我們主要分享了MySQL中的常見知識與使用。這里我們主要分享一下MySQL中的高階使用，主要包括：函數、存儲過程和存儲引擎。

對于MySQL中的基礎知識，可以參見

《與 MySQL 的零距離接觸》

1. 函數

函數可以返回任意類型的值，也可以接收這些類型的參數。

字符函數

函數名稱	描述
CONCAT()	字符連接
CONCAT_WS()	使用指定的分隔符進行字符連接
FORMAT()	數字格式化
LOWER()	轉換成小寫字母
UPPER()	轉換成大寫字母
LEFT()	獲取左側字符
RIGHT()	獲取右側字符
LENGTH()	獲取字符串長度
LTRIM()	刪除前導空格
RTRIM()	刪除后續空格
TRIM()	刪除前導和后續空格
SUBSTRING()	字符串截取
[NOT] LIKE	模式匹配
REPLACE()	字符串替換

函數可以嵌套使用。

% (百分號)：代表任意個字符。

_ (下劃線)：代表任意一個字符。

# 刪除前導'?'符號 
SELECT TRIM(LEADING '?' FROM '??MySQL???'); 
# 刪除后續'?'符號 
SELECT TRIM(TRAILING '?' FROM '??MySQL???'); 
# 刪除前后'?'符號 
SELECT TRIM(BOTH '?' FROM '??My??SQL???'); 
# 將'?'符號替換成'!'符號 
SELECT REPLACE('??My??SQL???', '?', '!'); 
# 從中'MySQL'第1個開始，截取2個字符 
SELECT SUBSTRING('MySQL', 1, 2); 
# 從中'MySQL'截取最后1個字符 
SELECT SUBSTRING('MySQL', -1); 
# 從中'MySQL'第2個開始，截取至結尾 
SELECT SUBSTRING('MySQL', 2);  

數值運算符函數

函數名稱	描述
CEIL()	進一取整
DIV	整數除法
FLOOR()	舍一取整
MOD	取余數（取模）
POWER()	冪運算
ROUND()	四舍五入
TRUNCATE()	數字截取

比較運算符函數

函數名稱	描述
[NOT]BETWEEN…AND..	[不]在范圍之內
[NOT]IN()	[不]在列出值范圍內
IS[NOT]NULL	[不]為空

日期時間函數

函數名稱	描述
NOW()	當前日期和時間
CURDATE()	當前日期
CURTIME()	當前時間
DATE_ADD()	日期變化
DATEDIFF()	日期差值
DATE_FORMAT()	日期格式化

  

# 時間增加1年 
SELECT DATE_ADD('2016-05-28', INTERVAL 365 DAY); 
# 時間減少1年 
SELECT DATE_ADD('2016-05-28', INTERVAL -365 DAY); 
# 時間增加3周 
SELECT DATE_ADD('2016-05-28', INTERVAL 3 WEEK); 
# 日期格式化 
SELECT DATE_FORMAT('2016-05-28', '%m/%d/%Y'); 
# 更多時間格式可以前往MySQL官網查看手冊  

信息函數

函數名稱	描述
CONNECTION_ID()	連接ID
DATEBASE()	當前數據庫
LAST_INSERT_ID()	最后插入記錄的ID號
USER()	當前用戶
VERSION()	版本信息

聚合函數

函數名稱	描述
AVG()	平均值
COUNT()	計數
MAX()	最大值
MIN()	最小值
SUM()	求和

加密函數

函數名稱	描述
MD5()	信息摘要算法
PASSWORD()	密碼算法

自定義函數

用戶自定義函數(user-defined function，UDF)是一種對MySQL擴展的途徑，其用法與內置函數相同。UDF是對MySQL擴展的一種途徑。

必要條件

• 參數：可以有零個或多個
• 返回值：只能有一個

參數和返回值沒有必然的聯系。

創建自定義函數

CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body 

函數體(routine_body)

• 函數體由合法的SQL語句構成;
• 函數體可以是簡單的SELECT或INSERT語句;
• 函數體如果為復合結構則使用BEGIN…END語句;
• 復合結構可以包含聲明，循環，控制結構。

示例

# 不帶參數 
CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); 
 
# 帶參數 
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2) / 2; 
 
# 具有復合結構函數體 
# 可能需要使用DELIMITER命令修改分隔符 
CREATE FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED  
BEGIN  
INSERT test(username) VALUES(username); 
RETURN LAST_INSERT_ID(); 
END  

2. 存儲過程  

 

存儲過程是SQL語句和控制語句的預編譯集合，以一個名稱存儲作為一個單元處理。可以由用戶調用執行，允許用戶聲明變量以及進行流程控制。存儲過程可以接收輸入類型的參數，也可以接收輸出類型的參數，并可以存在多個返回值。執行效率比單一的SQL語句高。

優點

• 增強SQL語句的功能和靈活性

在存儲過程中可以寫控制語句具有很強的靈活性，可以完成復雜的判斷及較復雜的運算。

• 實現較快的執行速度

如果某一操作包含了大量的SQL語句，那么這些SQL語句都將被MySQL引擎執行語法分析、編譯、執行，所以效率相對過低。而存儲過程是預編譯的，當客戶端第一次調用存儲過程時，MySQL的引擎將對它進行語法分析、編譯等操作，然后把這個編譯的結果存儲到內存中，所以說第一次使用的時候效率和以前是相同的。但是以后客戶端再次調用這個存儲過程時，直接從內存中執行，所以說效率比較高，速度比較快。

• 減少網絡流量

如果通過客戶端每一個單獨發送SQL語句讓服務器來執行，那么通過http協議來提交的數據量相對來說較大。

創建

CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[, ...]]) [characteristic ...] routine_body 

proc_parameter :

[IN | OUT | INOUT] param_name type

參數：

IN ，表示該參數的值必須在調用存儲過程時指定。

OUT ，表示該參數值可以被存儲過程改變，并且可以返回。

INOUT ，表示該參數的調用時指定，并且可以被改變和返回。

特性：

COMMENT 注釋

CONTAINS SQL 包含SQL語句，但不包含讀或寫數據的語句。

NO SQL 不包含SQL語句。

READS SQL DATA 包含讀寫數據的語句。

MODIFIES SQL DATA 包含寫數據的語句。

SQL SECURITY {DEFINER | INVOKER} 指明誰有權限來執行。

過程體

• 過程體由合法的SQL語句構成;
• 過程體可以是任意SQL語句;

不能通過存儲過程來創建數據表、數據庫。可以通過存儲過程對數據進行增、刪、改、查和多表連接操作。

• 過程體如果為復合結構則使用BEGIN…END語句;
• 復合結構中可以包含聲明、循環、控制結構。

調用

CALL sp_name ([parameter[, ...]]) 
 
CALL sp_name[()]  

刪除

DROP PROCEDURE [IF EXISTS] sp_name 

修改

ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string' 
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
| SQL SECURITY {DEFINER | INVOKER}  

存儲過程與自定義函數的區別

• 存儲過程實現的功能要復雜一些，而函數的針對性更強。
• 存儲過程可以返回多個值，函數只能有一個返回值。
• 存儲過程一般獨立執行，函數可以作為其他SQL語句的組成部分來實現。

示例：

# 創建不帶參數的存儲過程 
CREATE PROCEDURE sp1() SELECT VERSION(); 
 
# 創建帶有IN類型參數的存儲過程(users為數據表名) 
# 參數的名字不能和數據表中的記錄名字一樣 
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE id = p_id; 
END 
 
# 創建帶有IN和OUT類型參數的存儲過程(users為數據表名) 
CREATE PROCEDURE removeUserAndReturnUserNumsById(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE id = p_id; 
SELECT COUNT(id) FROM users INTO userNums; 
END 
 
# 創建帶有多個OUT類型參數的存儲過程(users為數據表名) 
CREATE PROCEDURE removeUserAndReturnInfosByAge(IN p_age SMALLINT UNSIGNED, OUT delUser SMALLINT UNSIGNED,  OUT userNums SMALLINT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE age = p_age; 
SELECT ROW_COUNT INTO delUser; 
SELECT COUNT(id) FROM users INTO userNums; 
END  

3. 存儲引擎

MySQL可以將數據以不同的技術存儲在文件(內存)中，這種技術就稱為存儲引擎。

每一種存儲引擎使用不同的存儲機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。

• 鎖

共享鎖(讀鎖)：在同一時間段內，多個用戶可以讀取同一個資源，讀取過程中數據不會發生任何變化。

排他鎖(寫鎖)：在任何時候只能有一個用戶寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。

• 鎖顆粒

表鎖：是一種開銷最小的鎖策略。

行鎖：是一種開銷最大的鎖策略。

• 并發控制

當多個連接記錄進行修改時保證數據的一致性和完整性。

• 事務

事務用于保證數據庫的完整性。

舉例：用戶銀行轉賬

用戶A 轉賬200元 用戶B

實現步驟：

1)從當前賬戶減掉200元(賬戶余額大于等于200元)。

2)在對方賬戶增加200元。

事務特性：

1)原子性(atomicity)

2)一致性(consistency)

3)隔離性(isolation)

4)持久性(durability)

• 外鍵

是保證數據一致性的策略。

• 索引

是對數據表中一列或多列的值進行排序的一種結構。

類型

MySQL主要支持以下幾種引擎類型：

• MyISAM
• InnoDB
• Memory
• CSV
• Archive

特點	MyISAM	InnoDB	Memory	Archive
存儲限制	256TB	64TB	有	無
事務安全	–	支持	–	–
支持索引	支持	支持	支持
鎖顆粒	表鎖	行鎖	表鎖	行鎖
數據壓縮	支持	–	–	支持
支持外鍵	–	支持	–	–

CSV:實際上是由逗號分隔的數據引擎，在數據庫子目錄為每一個表創建一個 .csv 的文件，這是一種普通的文本文件，每一個數據行占用一個文本行。不支持索引。

BlackHole：黑洞引擎，寫入的數據都會消失，一般用于做數據復制的中繼。

MyISAM：適用于事務的處理不多的情況。

InnoDB：適用于事務處理比較多，需要有外鍵支持的情況。

索引分類：普通索引、唯一索引、全文索引、btree索引、hash索引…

修改存儲引擎

• 通過修改MySQL配置文件

default-storage-engine=engine_name

• 通過創建數據表命令實現

CREATE TABLE table_name(...)ENGINE=engine_name

• 通過修改數據表命令實現

ALTER TABLE table_name ENGINE[=]engine_name

4. 管理工具

• phpMyAdmin

需要有PHP環境

• Navicat
• MySQL Workbench