本文原始碼：GitHub·點這里 || GitEE·點這里
一、系統封裝函式
MySQL 有很多內置的函式，可以快速解決開發中的一些業務需求，大概包括流程控制函式，數值型函式、字串型函式、日期時間函式、聚合函式等，以下列出了這些分類中常用的函式，
1、控制流程函式
- case...when
根據值判斷回傳值，類比編程中的IF-ELSE判斷，
-- DEMO 01
SELECT CASE DATE_FORMAT(NOW(),'%Y-%m-%d')
WHEN '2019-12-29' THEN 'today'
WHEN '2019-12-28' THEN 'yesterday'
WHEN '2019-12-30' THEN 'tommor'
ELSE 'Unknow' END;
-- DEMO 02
SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result;
- if(expr1,expr2,expr3)
如果運算式 expr1 是TRUE，則 IF()的回傳值為expr2; 否則回傳值則為 expr3，
SELECT IF(1>2,'1>2','1<2') AS result ;
SELECT IF(1<2,'yes ','no') AS result ;
SELECT IF(STRCMP('test','test'),'no','yes');
- ifnull(expr1,expr2)
如果運算式 expr1不為NULL，則回傳值為expr1;否則回傳值為 expr2，
SELECT IFNULL(NULL,'cicada');
SELECT IFNULL(1/1,'no');
2、常用字串函式
- CHAR_LENGTH()
回傳值為字串的長度 ，
SELECT CHAR_LENGTH(' c i c ') ;-- 包含空格
SELECT LENGTH(' S q l ') ;
- CONCAT(str1...)
拼接串聯字串，
SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- 包含Null 則回傳Null
SELECT CONCAT("%", "Java", "%"); -- mybatis中拼接模糊查詢
- ELT(N,str1,str2,...)
若N = 1，則回傳值為 str1 ，若N = 2，則回傳值為 str2 ，以此類推，可以用來轉換回傳頁面的狀態，
SELECT ELT(1,'提交','審核中','規則通過') ;
SELECT ELT(2,'提交','審核中','規則通過') ;
- FORMAT(X,D)
格式化數字型別，
SELECT FORMAT(3.1455,2) ; -- 四舍五入保留兩位
SELECT TRUNCATE(3.1455,2) ; -- 直接截取兩位
- TRIM(str)
清空字串空格，
SELECT LTRIM(' hel l o ') ;-- 清空左邊
SELECT RTRIM(' hel l o ') ;-- 清空右邊
SELECT TRIM(' hel l o ') ; -- 清空兩邊
SELECT REPLACE('M y S Q L',' ','') ; -- 替換掉全部空格
3、數值函式
- FLOOR(X)
回傳不大于X的最大整數值 ，
SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2
- MOD(N,M)
模操作，回傳N 被 M除后的余數，
SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2
- RAND() RAND(N)
回傳一個隨機浮點值，范圍在0到1之間，若已指定一個整數引數 N ，則它被用作種子值，用來產生重復序列，
SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE
4、時間日期函式
- ADDDATE(date,INTERVAL expr type)
給指定日期，以指定型別進行運算，
SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01
- CURDATE()
將當前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值回傳，具體格式根據函式用在字串或是數字語境中而定，
SELECT CURDATE(); -- '2019-12-29' 字串
SELECT CURDATE() + 0; -- 20180725 數字
- DATE(expr)
提取日期或時間日期運算式expr中的日期部分，
SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231
- DATE_FORMAT(date,format)
根據format 字串進行 date 值的格式化，
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2019年12月29日
5、聚合函式
AVG([distinct] expr) 求平均值
COUNT({*|[distinct] } expr) 統計行的數量
MAX([distinct] expr) 求最大值
MIN([distinct] expr) 求最小值
SUM([distinct] expr) 求累加和
二、自定義函式
1、概念簡介
函式存盤著一系列sql陳述句，呼叫函式就是一次性執行這些陳述句，所以函式可以降低陳述句重復，函式注重回傳值，而觸發器注重執行程序，所以一些陳述句無法執行，所以函式并不是單純的sql陳述句集合，
2、使用方式
create function 函式名([引數串列]) returns 資料型別
begin
sql陳述句;
return 值;
end;
引數串列的格式是： 變數名 資料型別，
- 無參案例
CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;
- 有參函式
表結構
CREATE TABLE t01_user (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵ID',
user_name varchar(20) DEFAULT NULL COMMENT '用戶名稱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用戶表';
函式用法
create function get_name(p_id INT) returns VARCHAR(20)
begin
declare userName varchar(20);
select user_name from t01_user where id=p_id into userName;
return userName;
end;
SELECT get_name(1) ;
3、函式查看
show create function get_name ;
4、洗掉函式
drop function get_name ;
5、函式注意事項
函式是事先經過編譯，才能在服務器環境呼叫，所以MySQL集群環境需要同步編譯；MySQL是多執行緒環境，所以要保證函式也是執行緒安全 ，
三、觸發器
1、觸發器簡介
觸發器是特殊的存盤程序，不同的是存盤程序要用CALL來呼叫，而觸發器不需要使用CALL，也不需要手工啟動，只要當一個預定義的事件發生的時候，就會被MYSQL自動觸發呼叫，
2、創建觸發器
觸發器語法
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
- trigger_name：觸發器命名 ;
- trigger_time: 觸發動作的時間 ;
- trigger_event: 激活觸發器的陳述句型別 ;
- tbl_name: 觸發器作用的表明，非臨時表 ;
- trigger_stmt：觸發程式執行的陳述句 ;
表資料同步
當向用戶表 t01_user 寫入資料時，同時向 t02_back 表寫入一份備份資料，
-- 用戶備份表
CREATE TABLE t02_back (
id int(11) NOT NULL PRIMARY KEY COMMENT '主鍵ID',
user_name varchar(20) DEFAULT NULL COMMENT '用戶名稱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用戶備份';
-- 觸發器程式
DROP TRIGGER IF EXISTS user_back_trigger ;
CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
BEGIN
INSERT INTO t02_back (id,user_name)
VALUES (new.id,new.user_name);
END ;
-- 測驗案例
INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ;
SELECT * FROM t02_back ;
3、查看觸發器
查看觸發器是指資料庫中已存在的觸發器的定義、狀態、語法資訊等，可以在TRIGGERS表中查看觸發器資訊，
SELECT * FROM `information_schema`.`TRIGGERS`
WHERE `TRIGGER_NAME`='user_back_trigger';
4、洗掉觸發器
DROP TRIGGER陳述句可以洗掉MYSQL中已經定義的觸發器，洗掉觸發器的基本語法，
DROP TRIGGER [schema_name.]trigger_name
5、觸發器注意事項
- 觸發事件
對于相同的表，相同的事件只能創建一個觸發器，比如對表t01_user創建兩次AFTER INSERT觸發器，就會報錯，
- 執行效率
觸發器可以減少應用端和資料庫的通信次數和業務邏輯，但是基于行觸發的邏輯，如果資料集非常大，效率會降低，
- 事務問題
觸發器執行和原表的執行陳述句是否在同一個事務中，取決于觸發表的存盤引擎是否支持事務，
四、源代碼地址
GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/89043.html
標籤：MySQL
上一篇：資料庫中間件分片演算法之hash