1. Mysql內建函式分類及使用範圍
- 數學函式： 這類函式只要用於處理數字。這類函式包括絕對值函式、正弦函式、餘弦函式、獲取隨機數函式等。
- 字串函式：這類函式主要用於處理字串。其中包括字串連線函式、字串比較函式、將字串的字母變成小寫或大寫字母的函式、獲取子串的函式等。
- 日期和時間函式：這類函式主要用於處理日期和時間。其中包括取當前時間的函式、獲取當前日期的函式、返回年份的函式、返回日期的函式等。
- 流程函式：這類函式主要用於在SQL語句中控制條件選擇。其中包括IF語句、CASE語句、WHEN語句等。
- 系統資訊函式：這類函式主要用於獲取mysql資料庫的系統資訊。其中包括獲取資料庫名的函式、獲取當前使用者的函式、獲取資料庫版本的函式等。
- 加密函式：這類函式主要用於對字串進行加密解密。其中包括字串加密函式、字串解密函式等。
- 其他函式：包括格式化函式、鎖函式等。
2. 常用函式列舉
1. 數學函式
ABS(x) 返回x的絕對值
CEIL(x),CEILING(x) 返回大於或等於x的最小整數（向上取整）
FLOOR(x) 返回小於或等於x的最大整數（向下取整）
RAND() 返回0~1的隨機數
RAND(x) 返回0~1的隨機數，x值相同時返回的隨機數相同
SIGN(x) 返回x的符號，x是負數、0、正數分別返回-1、0、1
PI() 返回圓周率
TRUNCATE(x,y) 返回數值x保留到小數點後y位的值
ROUND(x) 返回離x最近的整數（四捨五入）
ROUND(x,y) 保留x小數點後y位的值，但截斷時要四捨五入
POW(x,y),POWER(x,y) 返回x的y次方
SQRT(x) 返回x的平方根
EXP(x) 返回e的x次方
MOD(x,y) 返回x 除以y以後的餘數
LOG(x) 返回自然對數（以e為底的對數）
LOG10(x) 返回以10為底的對數
RADIANS(x) 講角度轉換為弧度
DEGREES(x) 講弧度轉換為角度
SIN(x) 求正弦值
ASIN(x) 求反正弦值
COS(x) 求餘弦值
ACOS(x) 求反餘弦值
TAN(x) 求正切值
ATAN(x),ATAN(x,y) 求反正切值
COT(x) 求餘切值
2.字串函式
CHAR_LENGTH(s) 返回字串s的字元數
LENGTH(s) 返回字串s的長度
CONCAT(s1,s2,.....) 將字串s1,s2等多個字串合併為一個字串
CONCAT_WS(x,s1,s2,....) 同COUCAT(s1,s2,.....)，但是每個字串之間要加上x
INSERT(s1,x,len,s2) 將字串s2替換s1的x位置開始長度為len的字串
UPPER(s),UCASE(s) 講字串s的所有字元都變成大寫字母
LOWER(s),LCASE(s) 講字串s的所有字元都變成小寫字母
LEFT(s,n) 返回字串s的前n個字元
RIGHT(s,n) 返回字串s的後n個字元
LPAD(s1,len,s2) 字串s2來填充s1的開始處，使字串長度達到len
RPAD(s1,len,s2) 字串s2來填充s1的結尾處，使字串長度達到len
LTRIM(s) 去掉字串s開始處的空格
RTRIM(s) 去掉字串s結尾處的空格
TRIM(s) 去掉字串s開始處和結尾處的空格
TRIM(s1 FROM s) 去掉字串s中開始處和結尾處的字串s1
REPEAT(s,n) 將字串s重複n次
SPACE(n) 返回n個空格
REPLACE(s,s1,s2) 用字串s2代替字串s中的字串s1
STRCMP(s1,s2) 比較字串s1和s2
SUBSTRING(s,n,len) 獲取從字串s中的第n個位置開始長度為len的字串
MID(s,n,len) 同SUBSTRING(s,n,len)ATE(s1,s),POSTTION(s1 IN s)從字串s中獲取s1的開始位置
INSTR(s,s1) 從字串s中獲取s1的開始位置
REVERSE(s) 將字串s的順序反過來
ELT(n,s1,s2...) 返回第n個字串
FIELD(s,s1,s2...) 返回第一個與字串s匹配的字串的位置
FIND_IN_SET(s1,s2) 返回在字串s2中與s1匹配的字串的位置
MAKE_SET(x,s1,s2...) 按x的二進位制數從s1,s2......sn中選取字串
3.日期和時間函式
CURDATE(),CURRENT_DATE() 返回當前日期
CURTIME(),CURRENT_TIME() 返回當前時間
NOW(),CURRENT_TIMESTAMP()LOCALTIME(),SYSDATE()LOCALTIMESTAMP() 返回當前日期和時間
UNIX_TIMESTAMP() 以UNIX時間戳的形式返回當前時間
UNIX_TIMESTAMP(d) 將時間d以UNIX時間戳的形式返回
FROM_UNIXTIME(d) 把UNIX時間戳的時間轉換為普通格式的時間
UTC_DATE() 返回UTC(國際協調時間)日期
UTC_TIME() 返回UTC時間
MONTH(d) 返回日期d中的月份值，範圍是1~12
MONTHNAME(d) 返回日期d中的月份名稱，如january
DAYNAME(d) 返回日期d是星期幾，如Monday
DAYOFWEEK(d) 返回日期d是星期幾，1表示星期日，2表示星期2
WEEKDAY(d) 返回日期d是星期幾，0表示星期一,1表示星期2
WEEK(d) 計算日期d是本年的第幾個星期，範圍是0-53
WEEKOFYEAR(d) 計算日期d是本年的第幾個星期，範圍是1-53
DAYOFYEAR(d) 計算日期d是本年的第幾天
DAYOFMONTH(d) 計算日期d是本月的第幾天
YEAR(d) 返回日期d中的年份值
QUARTER(d) 返回日期d是第幾季度，範圍1-4
HOUR(t) 返回時間t中的小時值
MINUTE(t) 返回時間t中的分鐘值
SECOND(t) 返回時間t中的秒鐘值
EXTRACT(type FROM d) 從日期d中獲取指定的值，type指定返回的值，如YEAR,HOUR等
TIME_TO_SEC(t) 將時間t轉換為秒
SEC_TO_TIME(s) 將以秒為單位的時間s轉換為時分秒的格式
TO_DAYS(d) 計算日期d到0000年1月1日的天數
FROM_DAYS(n) 計算從0000年1月1日開始n天后的日期
DATEDIFF(d1,d2) 計算日期d1到d2之間相隔的天數
TIMESTAMPDIFF(type,d1,d2) 計算日期d1到d2之間的時間差，type可指定YEAR、MONTH、DAY、HOUR、MINUTE或SECOND。
ADDDATE(d,n) 計算開始日期d加上n天的日期
ADDDATE(d, INTERVAL expr type) 計算起始日期d加上一個時間段後的日期
SUBDATE(d,n) 計算起始日期d減去n天的日期
SUBDATE(d, INTERVAL expr type) 計算起始日期d減去一個時間段後的日期
ADDTIME(t,n) 計算起始時間t加上n秒的時間
SUBTIME(t,n) 計算起始時間t減去n秒的時間
DATE_FORMAT(d,f) 按照表示式f的要求顯示日期d
TIME_FORMAT(t,f) 按照表示式f的要求顯示時間t
GET_FORMAT(type,s) 根據字串s獲取type型別資料的顯示格式
4.條件判斷函式
條件判斷函式用來在SQL語句中進行條件判斷。更加是否滿足判斷條件，SQL語句執行不同的分支。
IF(expr,v1,v2)函式
IF(expr,v1,v2)函式中，如果表示式expr成立，返回結果v1,否則，返回結果v2。
CASE函式
CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2...][ELSE vn] END
示例
case status when 1 then '狀態為1'
when 2 then '狀態為2'
when 3 then '狀態為3'
else '其他' end
等同寫法：
case when status=1 then '狀態為1'
when status=2 then '狀態為2'
when status=3 then '狀態為3'
else '其他' end
ifnull(val1,val2) 當val1為null時返回val2，否則返回val1
nullif(val1,val2) 當val1等於val2時返回null，否則返回val1
5.系統資訊函式
VERSION() 返回資料庫的版本號
CONNECTION_ID() 返回伺服器的連線數，也就是到現在為止mysql服務的連線次數
DATABASE(),SCHEMA() 返回當前資料庫名
USER() 返回當前使用者的名稱
CHARSET(str) 返回字串str的字符集
COLLATION(str) 返回字串str的字元排列方式
LAST_INSERT_ID() 返回最後生成的auto_increment值
6.加密解密函式
PASSWORD(str) 對字串str進行加密
MD5(str) 對字串str進行加密
ENCODE(str,pswd_str) 使用字串pswd_str來加密字串str,加密結果是一個二進位制數，必須使用BLOB型別來保持它
DECODE(crypt_str,pswd_str) 解密函式，使用字串pswd_str來為crypt_str解密
7.其他函式
FORMAT(x,n) 格式化函式，可以講數字x進行格式化，將x保留到小數點後n位，這個過程需要進行四捨五入。
ASCII(s) 返回字串s的第一個字元的ASSCII碼
BIN(x) 返回x的二進位制編碼
HEX(x) 返回x的十六進位制編碼
OCT(x) 返回x的八進位制編碼
CONV(x,f1,f2) 將x從f1進位制數變成f2進位制數
INET_ATON(IP) 將IP地址轉換為數字表示，IP值需要加上引號
INET_NTOA(n) 可以將數字n轉換成IP的形式
GET_LOCT(name,time) 加鎖函式，定義一個名稱為name、持續時間長度為time秒的鎖，如果鎖定成功，返回1，如果嘗試超時，返回0，如果遇到錯誤，返回NULL.
RELEASE_LOCK(name) 解除名稱為name的鎖，如果解鎖成功，返回1，如果嘗試超時，返回0，如果解鎖失敗，返回NULL。
IS_FREE_LOCK(name) 判斷是否使用名為name的鎖，如果使用，返回0，否則返回1.
CONVERT(s USING cs) 將字串s的字符集變成cs
CAST(x AS type),CONVERT(x,type) 這兩個函式將x變成type型別，這兩個函式只對BINARY,CHAR,DATE,DATETIME,TIME,SIGNED INTEGER,UNSIGNED INTEGER這些型別起作用，但這兩種方法只是改變了輸出值得資料型別，並沒有改變表中欄位的型別。
3. mysql5.7新增json相關函式
JSON_ARRAY(d1,d2...) 建立JSON陣列
JSON_ARRAY_APPEND(json_arr, path, value) 往json陣列內之後追加元素
JSON_ARRAY_INSERT(json_arr, path, value) 往json陣列內之前新增元素
JSON_CONTAINS() JSON是否在路徑中包含特定物件
JSON_CONTAINS_PATH() JSON是否在路徑處包含任何資料
JSON_DEPTH() JSON的最大深度
JSON_EXTRACT() 從JSON獲取資料
JSON_INSERT() 如果資料不存在就將資料插入JSON
JSON_KEYS() JSON檔案中的鍵陣列（類似map.keys()）
JSON_LENGTH() JSON檔案中的元素數量
JSON_MERGE_PATCH() 合併JSON，替換重複鍵的值(後面替換前面)
JSON_MERGE_PRESERVE() 合併JSON，保留重複的鍵
JSON_OBJECT() 建立JSON
JSON_PRETTY() 格式化列印JSON
JSON_QUOTE() 引用JSON(將字元轉換成帶""，能轉換轉譯字元)
JSON_REMOVE() 從JSON檔案中刪除資料
JSON_REPLACE() 替換JSON檔案中的值
JSON_SEARCH() 查詢JSON檔案中值的路徑
JSON_SET() 將資料插入JSON檔案（存在即替換值）
JSON_STORAGE_SIZE() JSON二進位制所佔位元組數
JSON_TYPE() JSON值的型別
JSON_UNQUOTE() 引用JSON(去除字元的""，能轉換轉譯字元)
JSON_VALID() JSON值是否有效
1.建立一個json
select JSON_OBJECT('name','張三','no','001')
#結果：{"no": "001", "name": "張三"}
2.建立一個json陣列
select JSON_ARRAY(JSON_OBJECT('name','張三','no','001')
,JSON_OBJECT('name','李四','no','002'))
#結果：[{"no": "001", "name": "張三"}, {"no": "002", "name": "李四"}]
3.獲取json中的某個值
select JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAY(JSON_OBJECT('name','張三','no','001')
,JSON_OBJECT('name','李四','no','002')), '$[0].name'))
#結果：張三
4.往json陣列追加元素
select JSON_ARRAY_APPEND(JSON_ARRAY(JSON_OBJECT('name','張三','no','001')
,JSON_OBJECT('name','李四','no','002')),'$',JSON_OBJECT('name','王五','no','003'))
#結果：[{"no": "001", "name": "張三"}, {"no": "002", "name": "李四"}
, {"no": "003", "name": "王五"}]
#注意，此函式是往元素內增加
select JSON_ARRAY_INSERT(JSON_ARRAY(JSON_OBJECT('name','張三','no','001')
,JSON_OBJECT('name','李四','no','002')),'$[1]',JSON_OBJECT('name','王五','no','003'))
#結果：[{"no": "001", "name": "張三"}, {"no": "003", "name": "王五"},
{"no": "002", "name": "李四"}]
5.修改json中的某個值
select JSON_REPLACE(JSON_ARRAY(JSON_OBJECT('name','張三','no','001')
,JSON_OBJECT('name','李四','no','002')),'$[1].name','修改後')
#結果：[{"no": "001", "name": "張三"}, {"no": "002", "name": "修改後"}]
6.獲取條件下的指定下標資料
註釋：此方法只能查尋第一條匹配資料
此條件為字串時：
select order_status_info,
JSON_EXTRACT(order_status_info, '$[*].statusDesc'),
JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(order_status_info, '$[*].statusDesc'),'one','進件完成')) as t,
JSON_EXTRACT(order_status_info,
JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(order_status_info, '$[*].statusDesc'),'one','進件完成')))
from loan_activate_order_extend where id=5678456467878979001
此條件不為字串時
select order_status_info,
JSON_EXTRACT(order_status_info, '$[*].status'),
REPLACE(REPLACE(REPLACE(JSON_EXTRACT(order_status_info, '$[*].status'),',','","'),'[','["'),']','"]'),
JSON_UNQUOTE(JSON_SEARCH(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(order_status_info, '$[*].status'),',','","'),'[','["'),']','"]'),'one','41')) as t,
JSON_EXTRACT(order_status_info,
JSON_UNQUOTE(JSON_SEARCH(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(order_status_info, '$[*].status'),',','","'),'[','["'),']','"]'),'one','41')))
from loan_activate_order_extend where id=5678456467878979001
4. 參考檔案
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
5. 後言
在使用mysql函式時應當注意查詢效率，sql是否合理，當需求的sql過於複雜的時候應當思考是否可以在表內做欄位沉餘、建中間表等解決方式。
資料格式是否與預期一致，如在使用with recursive注意連表條件是否會造成死迴圈。