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

對于MySQL中的基礎(chǔ)知識，可以參見

《與 MySQL 的零距離接觸》

1 函數(shù)

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

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

函數(shù)可以嵌套使用。

% （百分號）：代表任意個字符。

_ （下劃線）：代表任意一個字符。

# 刪除前導(dǎo)’?’符號SELECT TRIM(LEADING ’?’ FROM ’??MySQL???’);# 刪除后續(xù)’?’符號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個開始，截取至結(jié)尾SELECT SUBSTRING(’MySQL’, 2); 數(shù)值運算符函數(shù) 函數(shù)名稱 描述CEIL() 進一取整DIV 整數(shù)除法FLOOR() 舍一取整MOD 取余數(shù)（取模）POWER() 冪運算ROUND() 四舍五入TRUNCATE() 數(shù)字截取 比較運算符函數(shù) 函數(shù)名稱 描述[NOT]BETWEEN…AND.. [不]在范圍之內(nèi)[NOT]IN() [不]在列出值范圍內(nèi)IS[NOT]NULL [不]為空 日期時間函數(shù) 函數(shù)名稱 描述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官網(wǎng)查看手冊 信息函數(shù) 函數(shù)名稱 描述CONNECTION_ID() 連接IDDATEBASE() 當前數(shù)據(jù)庫LAST_INSERT_ID() 最后插入記錄的ID號USER() 當前用戶VERSION() 版本信息 聚合函數(shù) 函數(shù)名稱 描述AVG() 平均值COUNT() 計數(shù)MAX() 最大值MIN() 最小值SUM() 求和 加密函數(shù) 函數(shù)名稱 描述MD5() 信息摘要算法PASSWORD() 密碼算法 自定義函數(shù)

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

必要條件 參數(shù)：可以有零個或多個 返回值：只能有一個

參數(shù)和返回值沒有必然的聯(lián)系。

創(chuàng)建自定義函數(shù)

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

函數(shù)體（routine_body）

函數(shù)體由合法的SQL語句構(gòu)成； 函數(shù)體可以是簡單的SELECT或INSERT語句； 函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語句； 復(fù)合結(jié)構(gòu)可以包含聲明，循環(huán)，控制結(jié)構(gòu)。

示例

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

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

優(yōu)點 增強SQL語句的功能和靈活性

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

實現(xiàn)較快的執(zhí)行速度

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

減少網(wǎng)絡(luò)流量

如果通過客戶端每一個單獨發(fā)送SQL語句讓服務(wù)器來執(zhí)行，那么通過http協(xié)議來提交的數(shù)據(jù)量相對來說較大。

創(chuàng)建

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

proc_parameter :

[IN | OUT | INOUT] param_name type

參數(shù)：

IN ，表示該參數(shù)的值必須在調(diào)用存儲過程時指定。

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

INOUT ，表示該參數(shù)的調(diào)用時指定，并且可以被改變和返回。

特性：

COMMENT 注釋

CONTAINS SQL 包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。

NO SQL 不包含SQL語句。

READS SQL DATA 包含讀寫數(shù)據(jù)的語句。

MODIFIES SQL DATA 包含寫數(shù)據(jù)的語句。

SQL SECURITY {DEFINER | INVOKER} 指明誰有權(quán)限來執(zhí)行。

過程體 過程體由合法的SQL語句構(gòu)成； 過程體可以是任意SQL語句； 不能通過存儲過程來創(chuàng)建數(shù)據(jù)表、數(shù)據(jù)庫。可以通過存儲過程對數(shù)據(jù)進行增、刪、改、查和多表連接操作。 過程體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語句； 復(fù)合結(jié)構(gòu)中可以包含聲明、循環(huán)、控制結(jié)構(gòu)。 調(diào)用

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} 存儲過程與自定義函數(shù)的區(qū)別 存儲過程實現(xiàn)的功能要復(fù)雜一些，而函數(shù)的針對性更強。 存儲過程可以返回多個值，函數(shù)只能有一個返回值。 存儲過程一般獨立執(zhí)行，函數(shù)可以作為其他SQL語句的組成部分來實現(xiàn)。

示例：

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

MySQL可以將數(shù)據(jù)以不同的技術(shù)存儲在文件（內(nèi)存）中，這種技術(shù)就稱為存儲引擎。

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

鎖

共享鎖（讀鎖）：在同一時間段內(nèi)，多個用戶可以讀取同一個資源，讀取過程中數(shù)據(jù)不會發(fā)生任何變化。

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

鎖顆粒

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

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

并發(fā)控制

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

事務(wù)

事務(wù)用于保證數(shù)據(jù)庫的完整性。

舉例：用戶銀行轉(zhuǎn)賬

用戶A 轉(zhuǎn)賬200元 用戶B

實現(xiàn)步驟：

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

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

事務(wù)特性：

1）原子性（atomicity）

2）一致性（consistency）

3）隔離性（isolation）

4）持久性（durability）

外鍵

是保證數(shù)據(jù)一致性的策略。

索引

是對數(shù)據(jù)表中一列或多列的值進行排序的一種結(jié)構(gòu)。

類型

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

MyISAM InnoDB Memory CSV Archive

各類存儲引擎特點

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

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

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

MyISAM：適用于事務(wù)的處理不多的情況。

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

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

修改存儲引擎 通過修改MySQL配置文件 default-storage-engine=engine_name 通過創(chuàng)建數(shù)據(jù)表命令實現(xiàn) CREATE TABLE table_name(...)ENGINE=engine_name 通過修改數(shù)據(jù)表命令實現(xiàn) ALTER TABLE table_name ENGINE[=]engine_name 4 管理工具

phpMyAdmin

需要有PHP環(huán)境

Navicat

MySQL Workbench

來自：http://chars.tech/2017/05/29/mysql-advanced-study/