MySQL 常用函數
經常編寫程式的朋友一定體會得到函數的重要性，豐富的函數往往能使使用者的工作事半功倍。函數能幫助使用者做很多事情，比如說字元串的處理、數值的運算、日期的運算等，在這方面MySQL 提供了多種内建函數幫助開發人員編寫簡單快捷的SQL 語句，其中常用的函數有字元串函數、日期函數和數值函數。
在MySQL 資料庫中，函數可以用在SELECT 語句及其子句（例如where、order by、having 等）中，也可以用在UPDATE、DELETE 語句及其子句中。本章将配合一些執行個體對這些常用函數進行詳細的介紹。
1、字元串函數
字元串函數是最常用的一種函數了，如果大家編寫過程式的話，不妨回過頭去看看自己使用過的函數，可能會驚訝地發現字元串處理的相關函數占已使用過的函數很大一部分。MySQL中字元串函數也是最豐富的一類函數：
| 函數 | 功能 |
| CANCAT(S1,S2,…Sn) | 連接配接S1,S2,…Sn 為一個字元串 |
| INSERT(str,x,y,instr) | 将字元串str 從第x 位置開始，y 個字元長的子串替換為字元串instr |
| LOWER(str) | 将字元串str 中所有字元變為小寫 |
| UPPER(str) | 将字元串str 中所有字元變為大寫 |
| LEFT(str ,x) | 傳回字元串str 最左邊的x 個字元 |
| RIGHT(str,x) | 傳回字元串str 最右邊的x 個字元 |
| LPAD(str,n ,pad) | 用字元串pad 對str 最左邊進行填充，直到長度為n 個字元長度 |
| RPAD(str,n ,pad) | 用字元串pad 對str 最右邊進行填充，直到長度為n 個字元長度 |
| LTRIM(str) | 去掉字元串str 左側的空格 |
| RTRIM(str) | 去掉字元串str 行尾的空格 |
| REPEAT(str,x) | 傳回str 重複x 次的結果 |
| REPLACE(str,a,b) | 用字元串b 替換字元串str 中所有出現的字元串a |
| STRCMP(s1,s2) | 比較字元串s1 和s2 |
| TRIM(str) | 去掉字元串行尾和行頭的空格 |
| SUBSTRING(str,x,y) | 傳回從字元串str x 位置起y 個字元長度的字串 |
下面通過具體的執行個體來逐個地研究每個函數的用法，需要注意的是這裡的例子僅僅在于說明各個函數的使用方法，是以函數都是單個出現的，但是在一個具體的應用中通常可能需要綜合幾個甚至幾類函數才能實作相應的應用。
-
CANCAT(S1,S2,…Sn)函數：把傳入的參數連接配接成為一個字元串。
下面的例把“aaa”、“bbb”、“ccc”3 個字元串連接配接成了一個字元串“aaabbbccc”。另外，任何字元串與NULL 進行連接配接的結果都将是NULL。
mysql> select concat('aaa','bbb','ccc'),concat('aaa',null); +---------------------------+--------------------+ | concat('aaa','bbb','ccc') | concat('aaa',null) | +---------------------------+--------------------+ | aaabbbccc | NULL | +---------------------------+--------------------+ 1 row in set (0.00 sec) -
INSERT(str ,x,y,instr)函數：将字元串str 從第x 位置開始，y 個字元長的子串替換為字元串instr。
下面的例子把字元串“beijing2008you”中的從第12 個字元開始以後的3 個字元替換成“me”。
mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me |
+------------------------------------+
1 row in set (0.00 sec)
-
LOWER(str)和UPPER(str)函數：把字元串轉換成小寫或大寫。
在字元串比較中，通常要将比較的字元串全部轉換為大寫或者小寫，如下例所示：
mysql> select lower('BEIJING2008'),upper('beijing2008'); +----------------------+----------------------+ | lower('BEIJING2008') | upper('beijing2008') | +----------------------+----------------------+ | beijing2008 | BEIJING2008 | +----------------------+----------------------+ 1 row in set (0.00 sec) -
LEFT(str,x)和RIGHT(str,x)函數：分别傳回字元串最左邊的x 個字元和最右邊的x 個字元。如果第二個參數是NULL，那麼将不傳回任何字元串。
下例中顯示了對字元串“beijing2008”應用函數後的結果。
mysql> select LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);
+-----------------------+----------------------+------------------------+
| LEFT('beijing2008',7) | LEFT('beijing',null) | RIGHT('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing | NULL | 2008 |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)
-
LPAD(str,n ,pad)和RPAD(str,n ,pad)函數：用字元串pad 對str 最左邊和最右邊進行填充,直到長度為n 個字元長度。
下例中顯示了對字元串“2008”和“beijing”分别填充後的結果。
mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)
-
LTRIM(str)和RTRIM(str)函數：去掉字元串str 左側和右側空格。
下例中顯示了字元串“beijing”加空格進行過濾後的結果。
mysql> select ltrim(' |beijing'), rtrim('beijing| '); +-----------------------+-----------------------+ | ltrim(' |beijing') | rtrim('beijing| ') | +-----------------------+-----------------------+ | |beijing | beijing| | +-----------------------+-----------------------+ 1 row in set (0.00 sec) -
REPEAT(str,x)函數：傳回str 重複x 次的結果。
下例中對字元串“mysql”重複顯示了3 次。
mysql> select repeat('mysql',3); +-------------------+ | repeat('mysql',3) | +-------------------+ | mysqlmysqlmysql | +-------------------+ 1 row in set (0.00 sec) -
REPLACE(str,a,b)函數：用字元串b 替換字元串str 中所有出現的字元串a。
下例中用字元串“2008”代替了字元串“beijing_2010”中的“_2010”。
mysql> select replace('beijing_2010','_2010','2008'); +----------------------------------------+ | replace('beijing_2010','_2010','2008') | +----------------------------------------+ | beijing2008 | +----------------------------------------+ 1 row in set (0.00 sec) - STRCMP(s1,s2)函數：比較字元串s1 和s2 的ASCII 碼值的大小。如果s1 比s2 小，那麼傳回-1；如果s1 與s2 相等，那麼傳回0；如果s1 比s2 大，那麼傳回1。如下例：
mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
-
TRIM(str)函數：去掉目标字元串的開頭和結尾的空格。
下例中對字元串“ $ beijing2008 $ ”進行了前後空格的過濾。
mysql> select trim(' $ beijing2008 $ '); +-------------------------------+ | trim(' $ beijing2008 $ ') | +-------------------------------+ | $ beijing2008 $ | +-------------------------------+ 1 row in set (0.00 sec) -
SUBSTRING(str,x,y)函數：傳回從字元串str 中的第x 位置起y 個字元長度的字串。
此函數經常用來對給定字元串進行字串的提取，如下例所示。
mysql> select substring('beijing2008',8,4), substring('beijing2008',1,7); +------------------------------+------------------------------+ | substring('beijing2008',8,4) | substring('beijing2008',1,7) | +------------------------------+------------------------------+ | 2008 | beijing | +------------------------------+------------------------------+ 1 row in set (0.00 sec)
2、數值函數
MySQL 中另外一類很重要的函數就是數值函數，這些函數能處理很多數值方面的運算。可以想象，如果沒有這些函數的支援，使用者在編寫有關數值運算方面的代碼時将會困難重重，舉個例子，如果沒有ABS 函數的話，如果要取一個數值的絕對值，就需要進行好多次判斷才能傳回這個值，而數字函數能夠大大提高使用者的工作效率。
| 函數 | 功能 |
| ABS(x) | 傳回x 的絕對值 |
| CEIL(x) | 傳回大于x 的最大整數值 |
| FLOOR(x) | 傳回小于x 的最大整數值 |
| MOD(x，y) | 傳回x/y 的模 |
| RAND() | 傳回0 到1 内的随機值 |
| ROUND(x,y) | 傳回參數x 的四舍五入的有y 位小數的值 |
| TRUNCATE(x,y) | 傳回數字x 截斷為y 位小數的結果 |
下面将結合執行個體對這些函數進行介紹。
-
ABS(x)函數：傳回x 的絕對值。
下例中顯示了對正數和負數分别取絕對值之後的結果。
mysql> select ABS(-0.8),ABS(0.8); +-----------+----------+ | ABS(-0.8) | ABS(0.8) | +-----------+----------+ | 0.8 | 0.8 | +-----------+----------+ 1 row in set (0.00 sec) -
CEIL(x)函數：傳回大于x 的最大整數。
下例中顯示了對0.8 和－0.8 分别CEIL 後的結果。
mysql> select ceil(-0.8),ceil(0.8); +------------+-----------+ | ceil(-0.8) | ceil(0.8) | +------------+-----------+ | 0 | 1 | +------------+-----------+ 1 row in set (0.00 sec) -
FLOOR(x)函數：傳回小于x 的最大整數，和CEIL 的用法剛好相反。
下例中顯示了對0.8 和－0.8 分别FLOOR 後的結果。
mysql> select floor(-0.8),floor(0.8); +-------------+------------+ | floor(-0.8) | floor(0.8) | +-------------+------------+ | -1 | 0 | +-------------+------------+ 1 row in set (0.00 sec) -
MOD(x，y)函數：傳回x/y 的模。
和x%y 的結果相同，模數和被模數任何一個為NULL 結果都為NULL。如下例所示：
mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10); +------------+-----------+--------------+ | MOD(15,10) | MOD(1,11) | MOD(NULL,10) | +------------+-----------+--------------+ | 5 | 1 | NULL | +------------+-----------+--------------+ 1 row in set (0.00 sec) -
RAND()函數：傳回0 到1 内的随機值。
每次執行結果都不一樣，如下例所示：
mysql> select RAND(),RAND(); +--------------------+-----------------------+ | RAND() | RAND() | +--------------------+-----------------------+ | 0.7870859902231824 | 0.0031945509865829265 | +--------------------+-----------------------+ 1 row in set (0.00 sec)利用此函數可以取任意指定範圍内的随機數，比如需要産生0～100 内的任意随機整數，可
以操作如下：
mysql> select ceil(100*rand()), ceil(100*rand()); +------------------+------------------+ | ceil(100*rand()) | ceil(100*rand()) | +------------------+------------------+ | 66 | 27 | +------------------+------------------+ 1 row in set (0.00 sec) -
ROUND(x,y)函數：傳回參數x 的四舍五入的有y 位小數的值。
如果是整數，将會保留y 位數量的0（8.0.16 隻保留整數）；如果不寫y，則預設y 為0，即将x 四舍五入後取整。适合于将所有數字保留同樣小數位的情況。如下例所示。
mysql> select ROUND(1.1), ROUND(1.1,2),ROUND(1,2); +------------+--------------+------------+ | ROUND(1.1) | ROUND(1.1,2) | ROUND(1,2) | +------------+--------------+------------+ | 1 | 1.10 | 1 | +------------+--------------+------------+ 1 row in set (0.00 sec) -
TRUNCATE(x,y)函數：傳回數字x 截斷為y 位小數的結果。
注意TRUNCATE 和ROUND 的差別在于TRUNCATE 僅僅是截斷，而不進行四舍五入。下例中描述了二者的差別：
mysql> select round(1.235,2), truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
3、日期和時間函數
有時我們可能會遇到這樣的需求：目前時間是多少、下個月的今天是星期幾、統計截止到目前日期前3 天的收入總和等。這些需求就需要日期和時間函數來實作
| 函數 | 功能 |
| CURDATE() | 傳回目前日期 |
| CURTIME() | 傳回目前時間 |
| NOW() | 傳回目前的日期和時間 |
| UNIX_TIMESTAMP(date) | 傳回日期date 的UNIX 時間戳 |
| FROM_UNIXTIME | 傳回UNIX 時間戳的日期值 |
| WEEK(date) | 傳回日期date 為一年中的第幾周 |
| YEAR(date) | 傳回日期date 的年份 |
| HOUR(time) | 傳回time 的小時值 |
| MINUTE(time) | 傳回time 的分鐘值 |
| MONTHNAME(date) | 傳回date 的月份名 |
| DATE_FORMAT(date,fmt) | 傳回按字元串fmt 格式化日期date 值 |
| DATE_ADD(date,INTERVAL expr type) | 傳回一個日期或時間值加上一個時間間隔的時間值 |
| DATEDIFF(expr,expr2) | 傳回起始時間expr 和結束時間expr2 之間的天數 |
下面結合一些執行個體來逐個講解每個函數的使用方法。
- CURDATE()函數：傳回目前日期，隻包含年月日。
mysql> select CURDATE(); +------------+ | CURDATE() | +------------+ | 2020-08-14 | +------------+ 1 row in set (0.00 sec) - CURTIME()函數：傳回目前時間，隻包含時分秒。
mysql> select CURTIME(); +-----------+ | CURTIME() | +-----------+ | 17:16:04 | +-----------+ 1 row in set (0.00 sec) - NOW()函數：傳回目前的日期和時間，年月日時分秒全都包含。
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-08-14 17:16:26 | +---------------------+ 1 row in set (0.00 sec) - UNIX_TIMESTAMP(date)函數：傳回日期date 的UNIX 時間戳。
mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1597396659 | +-----------------------+ 1 row in set (0.00 sec) - FROM_UNIXTIME （unixtime ）函數：傳回UNIXTIME 時間戳的日期值，和UNIX_TIMESTAMP(date)互為逆操作
mysql> select from_unixtime(1597396659);
+---------------------------+
| from_unixtime(1597396659) |
+---------------------------+
| 2020-08-14 17:17:39 |
+---------------------------+
1 row in set (0.00 sec)
- WEEK(DATE)和YEAR(DATE)函數：前者傳回所給的日期是一年中的第幾周，後者傳回所給的日期是哪一年。
mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
| 32 | 2020 |
+-------------+-------------+
1 row in set (0.00 sec)
- HOUR(time)和MINUTE(time)函數：前者傳回所給時間的小時，後者傳回所給時間的分鐘。
mysql> select HOUR(curtime()), MINUTE(curtime()); +-----------------+-------------------+ | HOUR(curtime()) | MINUTE(curtime()) | +-----------------+-------------------+ | 17 | 20 | +-----------------+-------------------+ 1 row in set (0.00 sec) - MONTHNAME(date)函數：傳回date 的英文月份名稱。
mysql> select MONTHNAME(now()); +------------------+ | MONTHNAME(now()) | +------------------+ | August | +------------------+ 1 row in set (0.00 sec) - DATE_FORMAT(date,fmt)函數：按字元串fmt 格式化日期date 值，此函數能夠按指定的格式顯示日期，可以用到的格式符如表 下面的例子将目前時間顯示為“月，日，年”格式：
格式符 格式說明 %S,%s 兩位數字形式的秒（00,01,...,59） %i 兩位數字形式的分（00,01,...,59） %H 兩位數字形式的小時，24 小時（00,01,...,23） %h,%I 兩位數字形式的小時，12 小時（01,02,...,12） %k 數字形式的小時，24 小時（0,1,...,23） %l 數字形式的小時，12 小時（1,2,...,12） %T 24 小時的時間形式（hh:mm:ss） %r 12 小時的時間形式（hh:mm:ssAM 或hh:mm:ssPM） %p AM 或PM %W 一周中每一天的名稱（Sunday,Monday,...,Saturday） %a 一周中每一天名稱的縮寫（Sun,Mon,...,Sat） %d 兩位數字表示月中的天數（00,01,...,31） %e 數字形式表示月中的天數（1,2，...,31） %D 英文字尾表示月中的天數（1st,2nd,3rd,...） %w 以數字形式表示周中的天數（0=Sunday,1=Monday,...,6=Saturday） %j 以3 位數字表示年中的天數（001,002,...,366） %U 周（0,1,52），其中Sunday 為周中的第一天 %u 周（0,1,52），其中Monday 為周中的第一天 %M 月名（January,February,...,December） %b 縮寫的月名（January,February,...,December） %m 兩位數字表示的月份（01,02,...,12） %c 數字表示的月份（1,2,...,12） %Y 4 位數字表示的年份 %y 兩位數字表示的年份 %% 直接值“%” mysql> select DATE_FORMAT(NOW(), '%M,%D,%Y'); +--------------------------------+ | DATE_FORMAT(NOW(), '%M,%D,%Y') | +--------------------------------+ | August,14th,2020 | +--------------------------------+ 1 row in set (0.00 sec) - DATE_ADD(date,INTERVAL expr type)函數：傳回與所給日期date 相差INTERVAL 時間段的日期。其中INTERVAL 是間隔類型關鍵字，expr 是一個表達式，這個表達式對應後面的類型，type是間隔類型，MySQL 提供了13 種間隔類型:
| 表達式類型 | 描述 | 格式 |
| HOUR | 小時 | hh |
| MINUTE | 分 | mm |
| SECOND | 秒 | ss |
| YEAR | 年 | YY |
| MONTH | 月 | MM |
| DAY | 日 | DD |
| YEAR_MONTH | 年和月 | YY_MM |
| DAY_HOUR | 日和小時 | DD hh |
| DAY_MINUTE | 日和分鐘 | DD hh:mm |
| DAY_SECOND | 日和秒 | DD hh:mm:ss |
| HOUR_MINUTE | 小時和分 | hh:ss |
| HOUR_SECOND | 小時和秒 | hh:ss |
| MINUTE_SECOND | 分鐘和秒 | mm:ss |
來看一個具體的例子，在這個例子中第1 列傳回了目前日期時間，第2 列傳回距離目前日期31 天後的日期時間，第3 列傳回距離目前日期一年兩個月後的日期時間。
mysql> select now() current, date_add(now(),INTERVAL 31 day) after31days, date_add(now(), interval '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2020-08-14 17:46:38 | 2020-09-14 17:46:38 | 2021-10-14 17:46:38 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
同樣也可以用負數讓它傳回之前的某個日期時間，如下第1 列傳回了目前日期時間，第2列傳回距離目前日期31 天前的日期時間，第3 列傳回距離目前日期一年兩個月前的日期時間。
mysql> select now() current, date_add(now(),INTERVAL -31 day) before31days, date_add(now(), interval '-1_-2' year_month) before_oneyear_twomonth;
+---------------------+---------------------+-------------------------+
| current | before31days | before_oneyear_twomonth |
+---------------------+---------------------+-------------------------+
| 2020-08-14 17:48:19 | 2020-07-14 17:48:19 | 2019-06-14 17:48:19 |
+---------------------+---------------------+-------------------------+
1 row in set (0.00 sec)
- **DATEDIFF（date1，date2）函數：**用來計算兩個日期之間相差的天數。
mysql> select datediff('2020-10-1', now()); +------------------------------+ | datediff('2020-10-1', now()) | +------------------------------+ | 48 | +------------------------------+ 1 row in set (0.00 sec)
4、流程函數
流程函數也是常用的一類函數，使用者可以使用這類函數在一個SQL語句中實作條件選擇，這樣做能夠提高語句的效率。下表列出了MySQL中跟條件選擇有關的流程函數。下面将通過具體的事例來講解每個函數的用法
| 函數 | 功能 |
|---|---|
| IF(value, t, f) | 如果value是真，傳回t；否則傳回f |
| IFNULL(value1,value2) | 如果value1不為空，傳回value1；否則傳回value2 |
| CASE WHEN [value1] THEN [result1]… ELSE [default] END | 如果value是真，傳回resule1，否則傳回 default |
| CASE[expr] WHEN [value] THEN [result1] … ELSE [default] END | 如果expr等于value1,傳回result1，否則傳回default |
下面的例子中模拟了對職員薪資進行分類，這裡首先建立并初始化一個職員薪水表
mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.07 sec)
插入一些測試資料
mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
6 rows in set (0.00 sec)
接下來通過這個表來介紹各個函數的應用
- IF(value, t ，f)函數：這裡認為月薪在2000 元以上的職員屬于高薪，用“high”來表示，而200元以下的職員屬于低薪，用“low”來表示。
mysql> select userid, salary, if(salary>2000, 'high', 'low') as slary_level from salary; +--------+---------+-------------+ | userid | salary | slary_level | +--------+---------+-------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | low | +--------+---------+-------------+ 6 rows in set (0.00 sec) - IFNULL(value1,value2) 函數：這個函數一般用來替換NULL值，我們知道NULL值是不能參與數值運算的。下面這個語句就是把NULL值用0來替換：
mysql> select userid,salary,ifnull(salary,0) from salary; +--------+---------+------------------+ | userid | salary | ifnull(salary,0) | +--------+---------+------------------+ | 1 | 1000.00 | 1000.00 | | 2 | 2000.00 | 2000.00 | | 3 | 3000.00 | 3000.00 | | 4 | 4000.00 | 4000.00 | | 5 | 5000.00 | 5000.00 | | 1 | NULL | 0.00 | +--------+---------+------------------+ 6 rows in set (0.00 sec) - CASE [expr] WHEN [value] THEN [result1] … ELSE [default] END 函數：這是case的簡單函數用法，case後面跟列名或者列的表達式，when 後面枚舉這個表達式所有可能的值，但不能是值的範圍。如果要實作上面例子中高薪低薪的問題，寫法如下：
mysql> select userid,salary,case salary when 1000 then 'low' when 2000 then 'low' else 'high' end salary_level from salary; +--------+---------+--------------+ | userid | salary | salary_level | +--------+---------+--------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+--------------+ 6 rows in set (0.00 sec) - CASE WHEN [value1] THEN [result1]… ELSE [default] END 函數：這是case的搜尋函數用法，直接在when後面寫條件表達式，并且隻傳回第一個符合條件的值，使用起來更加靈活。上例可以改寫如下：
mysql> select userid,salary, case when salary<=2000 then 'low' else 'high' end from salary; +--------+---------+---------------------------------------------------+ | userid | salary | case when salary<=2000 then 'low' else 'high' end | +--------+---------+---------------------------------------------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+---------------------------------------------------+ 6 rows in set (0.00 sec)
5、JSON 函數
自MySQL5.7.8 新引入了JSON文檔類型，對于JSON文檔的操作，除了簡單的讀寫之外，通常還會有各種各樣的查詢、修改等需求，為此MySQL 也提供了很多相應的函數:
| 函數類型 | 名稱 | 功能 |
| 建立JSON | JSON_ARRAY() | 建立JSON數組 |
| JSON_OBJECT() | 建立JSON對象 | |
| JSON_QUOTE() / JSON_UNQUOTE() | 加上 / 去掉JSON文檔兩邊的雙引号 | |
| 查詢JSON | JSON_CONTAINS() | 查詢文檔中是否包含指定的元素 |
| JSON_CONTAINS_PATH() | 查詢文檔中是否包含指定的路徑 | |
| JSON_EXTRACT()/->/->> | 根據條件提取文檔中的資料 | |
| JSON_KEYS() | 提取所有key的集合 | |
| JSON_SEARCH() | 傳回所有符合條件的路徑集合 | |
| 修改JSON | JSON_MERGE()(deprecated 5.7.22)/JSON_MERGE_PRESERVE | 将兩個文檔合并 |
| JSON_ARRAY_APPEND() | 數組尾部追加元素 | |
| JSON_ARRAY_INSERT() | 在數組的指定位置插入元素 | |
| JSON_REMOVE() | 删除文檔中指定位置的元素 | |
| JSON_REPLACE() | 替換文檔中指定位置的元素 | |
| JSON_SET() | 給文檔中指定位置的元素設定新值，如果元素不存在，則進行插入 | |
| 查詢JSON中繼資料 | JSON_DEPTH() | JSON文檔的深度（元素最大嵌套層數） |
| JSON_LENGTH() | JSON文檔的長度（元素個數） | |
| JSON_TYPE() | JSON文檔類型（數組、對象、标量類型） | |
| JSON_VALID() | JOSN格式是否合法 | |
| 其它函數 | JSON_PRETTY() | 美化JSON格式 |
| JSON_STORAGE_SIZE() | JSON文檔占用的存儲空間 | |
| JSON_STORAGE_FREE() | JSON文檔更新操作後剩餘的空間，MySQL8.0新增 | |
| JSON_TABLE() | 将JSON文檔轉換為表格，MySQL8.0新增 | |
| JSON_ARRAYAGG() | 将聚合後參數中的多個值轉換為JSON數組 | |
| JSON_OBJECTAGG() | 把連個列或者是表達式解釋為一個Key 和一個value 傳回一個JSON對象 |
這些函數安裝功能可以分為以下幾類：
- 建立JSON函數
- 查詢JSON函數
- 修改JSON函數
- chaxunJSONyuanshujuhanshu其它函數
下面将詳細介紹這些函數
5.1、建立JSON函數
5.1.1、 JSON_ARRAY([val[,val]…])
此函數可以傳回包含參數中所有值清單的JSON數組。
以下示例建立一個包含數字、字元串、null、布爾、日期類型在内的混合數組，需要注意的是，參數中的null 和 true/false 大小寫不敏感。
mysql> select JSON_ARRAY(1,"abc",null,TRUE,curtime());
+-------------------------------------------+
| JSON_ARRAY(1,"abc",null,TRUE,curtime()) |
+-------------------------------------------+
| [1, "abc", null, true, "00:02:21.000000"] |
+-------------------------------------------+
1 row in set (0.00 sec)
51.2、 JSON_OBJECT([key,val[,key,val] …])
此函數可以傳回包含參數中所有鍵值對的對象清單。canshuzhongdeKey不能為null，參數個數也不能為奇數，否則報文法錯誤。
以下示例使用了正确的文法：
mysql> select JSON_OBJECT('id',100,'name','jack');
+-------------------------------------+
| JSON_OBJECT('id',100,'name','jack') |
+-------------------------------------+
| {"id": 100, "name": "jack"} |
+-------------------------------------+
1 row in set (0.00 sec)
以下示例則使用了錯誤的文法：
mysql> select JSON_OBJECT('id',100,'name');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'
5.1.3、 JSON_QUOTE(string)
此函數可以将參數中的JSON文檔轉換為雙引号引起來的字元串,如果JSON文檔中包含雙引号，則轉換後的字元串自動加上轉義字元""，如以下示例：
mysql> select JSON_QUOTE('[1,2,3]'),JSON_QUOTE('"null"');
+-----------------------+----------------------+
| JSON_QUOTE('[1,2,3]') | JSON_QUOTE('"null"') |
+-----------------------+----------------------+
| "[1,2,3]" | "\"null\"" |
+-----------------------+----------------------+
1 row in set (0.00 sec)
如果需要将非JSON文檔轉換為JSON文檔，或則反過來，可以使用CONVERT或者CAST函數進行強制轉換，這兩個函數可以在不同資料類型之間進行強制轉換，具體用法參考官方文檔。
5.2、查詢JSON函數
5.2.1、JSON_CONTAINS(target,candidate[,path])
此函數可以查詢指定的元素（candidate）是否包含在目标JSON文檔（target）中，包含則傳回1，否則傳回0，path參數可選。如果有參數為NULL 或path不存在，則傳回NULL。
以下示例分别要查詢元素‘abc’,1,10是否包含在JSON文檔中：
mysql> select json_contains('[1,2,3,"abc",null]','"abc"');
+---------------------------------------------+
| json_contains('[1,2,3,"abc",null]','"abc"') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains('[1,2,3,"abc",null]','1');
+-----------------------------------------+
| json_contains('[1,2,3,"abc",null]','1') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains('[1,2,3,"abc",null]','10');
+------------------------------------------+
| json_contains('[1,2,3,"abc",null]','10') |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set (0.00 sec)
顯然結果符合我們的預期。
元素如果是數組也是可以的：
mysql> select json_contains('[1,2,3,"abc",null]','[1,3]');
+---------------------------------------------+
| json_contains('[1,2,3,"abc",null]','[1,3]') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
path參數是可選的，可以指定在特定的路徑下查詢。如果JSON文檔為對象，則路徑格式通常類似于 . a 或 者 .a或者 .a或者.a.b這種格式。 . a 很 好 理 解 ， 表 示 K e y 為 a ; .a很好了解，表示Key為a; .a很好了解，表示Key為a;.a.b通常用在value也是對象清單的情況，表示鍵a下層的鍵b，比如{“id”:{“id1”:1,“id2”:2}}。如果JSON文檔為數組，則路徑通常寫為$[i] 這種格式，表示數組中第i個元素。
在下例中，要查詢JSON文檔 j 中是否包含value為10的對象，并指定路徑為$.jack(key=‘jack’),如果包含則傳回1，如果不包含則傳回0.那麼SQL代碼可以這麼寫：
mysql> set @j='{"jack":10,"tom":20,"lisa":30}';
Query OK, 0 rows affected (0.00 sec)
mysql> set @j2='10';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_contains(@j,@j2,'$.jack');
+--------------------------------+
| json_contains(@j,@j2,'$.jack') |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set (0.00 sec)
傳回1，表示在路徑key="jack"下，存在value為10的值。将查詢路徑改為tom後，再次查詢：
mysql> select json_contains(@j,@j2,'$.tom');
+-------------------------------+
| json_contains(@j,@j2,'$.tom') |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.00 sec)
此時傳回0，則表示JSON文檔中不包含{“tom”:10}的元素
5.2.2、JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]…)
此函數可以查詢JSON文檔中是否存在指定路徑，存在則傳回1，否則傳回0.one_or_all隻能取值 one 或 all， one表示隻要有一個存在即可；all 表示所有的都存在才行。如果有參數為NULL或path不存在，則傳回NULL。
比如，要查詢給定的3個path 是否至少一個存在或者必須全部存在，可以分别寫SQL代碼如下：
mysql> select json_contains_path('{"k1":"jack","k2":"tom","k3":"lisa"}','one','$.k1','$.k4') one_path;
+----------+
| one_path |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select json_contains_path('{"k1":"jack","k2":"tom","k3":"lisa"}','all','$.k1','$.k4') one_path;
+----------+
| one_path |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select json_contains_path('{"k1":"jack","k2":"tom","k3":"lisa"}','all','$.k1','$.k3') one_path;
+----------+
| one_path |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
5.2.3、JSON_EXTRACT(json_doc, path[, path]…)
此函數可以從JSON文檔裡抽取資料。如果有參數有NULL或者path不存在，則傳回NULL。如果抽取出多個path，則傳回的資料合并在一個JSON ARRAY裡。
以下示例從JSON文檔的第一和第二個元素中提取出對應的value：
mysql> select JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]') |
+-----------------------------------------------+
| [10, 20] |
+-----------------------------------------------+
1 row in set (0.00 sec)
可以看到，傳回的兩個值以數組的形式進行了合并。如果要取第三個數組值，path可以寫為 $2或者$[2][*]:
mysql> select JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[2]');
+-----------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[2]') |
+-----------------------------------------------+
| [10, [30, 40]] |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[2][0]');
+--------------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[2][0]') |
+--------------------------------------------------+
| [10, 30] |
+--------------------------------------------------+
1 row in set (0.00 sec)
在MySQL 5.7.9版本之後，可以用一種更簡單的函數"->"來替代JSON_EXTRACT,文法如下：
column->path
注意左邊隻能是列名，不能是表達式；右邊是要比對的JSON路徑。上面的例子可以改寫為：
mysql> desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id1 | json | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t1 values('[10,20,[30,40]]');
Query OK, 1 row affected (0.01 sec)
mysql> select id1,id1->"$[1]" from t1 where id1->"$[0]"=10;
+--------------------+-------------+
| id1 | id1->"$[1]" |
+--------------------+-------------+
| [10, 20, [30, 40]] | 20 |
+--------------------+-------------+
1 row in set (0.00 sec)
如果JSON文檔查詢的結果是字元串，則顯示結果預設會包含雙引号，在很多情況下是不需要的，為了解決這個問題，MySQL提供了另外兩個函數 JSON_UNQUOTE 和 “->>”, 用法類似于 JSON_EXTRACT 和 ‘->’ ，簡單舉例如下：
mysql> insert into t1 values('{"k1":"jack"}');
Query OK, 1 row affected (0.01 sec)
mysql> select json_extract (id1,'$.k1'),json_unquote(id1->'$.k1'),id1->'$.k1',id1->>'$.k1' from t1 where id1->'$.k1'='jack';
+---------------------------+---------------------------+-------------+--------------+
| json_extract (id1,'$.k1') | json_unquote(id1->'$.k1') | id1->'$.k1' | id1->>'$.k1' |
+---------------------------+---------------------------+-------------+--------------+
| "jack" | jack | "jack" | jack |
+---------------------------+---------------------------+-------------+--------------+
1 row in set (0.00 sec)
及下面3種寫法效果是一樣的：
- JSON_UNQUOTE(JSON_EXTRACT(column, path))
- JSON_UNQUOTE(column->path)
- column ->> path
5.2.4、JSON_KEYS(json_doc [,path])
此函數可以擷取JSON文檔在指定路徑下的所有鍵值，傳回一個JSON ARRAY。如果有參數為null 或path不存在，則傳回null（8.0.16 path不存在，則傳回所有的key）
參數path 通常使用在嵌套對象清單中，如下例所示：
mysql> select JSON_KEYS('{"a":1,"b":{"c":30}}');
+-----------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":30}}') |
+-----------------------------------+
| ["a", "b"] |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_KEYS('{"a":1,"b":{"c":30}}','$.b');
+-----------------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":30}}','$.b') |
+-----------------------------------------+
| ["c"] |
+-----------------------------------------+
1 row in set (0.00 sec)
如果元素中都是數組 ARRAY，則傳回為NULL。
5.2.5、JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char [,path]])
此函數可以查詢包含指定字元串的路徑，并作為一個JSON ARRAY傳回。如果有參數為NULL，或path不存在，則傳回NULL，各參數含義如下：
- one_or_all: one表示查詢到一個即傳回，all表示查詢所有
- search_str:要查詢的字元串，可以用LIKE裡的‘%’ 或‘_'比對
- path：表示在指定path下進行查詢。
以下示例給出了如何查詢JSON文檔中以字母t開頭的元素的第一個路徑:
mysql> select json_search('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','one','t%');
+----------------------------------------------------------------------------+
| json_search('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','one','t%') |
+----------------------------------------------------------------------------+
| "$.k2" |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看出，滿足條件的第一個元素是“k2”:“tom”,path描述為"$.k2"
下面講條件“one” 改成"all",在看看結果：
mysql> select json_search('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','all','t%');
+----------------------------------------------------------------------------+
| json_search('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','all','t%') |
+----------------------------------------------------------------------------+
| ["$.k2", "$.k4"] |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
此時，滿足條件的所有元素是：“k2”:“tom” 和 “k4”:“tony”,路徑描述為[" . k 2 " , " .k2", " .k2",".k4"] 數組。
如果将JSON文檔改為數組，則傳回路徑也将成為數組的描述格式，如下例所示：
mysql> select json_search('["tom","lisa","jack",{"name":"tony"}]',"all","t%");
+-----------------------------------------------------------------+
| json_search('["tom","lisa","jack",{"name":"tony"}]',"all","t%") |
+-----------------------------------------------------------------+
| ["$[0]", "$[3].name"] |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
5.3、修改JSON的函數
5.31、JSON_ARRAY_APPEND(json_doc, path, val[,path,val]…)
此函數可以再指定path的是json array尾部追加val。如果指定path是一個json object,則将其封裝成一個json array再追加。如果有參數為null，則傳回null。
以下示例在JSON文檔的不同path處分别追加字元 “1”；
mysql> select JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[0]', "1");
+-------------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[0]', "1") |
+-------------------------------------------------------+
| [["a", "1"], ["b", "c"], "d"] |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[1]',"1");
+------------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[1]',"1") |
+------------------------------------------------------+
| ["a", ["b", "c", "1"], "d"] |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[1][0]',"1");
+---------------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]', '$[1][0]',"1") |
+---------------------------------------------------------+
| ["a", [["b", "1"], "c"], "d"] |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_array_append('{"a":1,"b":[2,3],"c":4}', "$.b", "1");
+----------------------------------------------------------+
| json_array_append('{"a":1,"b":[2,3],"c":4}', "$.b", "1") |
+----------------------------------------------------------+
| {"a": 1, "b": [2, 3, "1"], "c": 4} |
+----------------------------------------------------------+
1 row in set (0.00 sec)
5.3.2、JSON_ARRAY_INSERT(json_doc, path, val[,path, val]…)
此函數可以在path指定json array 元素插入val ，原位置及以右的元素順序右移。如果path指定的資料非json array 元素，則略過此val；如果指定的元素下标超過 json array的長度，則插入尾部。
将上面例子中的4個SQL語句改成JSON_ARRAY_INSERT ,看一下結果：
mysql> select JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[0]', '1');
+-------------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[0]', '1') |
+-------------------------------------------------------+
| ["1", "a", ["b", "c"], "d"] |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[1]', '1');
+-------------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[1]', '1') |
+-------------------------------------------------------+
| ["a", "1", ["b", "c"], "d"] |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[1][0]', '1');
+----------------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]', '$[1][0]', '1') |
+----------------------------------------------------------+
| ["a", ["1", "b", "c"], "d"] |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_array_insert('{"a":1,"b":[2,3],"c":4}','$.b', '1');
ERROR 3165 (42000): A path expression is not a path to a cell in an array.
最後一個SQL報錯，提示路徑不對，将" . b " 改 為 “ .b" 改為“ .b"改為“[0]” 試一試：
mysql> select json_array_insert('{"a":1,"b":[2,3],"c":4}','$[0]', '1');
+----------------------------------------------------------+
| json_array_insert('{"a":1,"b":[2,3],"c":4}','$[0]', '1') |
+----------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": 4} |
+----------------------------------------------------------+
1 row in set (0.00 sec)
插入路徑正确，但是字元并沒有插入JSON文檔中，因為所有元素都是對象，跳過忽略。
5.3.3、JSON_REPLACE(json_doc, path, val[, path, val]…)
此函數可以替換指定路徑的資料，如果某個路徑不存在，則略過（存在才替換），如果有參數為null，則傳回null。
下面将JSON文檔中的第一個元素和第二個元素分别替換為“1” 和“2”
mysql> select json_replace('["a",["b","c"],"d"]', "$[0]","1", "$[1]", 2);
+------------------------------------------------------------+
| json_replace('["a",["b","c"],"d"]', "$[0]","1", "$[1]", 2) |
+------------------------------------------------------------+
| ["1", 2, "d"] |
+------------------------------------------------------------+
1 row in set (0.00 sec)
下例将JOSN文檔中的key為a和d的對象value分别替換為“10” 和“20”：
mysql> select json_replace('{"a":1,"b":[2,3],"c":4 }', '$.a',"10",'$.d', "20");
+------------------------------------------------------------------+
| json_replace('{"a":1,"b":[2,3],"c":4 }', '$.a',"10",'$.d', "20") |
+------------------------------------------------------------------+
| {"a": "10", "b": [2, 3], "c": 4} |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
5.3.4、JSON_SET(json_doc, path, val[, path,val]…)
此函數可以設定指定路徑的資料（不管是否存在）。如果有參數為null則傳回null，和JSON_REPLACE功能有些類似，最主要的差別是指定的路徑不存在時，會在文檔中自動添加，如下例所示：
mysql> select JSON_SET('{"a":1,"b":[2,3],"c":4}','$.a',10,"$.d",20);
+-------------------------------------------------------+
| JSON_SET('{"a":1,"b":[2,3],"c":4}','$.a',10,"$.d",20) |
+-------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": 4, "d": 20} |
+-------------------------------------------------------+
1 row in set (0.00 sec)
5.3.5、JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc]…)
此函數可以将多個JSON文檔進行合并，合并規則如下：
- 如果是 json array,則結果自動 merge 為一個json array
- 如果是 json object ,則結果自動 merge 為一個json object
- 如果有多種類型，則将非json array的元素封裝成一個 json array再按照規則進行merge。
下例分别将兩個數組合并、兩個對象合并、數組和對象合并
mysql> select json_merge_preserve('[1,2]','[3,4]');
+--------------------------------------+
| json_merge_preserve('[1,2]','[3,4]') |
+--------------------------------------+
| [1, 2, 3, 4] |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select json_merge_preserve('{"key1":"tom"}','{"key2":"lisa"}');
+---------------------------------------------------------+
| json_merge_preserve('{"key1":"tom"}','{"key2":"lisa"}') |
+---------------------------------------------------------+
| {"key1": "tom", "key2": "lisa"} |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_merge_preserve('[1,2]','{"key1":"tom"}');
+-----------------------------------------------+
| json_merge_preserve('[1,2]','{"key1":"tom"}') |
+-----------------------------------------------+
| [1, 2, {"key1": "tom"}] |
+-----------------------------------------------+
1 row in set (0.00 sec)
5.3.6、JSON_REMOVE(json_doc, path[,path]…)
此函數可以移除指定路徑的資料，如果某個路徑不存在則略過此路徑。如果有參數為NULL，則傳回NULL
下例中把JSON文檔中的第二個和第三個元素删除：
mysql> select json_remove('[1,2,3,4]',"$[1]","$[2]");
+----------------------------------------+
| json_remove('[1,2,3,4]',"$[1]","$[2]") |
+----------------------------------------+
| [1, 3] |
+----------------------------------------+
1 row in set (0.00 sec)
結果有些意外，‘ [ 1 ] ’ , ‘ [1]’,‘ [1]’,‘[2]’ 分别為2和3，删除後不是應該為[1，4]，嗎?這裡要注意，如果指定了多個path，則删除操作是串行操作的，即先删除“ [ 1 ] ” 後 J S O N 文 檔 變 為 [ 1 , 3 , 4 ] , 然 後 在 [ 1 , 3 , 4 ] 上 删 除 ′ [1]”後JSON文檔變為[1,3,4] ,然後在[1,3,4] 上删除' [1]”後JSON文檔變為[1,3,4],然後在[1,3,4]上删除′[2]'後變為[1,3]
5.4、查詢JSON中繼資料函數
5.4.1、JSON_DEPTH(json_doc)
此函數用來擷取json文檔的深度。
如果文檔是空數組，空對象、null、true/false，則深度為：1；如果非空數組，非空對象裡面包含的都是深度為1的對象，則整個文檔省深度為2；依次類推，整個文檔的深度取決于最大元素的深度。如下例所示：
mysql> select json_depth('{}'),json_depth('[]'),json_depth('true');
+------------------+------------------+--------------------+
| json_depth('{}') | json_depth('[]') | json_depth('true') |
+------------------+------------------+--------------------+
| 1 | 1 | 1 |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)
mysql> select json_depth('[10,20]'),json_depth('[[],{}]');
+-----------------------+-----------------------+
| json_depth('[10,20]') | json_depth('[[],{}]') |
+-----------------------+-----------------------+
| 2 | 2 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)
mysql> select json_depth('[10,{"a":20}]');
+-----------------------------+
| json_depth('[10,{"a":20}]') |
+-----------------------------+
| 3 |
+-----------------------------+
1 row in set (0.00 sec)
5.4.2、JSON_LENGTH(json_doc[,path])
此函數可以擷取指定路徑下的文檔長度。長度的計算規則如下：
- 标量（字元串、數字）的長度為1
- json array的長度為元素的個數
- json object的長度為對象的個數
- 嵌套數組或則嵌套對象不計算長度
見下例所示：
mysql> select json_length('1'),json_length('[1,2,[3,4]]'), json_length('{"key":"tom"}');
+------------------+----------------------------+------------------------------+
| json_length('1') | json_length('[1,2,[3,4]]') | json_length('{"key":"tom"}') |
+------------------+----------------------------+------------------------------+
| 1 | 3 | 1 |
+------------------+----------------------------+------------------------------+
1 row in set (0.00 sec)
5.4.3、JSON_TYPE(json_val)
此函數可以擷取JSON文檔的具體類型，可以是數組、對象或者标量類型。
mysql> select json_type('[1,3]'),json_type('{"id":"tom"}');
+--------------------+---------------------------+
| json_type('[1,3]') | json_type('{"id":"tom"}') |
+--------------------+---------------------------+
| ARRAY | OBJECT |
+--------------------+---------------------------+
1 row in set (0.00 sec)
mysql> select json_type('1'),json_type('"abc"'),json_type('null'),json_type('true');
+----------------+--------------------+-------------------+-------------------+
| json_type('1') | json_type('"abc"') | json_type('null') | json_type('true') |
+----------------+--------------------+-------------------+-------------------+
| INTEGER | STRING | NULL | BOOLEAN |
+----------------+--------------------+-------------------+-------------------+
1 row in set (0.00 sec)
5.4.4、JSON_VALID(val)
此函數判斷val是否為有效的JSON格式，有效為1，否則為0.
mysql> select json_valid('abc'),json_valid('"abc"'),json_valid('[1,2]'),json_valid('[1,2');
+-------------------+---------------------+---------------------+--------------------+
| json_valid('abc') | json_valid('"abc"') | json_valid('[1,2]') | json_valid('[1,2') |
+-------------------+---------------------+---------------------+--------------------+
| 0 | 1 | 1 | 0 |
+-------------------+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
顯然，字元串兩邊不加雙引号是無效的JSON格式， ‘[1,2’ 少了右中括号也是無效的，都傳回0。
5.5 JSON工具函數
5.5.1、JSON_PRETTY(json_val)
此函數是在 5.7.22版本中新增的，用來美化JSON的輸出格式，使得結果更加易讀。對于數組、對象，每一行顯示一個元素，多層嵌套的元素會在新行中進行縮進，清楚地顯示層級關系，如下例所示：
mysql> select json_pretty('{"a":"10","b":"15","x":{"x1":1,"x2":2,"x3":3}}');
+----------------------------------------------------------------------------------+
| json_pretty('{"a":"10","b":"15","x":{"x1":1,"x2":2,"x3":3}}') |
+----------------------------------------------------------------------------------+
| {
"a": "10",
"b": "15",
"x": {
"x1": 1,
"x2": 2,
"x3": 3
}
} |
+----------------------
5.5.2、JSON_STORAGE_SIZE(json_val) / JSON_STORAGE_FREE(json_val)
JSON_STORAGE_SIZE(json_val) 函數可以擷取JSON文檔占用的存儲空間（byte），而JSON_STORAGE_FREE(json_val)函數可以擷取由于JSON_SET、JSON_REPLACE、JSON_REMOVE操作導緻釋放的空間。
其中，JSON_STORAGE_FREE 是8.0 版本新增的函數。使用者可以在MySQL8.0環境下測試以下示例。下面的例子顯示了對JSON字段update操作前和操作後，兩個函數的顯示結果。
update前：
mysql> create table jtable(jcol json);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into jtable values('{"name":"homer","Stupid":"True"}');
Query OK, 1 row affected (0.01 sec)
mysql> select JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol), jcol from jtable;
+-------------------------+-------------------------+-------------------------------------+
| JSON_STORAGE_SIZE(jcol) | JSON_STORAGE_FREE(jcol) | jcol |
+-------------------------+-------------------------+-------------------------------------+
| 40 | 0 | {"name": "homer", "Stupid": "True"} |
+-------------------------+-------------------------+-------------------------------------+
1 row in set (0.00 sec)
JSON_STORAGE_SIZE顯示了jcol列所占用的空間為40位元組，由于沒有字段更新，是以JSON_STORAGE_FREE顯示為0
update後：
mysql> update jtable set jcol=json_set(jcol,'$.Stupid',1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol),jcol from jtable;
+-------------------------+-------------------------+--------------------------------+
| JSON_STORAGE_SIZE(jcol) | JSON_STORAGE_FREE(jcol) | jcol |
+-------------------------+-------------------------+--------------------------------+
| 40 | 5 | {"name": "homer", "Stupid": 1} |
+-------------------------+-------------------------+--------------------------------+
1 row in set (0.00 sec)
從結果上看，update操作釋放了5個位元組的空間，但JSON_STORAGE_SIZE(jcol)傳回的結果并沒有改變，仍然是40個位元組，這是由于MySQL規定局部更新（使用JSON_SET/JSON_REPLACE/JSON_REMOVE函數進行操作）後的文檔存儲隻能大于等于更新前的size。如果更新值大于原值，則JSON_STORAGE_SIZE則會大于原文檔的size，如下例所示：
mysql> update jtable set jcol=json_set(jcol,'$.Stupid','True123');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol),jcol from jtable;
+-------------------------+-------------------------+----------------------------------------+
| JSON_STORAGE_SIZE(jcol) | JSON_STORAGE_FREE(jcol) | jcol |
+-------------------------+-------------------------+----------------------------------------+
| 43 | 0 | {"name": "homer", "Stupid": "True123"} |
+-------------------------+-------------------------+----------------------------------------+
1 row in set (0.00 sec)
由于更新操作沒有釋放空間，是以JSON_STORAGE_FREE 傳回0，但JSON_STORAGE_SIZE已經顯示增大後的size 43。對于非局部更新（即不使用 JSON_SET/JSON_REPLACE/JSON_REMOVE操作進行更新），上面的函數不滿足之前的邏輯，如下例所示：
mysql> select JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol),jcol from jtable;
+-------------------------+-------------------------+-------------------------------------+
| JSON_STORAGE_SIZE(jcol) | JSON_STORAGE_FREE(jcol) | jcol |
+-------------------------+-------------------------+-------------------------------------+
| 40 | 0 | {"Name": "Homer", "Stupid": "TRUE"} |
+-------------------------+-------------------------+-------------------------------------+
1 row in set (0.00 sec)
mysql> update jtable set jcol='{"Name":"Homer", "Stupid":1}';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select JSON_STORAGE_SIZE(jcol),JSON_STORAGE_FREE(jcol),jcol from jtable;
+-------------------------+-------------------------+--------------------------------+
| JSON_STORAGE_SIZE(jcol) | JSON_STORAGE_FREE(jcol) | jcol |
+-------------------------+-------------------------+--------------------------------+
| 35 | 0 | {"Name": "Homer", "Stupid": 1} |
+-------------------------+-------------------------+--------------------------------+
1 row in set (0.00 sec)
顯然，兩個函數的結果和之前的結果都不一樣，JSON_STORAGE_SIZE顯示的都是JSON文檔的實際size，JSON_STORAGE_FREE則永遠為0。
5.5.3、JSON_TABLE(expr, path COLUMNS(column_list) [AS] alias)
此函數可以将JSON文檔映射為表格。參數中expr可以是表達式或者列；path是用來過濾的JSON路徑；COLUMNS是常量關鍵字；column list 是轉換後的字段清單。
這個函數是MySQL 8.0.4後新增的一個重要的函數，可以将複雜的JSON文檔轉換為表格資料，轉換後的表格可以像正常表一樣做連結、排序、create table as select 等操作，對JSON的資料展示、資料遷移等很多應用領域帶來極大的靈活性和便利性。
下面的例子将JSON文檔中的全部資料轉換為表格，并按表格中的ac字段進行排序：
mysql> select * from JSON_TABLE('[{"a":"3"},{"a":"2"},{"b":1},{"a":0},{"a":[1,2]}]', "$[*]" COLUMNS( rowid for ordinality, ac varchar(100) path "$.a" default '999' on error default '111' on empty, aj json path "$.a" default '{"x":333}' on EMPTY, bx int exists path "$.b" ) ) as tt order by ac;
+-------+------+------------+------+
| rowid | ac | aj | bx |
+-------+------+------------+------+
| 4 | 0 | 0 | 0 |
| 3 | 111 | {"x": 333} | 1 |
| 2 | 2 | "2" | 0 |
| 1 | 3 | "3" | 0 |
| 5 | 999 | [1, 2] | 0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
對例子中的參數簡單介紹一下。
（1）expr，即JSON對象數組 ‘[{“a”:“3”},{“a”:“2”},{“b”:1},{“a”:0},{“a”:[1,2]}]’
（2）過濾路徑（path），其中 “$[*]” 表示文檔中所有的資料，如果改為 “[$0]”,則表示隻轉換文檔中的第一個元素{“a”:“3”}。
（3）column list 包含4個部分的内容。
- rowid FOR ORDINALITY:rowid是轉換後的列名，FOR ORDINALITY表示按照序列順序加一，類似于MySQL中的自增列。資料類型為UNSIGNED INT 初始值為1
- ac VARCHAR(100) PATH “ . a ” D E F A U L T ′ 99 9 ′ O N E R R O R D E F A U L T ′ 11 1 ′ O N E M P T Y : a c 是 轉 換 後 的 列 名 ； V A R C H A R ( 100 ) 是 轉 換 後 的 列 類 型 ； P A T H “ .a” DEFAULT '999' ON ERROR DEFAULT '111' ON EMPTY: ac是轉換後的列名；VARCHAR(100) 是轉換後的列類型；PATH “ .a”DEFAULT′999′ONERRORDEFAULT′111′ONEMPTY:ac是轉換後的列名；VARCHAR(100)是轉換後的列類型；PATH“.a” 說明此字段隻記錄對象的key=“a” 的value；DEFAULT‘999’ ON ERROR 說明發生error，則轉換為預設值999，比如 {“a”:[1,2]}, value 為JSON數組，和VARCHAR不比對，是以此對象轉換後為“999”；DEFAULT‘111’ ON EMPTY 說明對應的key不比對 ‘a’ ,此對象轉換後為 “111”，比如{“b”:1}.
- aj和ac類似，隻是轉換後的列類型為JSON
- bx INT EXISTS PTH " . b " : b x 是 轉 換 後 列 名 ， 如 果 存 在 路 徑 “ .b": bx是轉換後列名，如果存在路徑“ .b":bx是轉換後列名，如果存在路徑“.b”,即key=‘b’ 的對象，則轉換為1；否則為0。
5.5.4、JSON_ARRAYAGG(col_or_expr)
此函數可以将聚合後參數中的多個值轉換為JSON數組。
下面的例子中按照o_id聚合後的屬性清單轉換為一個字元串JSON數組：
mysql> create table t (o_id int, attribute varchar(10), value varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t values(2,'color','red'),(2,'fabric','silk'),(3,'color','green'),(3,'shape','square');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+-----------+--------+
| o_id | attribute | value |
+------+-----------+--------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square |
+------+-----------+--------+
4 rows in set (0.00 sec)
mysql> select o_id, JSON_ARRAYAGG(attribute) as attributes from t group by o_id;
+------+---------------------+
| o_id | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)
5.5.5、JSON_OBJECTAGG(key,value)
此函數可以把兩個列或者是表達式解釋為一個key和一個value，傳回一個JSON對象。
還是上例的資料，這次按照o_id 聚合後的attribute/value 作為對象的key/value組成一個JSON對象文檔。
mysql> select o_id, JSON_OBJECTAGG(attribute, value) from t group by o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)
6、視窗函數
日常開發中，經常會遇到下面這些需求。
- 去醫院看病，怎樣知道上次就醫距現在的時長？
- 環比如何計算
- 怎樣得到各部門工資排名前N名的員工清單
- 如何查找組内每人工資占總工資的百分比？
這類需求都有一個共同點，為了得到結果，都需要在某個結果集内做一些特定的函數操作。為了友善的解決這一問題，MySQL8.0中引入了視窗函數。視窗函數的概念非常重要，它可以了解為記錄集合，視窗函數也就是在滿足某種條件的記錄集合上執行的特殊函數，對于每條記錄都要在此視窗内執行函數。有的函數，随着記錄不同的視窗，視窗大小都是固定的，這種屬于靜态視窗；有的函數則相反，不同的記錄對應着不同的視窗，這種動态變化的視窗叫滑動視窗。
視窗函數和聚合函數有些類似，兩者最大的差別是聚合函數是多行聚合為一行，視窗函數則是多行聚合為相同的行數，每行會多一個聚合後的新列。視窗函數在其他資料庫中（比如 oracle）也稱為分析函數，功能也都大體相似。
MySQL中支援的視窗函數如下：
| 函數 | 功能 |
|---|---|
| ROW_NUMBER() | 分區中目前行号 |
| RANK() | 目前行在分區中的排名，含序号間隙 |
| DENSE_RANK() | 目前行在分區的排名，沒有序号間隙 |
| PERCENT_RANK() | 百分比等級值 |
| CUME_DIST() | 累計配置設定值 |
| FIRST_VALUE() | 視窗中第一行的參數值 |
| LAST_VALUE() | 視窗中最後一行的參數值 |
| LAG() | 分區中指定行落後于目前行的參數值 |
| LEAD() | 分區中領先目前行的參數值 |
| NTH_VALUE() | 從第N行視窗架構的參數值 |
| NTILE(N) | 分區中目前行的桶号 |
下面以訂單表order_tab為例，逐個講解這些函數的使用。測試表中的資料如下，各字段含義按順序分别為訂單号、使用者id、訂單金額、訂單建立日期：
create table order_tab(order_id int, user_no int(3) zerofill, amount int, create_date datetime);
mysql> insert into order_tab values(1, 1,100,'2018-01-01 00:00:00'),(2, 1,300,'2018-01-02 00:00:00'),(3, 1,500,'2018-01-02 00:00:00'),(4, 1,800,'2018-01-03 00:00:00'),(5, 1,900,'2018-01-04 00:00:00'),(6,2,500,'2018-01-03 00:00:00'),(7, 2,600,'2018-01-04 00:00:00'),(8, 2,300,'2018-01-16 00:00:00'),(9, 2,800,'2018-01-22 00:00:00'),(10, 2,800,'2018-01-04 00:00:00');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from order_tab;
+----------+---------+--------+---------------------+
| order_id | user_no | amount | create_date |
+----------+---------+--------+---------------------+
| 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 8 | 002 | 300 | 2018-01-16 00:00:00 |
| 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 10 | 002 | 800 | 2018-01-04 00:00:00 |
+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
6.1、ROW_NUMBER()
如果要查詢每個使用者最新的一筆訂單，我們希望的結果是order_id分别為5和10的記錄，此時可以使用ROW_NUMBER()函數按照使用者進行分組并按照訂單日期進行由大到小的排序，最後查找每組中序号為1的記錄，SQL語句如下：
mysql> select * from (select row_number() over(partition by user_no order by create_date desc) as row_num,order_id,user_no,amount,create_date from order_tab) t where row_num=1;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date |
+---------+----------+---------+--------+---------------------+
| 1 | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 1 | 9 | 002 | 800 | 2018-01-22 00:00:00 |
+---------+----------+---------+--------+---------------------+
2 rows in set (0.00 sec)
其中，row_number() 後面的over是關鍵字，用來指定函數執行的視窗範圍，如果後面的括号中什麼都不寫，則意味着視窗包含所有行，視窗函數在所有行上進行計算；如果不為空，則支援以下4種文法。
- window_name:給視窗指定一個别名，如果SQL 中涉及的視窗較多，采用别名則更清晰易讀。上面的例子中如果指定一個别名w,則改寫代碼如下：
mysql> select * from (select row_number() over w as row_num,order_id,user_no,amount,create_date from order_tab window w as (partition by user_no order by create_date desc)) t where row_num=1; +---------+----------+---------+--------+---------------------+ | row_num | order_id | user_no | amount | create_date | +---------+----------+---------+--------+---------------------+ | 1 | 5 | 001 | 900 | 2018-01-04 00:00:00 | | 1 | 9 | 002 | 800 | 2018-01-22 00:00:00 | +---------+----------+---------+--------+---------------------+ 2 rows in set (0.00 sec) - partition子句：視窗按照哪些字段進行分組，視窗函數在不同的分組上分別執行。上面的例子就按照使用者id進行分組。在每個使用者id上，分别執行從1開始的順序編号。
- order by 子句：按照哪些字段進行排序，視窗函數将按照排序後的記錄順序進行編号。既可以和partition子句配合使用，也可以單獨使用。上例中二者同時使用。
- frame 子句：frame 是目前分區的一個子集，子句用來定義子集的規則，通常用來作為滑動視窗使用。比如要根據每個訂單動态計算包括本訂單和按時間順序前後兩個訂單的平均訂單金額，則可以設定如下 frame子句來建立滑動視窗：
mysql> select * from (select order_id,user_no,amount,avg(amount)over w as avg_num, create_date from order_tab window w as (partition by user_no order by create_date desc ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) t; +----------+---------+--------+----------+---------------------+ | order_id | user_no | amount | avg_num | create_date | +----------+---------+--------+----------+---------------------+ | 5 | 001 | 900 | 850.0000 | 2018-01-04 00:00:00 | | 4 | 001 | 800 | 666.6667 | 2018-01-03 00:00:00 | | 2 | 001 | 300 | 533.3333 | 2018-01-02 00:00:00 | | 3 | 001 | 500 | 300.0000 | 2018-01-02 00:00:00 | | 1 | 001 | 100 | 300.0000 | 2018-01-01 00:00:00 | | 9 | 002 | 800 | 550.0000 | 2018-01-22 00:00:00 | | 8 | 002 | 300 | 566.6667 | 2018-01-16 00:00:00 | | 7 | 002 | 600 | 566.6667 | 2018-01-04 00:00:00 | | 10 | 002 | 800 | 633.3333 | 2018-01-04 00:00:00 | | 6 | 002 | 500 | 650.0000 | 2018-01-03 00:00:00 | +----------+---------+--------+----------+---------------------+ 10 rows in set (0.00 sec) 從結果可以看出，order id 為5訂單屬于邊界值，沒有前一行，是以平均訂單金額為（900+800)/2=850;order id 為4的訂單前後都有訂單，是以平均訂單金額為（900+800+300)／3=666.6667,以此類推就可以得到一個基于滑動視窗的動态平均訂單值。
對于滑動視窗的範圍指定，有如下兩種方式。
（1)基于行：通常使用 BETWEEN frame start AND frame_end 文法來表示行範圍,frame start和 frame end 可以支援如下關鍵字，來确定不同的動态行記錄：
比如，下面都是合法的範圍：CURRENT ROW 邊界是目前行，一般和其他範圍關鍵字一起使用 UNBOUNDED PRECEDING 邊界是分區中的第一行 UNBOUNDED FOLLOWING 邊界是分區中的最後一行 expr PRECEDING 邊界是目前行減去expr的值 expr FOLLOWING 邊界是目前行加上expr的值
（2)基于範圍：和基于行類似，但有些範圍不是直接可以用行數來表示的，比如希望視窗範圍是一周前的訂單開始，截止到目前行，則無法使用rows來直接表示，此時就可以使用範圍來表示視窗：INTERVAL 7 DAY PRECEDING.Linux中常見的計算最近1分鐘、5分鐘、15分鐘負載就是一個典型的應用場景。rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 視窗範圍是目前行、前一行、後一行一共3行記錄 rowS UNBOUNDED FOLLOWING 視窗範圍是目前行到分區中的最後一行 rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 視窗範圍是目前分區中所有行，等同于不寫
6.2、RANK()/DENSE_RANK()
RANK() 和 DENSE_RANK() 這兩個函數與row_number()非常類似，隻是在出現重複值時處理邏輯有所不同。這裡稍微改一上面的示例，假設需要查詢不同使用者的訂單，按照訂單金額進行排序，顯示出相應的排名序号，SQL語句中用 row_number()、rank()、dense_rank() 分别顯示序号，我們來看一下有什麼差別。
mysql> select * from (select row_number()over(partition by user_no order by amount desc) as row_num1, rank() over(partition by user_no order by amount desc) as row_num2, dense_rank() over(partition by user_no order by amount desc) as row_num3, order_id,user_no,amount,create_date from order_tab)t;
+----------+----------+----------+----------+---------+--------+---------------------+
| row_num1 | row_num2 | row_num3 | order_id | user_no | amount | create_date |
+----------+----------+----------+----------+---------+--------+---------------------+
| 1 | 1 | 1 | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 2 | 2 | 2 | 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 3 | 3 | 3 | 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 4 | 4 | 4 | 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 5 | 5 | 5 | 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 1 | 1 | 1 | 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 2 | 1 | 1 | 10 | 002 | 800 | 2018-01-04 00:00:00 |
| 3 | 3 | 2 | 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 4 | 4 | 3 | 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 5 | 5 | 4 | 8 | 002 | 300 | 2018-01-16 00:00:00 |
+----------+----------+----------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
上面的記錄中倒數第3、4、5行的斜體顯示了3個函數的差別，row number() 在amount 都是800的兩條記錄上随機排序，但序号按照1、2遞增，後面amount為600的的序号繼續遞增為3,中間不會産生序号間隙；rank()/dense_rank()則把 amount為800的兩條記錄序号都設定為1,但後續amount為600的需要則分别設定為3(rank)和2(dense rank),即rank()會産生序号相同的記錄，同時可能産生序号間隙；而 dense rank()也會産生序号相同的記錄，但不會産生序号間隙。
6.3、PERCENT_RANK()/CUME_DIST()
PERCENT_RANK()和 CUME_DIST() 這兩個函數都是計算資料分布的函數，PERCENT_RANK() 和之前的RANK() 函數相關，每行按照以下公式進行計算：
（rank-1)/(rows -1)
其中，rank為 RANK()函數産生的序号，rows為目前視窗的記錄總行數，上面的例子修改如下：
mysql> select * from (select rank() over w as row_num, percent_rank() over w as percent,order_id,user_no,amount,create_date from order_tab WINDOW w as (partition by user_no order by amount desc)) t;
+---------+---------+----------+---------+--------+---------------------+
| row_num | percent | order_id | user_no | amount | create_date |
+---------+---------+----------+---------+--------+---------------------+
| 1 | 0 | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 2 | 0.25 | 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 3 | 0.5 | 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 4 | 0.75 | 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 5 | 1 | 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 1 | 0 | 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 1 | 0 | 10 | 002 | 800 | 2018-01-04 00:00:00 |
| 3 | 0.5 | 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 4 | 0.75 | 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 5 | 1 | 8 | 002 | 300 | 2018-01-16 00:00:00 |
+---------+---------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
從結果中可以看出，percent列按照公式（rank -1) / (rows-1) 代入 rank 值 (row_num列）和 rows 值 (user_no 為 ‘001’ 和 ‘002’ 的值均為5) 此函數主要應用在分析領域，日常應用場景較少。
相比PERCENT_RANK().CUME_DIST() 函數的應用場景更多，它的作用是分組内小于等于目前rank值的行數／分組内總行數，上例中，統計大于等于目前訂單金額的訂單數，占總訂單數的比例，SQL代碼如下：
mysql> select * from (select rank() over w as row_num, cume_dist()over w as cume,order_id,user_no,amount,create_date from order_tab WINDOW w as (partition by user_no order by amount desc)) t;
+---------+------+----------+---------+--------+---------------------+
| row_num | cume | order_id | user_no | amount | create_date |
+---------+------+----------+---------+--------+---------------------+
| 1 | 0.2 | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 2 | 0.4 | 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 3 | 0.6 | 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 4 | 0.8 | 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 5 | 1 | 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 1 | 0.4 | 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 1 | 0.4 | 10 | 002 | 800 | 2018-01-04 00:00:00 |
| 3 | 0.6 | 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 4 | 0.8 | 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 5 | 1 | 8 | 002 | 300 | 2018-01-16 00:00:00 |
+---------+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
列cume顯示了預期的結果。
6.4、NTILE(N)
NFILE() 函數的功能是對一個資料分區中的有序結果集進行劃分,将其分為N個組,并為每個小組配置設定一個唯一的組編号。繼續上面的例子,對每個使用者的訂單記錄分為3組,NFILE() 函數記錄每組組編号,SOL代碼如下
mysql> select * from (select ntile(3) over w as nf, order_id, user_no, amount, create_date from order_tab WINDOW w as (partition by user_no order by amount desc)) t;
+------+----------+---------+--------+---------------------+
| nf | order_id | user_no | amount | create_date |
+------+----------+---------+--------+---------------------+
| 1 | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 1 | 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 2 | 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 2 | 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 3 | 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 1 | 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 1 | 10 | 002 | 800 | 2018-01-04 00:00:00 |
| 2 | 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 2 | 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 3 | 8 | 002 | 300 | 2018-01-16 00:00:00 |
+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
此函數在資料分析中應用較多,比如由于資料量大,需要将資料配置設定到N個并行的程序分别計算,此時就可以用 NFILE(N)對資料進行分組,由于記錄數不一定被N整除,是以每組記錄數不一定完全一緻,然後将不同組号的資料再配置設定。
6.5、NTH_VALUE(expr, N)
NTH_VALUE(expr,N)函數可以傳回視窗中第N個expr的值,expr既可以是表達式,也可以是列名。這個函數不太好了解,來看下面的例子
mysql> select * from (select ntile(3) over w as nf,nth_value(order_id,3) over w as nth, order_id, user_no, amount, create_date from order_tab window w as (partition by user_no order by amount desc))t;
+------+------+----------+---------+--------+---------------------+
| nf | nth | order_id | user_no | amount | create_date |
+------+------+----------+---------+--------+---------------------+
| 1 | NULL | 5 | 001 | 900 | 2018-01-04 00:00:00 |
| 1 | NULL | 4 | 001 | 800 | 2018-01-03 00:00:00 |
| 2 | 3 | 3 | 001 | 500 | 2018-01-02 00:00:00 |
| 2 | 3 | 2 | 001 | 300 | 2018-01-02 00:00:00 |
| 3 | 3 | 1 | 001 | 100 | 2018-01-01 00:00:00 |
| 1 | NULL | 9 | 002 | 800 | 2018-01-22 00:00:00 |
| 1 | NULL | 10 | 002 | 800 | 2018-01-04 00:00:00 |
| 2 | 7 | 7 | 002 | 600 | 2018-01-04 00:00:00 |
| 2 | 7 | 6 | 002 | 500 | 2018-01-03 00:00:00 |
| 3 | 7 | 8 | 002 | 300 | 2018-01-16 00:00:00 |
+------+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)
nth列傳回了分組排序後的視窗中 order_id的第三個值, '001’使用者傳回3,'002’使用者傳回7,對于前N-1列,本函數傳回NULL。
6.6、LAG(expr, N)/LEAD(expr,N)
LAG(expr, N)和LEAD(expr,N) 這兩個函數的功能是擷取目前資料行按照某種排序規則的上N行(LAG)/下N行(LEAD)資料的某個字段。比如,每個訂單中希望增加一個字段,用來記錄本訂單距離上一個訂單的時間間隔,那麼就可以用LAG函數來實作,SQL代碼如下：
mysql> select order_id,user_no,amount,create_date,last_date,datediff(create_date,last_date) as diff from (select order_id,user_no,amount,create_date,lag(create_date,1) over w as last_date from order_tab window w as (partition by user_no order by create_date)) t;
+----------+---------+--------+---------------------+---------------------+------+
| order_id | user_no | amount | create_date | last_date | diff |
+----------+---------+--------+---------------------+---------------------+------+
| 1 | 001 | 100 | 2018-01-01 00:00:00 | NULL | NULL |
| 2 | 001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 1 |
| 3 | 001 | 500 | 2018-01-02 00:00:00 | 2018-01-02 00:00:00 | 0 |
| 4 | 001 | 800 | 2018-01-03 00:00:00 | 2018-01-02 00:00:00 | 1 |
| 5 | 001 | 900 | 2018-01-04 00:00:00 | 2018-01-03 00:00:00 | 1 |
| 6 | 002 | 500 | 2018-01-03 00:00:00 | NULL | NULL |
| 7 | 002 | 600 | 2018-01-04 00:00:00 | 2018-01-03 00:00:00 | 1 |
| 10 | 002 | 800 | 2018-01-04 00:00:00 | 2018-01-04 00:00:00 | 0 |
| 8 | 002 | 300 | 2018-01-16 00:00:00 | 2018-01-04 00:00:00 | 12 |
| 9 | 002 | 800 | 2018-01-22 00:00:00 | 2018-01-16 00:00:00 | 6 |
+----------+---------+--------+---------------------+---------------------+------+
10 rows in set (0.00 sec)
内層SQL先通過lag 函數得到上一次訂單的日期,外層SQL再将本次訂單和上次訂單日期做差得到時間間隔。
6.7、FIRST_VALUE (expr)/LAST_VALUE (expr)
FIRST_VALUE(exp)函數和 LAST_VALUE(expr)函數的功能分别是獲得滑動視窗範圍内的參數字段中第一個( FIRST_VALUE)和最後一個( LAST_VALUE)的值。下例中,每個使用者在每個訂單記錄中希望看到截止到目前訂單為止,按照日期排序最早訂單和最晚訂單的訂單金額,SQL語句如下:
mysql> select * from (select order_id,user_no,amount,create_date,first_value(amount) over w as first_amount, last_value(amount) over w as last_amount from order_tab window w as (partition by user_no order by create_date)) t;
+----------+---------+--------+---------------------+--------------+-------------+
| order_id | user_no | amount | create_date | first_amount | last_amount |
+----------+---------+--------+---------------------+--------------+-------------+
| 1 | 001 | 100 | 2018-01-01 00:00:00 | 100 | 100 |
| 2 | 001 | 300 | 2018-01-02 00:00:00 | 100 | 500 |
| 3 | 001 | 500 | 2018-01-02 00:00:00 | 100 | 500 |
| 4 | 001 | 800 | 2018-01-03 00:00:00 | 100 | 800 |
| 5 | 001 | 900 | 2018-01-04 00:00:00 | 100 | 900 |
| 6 | 002 | 500 | 2018-01-03 00:00:00 | 500 | 500 |
| 7 | 002 | 600 | 2018-01-04 00:00:00 | 500 | 800 |
| 10 | 002 | 800 | 2018-01-04 00:00:00 | 500 | 800 |
| 8 | 002 | 300 | 2018-01-16 00:00:00 | 500 | 300 |
| 9 | 002 | 800 | 2018-01-22 00:00:00 | 500 | 800 |
+----------+---------+--------+---------------------+--------------+-------------+
10 rows in set (0.00 sec)
結果和預期一緻,比如 order_id為4的記錄, first_amount和 last_amount分别記錄了使用者 ‘001’ 截到時間 2018-01-03 00:00:00 止,第一條訂單金額100 和最後一條訂單金額800,注意這裡是按時間排序的最早訂單和最晚訂單。并不是最小金額和最大金額訂單
6.8、聚合函數作為視窗函數
除了前面介紹的各類視窗函數外，我們經常使用的各種聚合函數（SUM/AVG/MAX/MIN/COUNT）也可以作為視窗函數來使用。比如要統計每個使用者按照訂單id，截止到目前時間的累積訂單/平均訂單金額/最大訂單金額/最小訂單金額/訂單數是多少，可以用聚合函數作為視窗函數實作如下：
mysql> select order_id,user_no,amount,create_date,sum(amount) over w as sum1,avg(amount) over w as avg1,max(amount) over w as max1,min(amount) over w as min1,count(amount) over w as count1 from order_tab window w as (partition by user_no order by order_id);
+----------+---------+--------+---------------------+------+----------+------+------+--------+
| order_id | user_no | amount | create_date | sum1 | avg1 | max1 | min1 | count1 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
| 1 | 001 | 100 | 2018-01-01 00:00:00 | 100 | 100.0000 | 100 | 100 | 1 |
| 2 | 001 | 300 | 2018-01-02 00:00:00 | 400 | 200.0000 | 300 | 100 | 2 |
| 3 | 001 | 500 | 2018-01-02 00:00:00 | 900 | 300.0000 | 500 | 100 | 3 |
| 4 | 001 | 800 | 2018-01-03 00:00:00 | 1700 | 425.0000 | 800 | 100 | 4 |
| 5 | 001 | 900 | 2018-01-04 00:00:00 | 2600 | 520.0000 | 900 | 100 | 5 |
| 6 | 002 | 500 | 2018-01-03 00:00:00 | 500 | 500.0000 | 500 | 500 | 1 |
| 7 | 002 | 600 | 2018-01-04 00:00:00 | 1100 | 550.0000 | 600 | 500 | 2 |
| 8 | 002 | 300 | 2018-01-16 00:00:00 | 1400 | 466.6667 | 600 | 300 | 3 |
| 9 | 002 | 800 | 2018-01-22 00:00:00 | 2200 | 550.0000 | 800 | 300 | 4 |
| 10 | 002 | 800 | 2018-01-04 00:00:00 | 3000 | 600.0000 | 800 | 300 | 5 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
10 rows in set (0.00 sec)
可以看到sum1/avg1/max1/min1/count1 的結果完全符合預期
7、其他常用函數
MySQL 提供的函數很豐富，除了前面介紹的字元串函數、數字函數、日期函數、流程函數以外還有很多其他函數，在此不再一一列舉，有興趣的讀者可以參考MySQL 官方手冊。
| 函數 | 功能 |
| DATABASE() | 傳回目前資料庫名 |
| VERSION() | 傳回目前資料庫版本 |
| USER() | 傳回目前登入使用者名 |
| INET_ATON(IP) | 傳回IP 位址的數字表示 |
| INET_NTOA(num) | 傳回數字代表的IP 位址 |
| PASSWORD(str) | 傳回字元串str 的加密版本 |
| MD5() | 傳回字元串str 的MD5 值 |
下面結合執行個體簡單介紹一下這些函數的用法。
- DATABASE()函數：傳回目前資料庫名。
mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) - VERSION()函數：傳回目前資料庫版本。
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.16 | +-----------+ 1 row in set (0.00 sec) - USER()函數：傳回目前登入使用者名。
mysql> select user(); +----------------+ | user() | +----------------+ | [email protected] | +----------------+ 1 row in set (0.00 sec) - INET_ATON(IP)函數：傳回IP 位址的網絡位元組序表示。
mysql> select INET_ATON('192.168.0.185'); +----------------------------+ | INET_ATON('192.168.0.185') | +----------------------------+ | 3232235705 | +----------------------------+ 1 row in set (0.00 sec) - INET_NTOA(num)函數：傳回網絡位元組序代表的IP 位址。
INET_ATON(IP)和INET_NTOA(num)函數主要的用途是将字元串的IP 位址轉換為數字表示的網絡位元組序，這樣可以更友善地進行IP 或者網段的比較。比如在下面的表t 中，要想知道在“192.168.1.3”和“192.168.1.20”之間一共有多少IP 位址：mysql> select INET_NTOA(3232235705); +-----------------------+ | INET_NTOA(3232235705) | +-----------------------+ | 192.168.0.185 | +-----------------------+ 1 row in set (0.00 sec)
按照正常的思維，應該用字元串來進行比較，下面是字元串的比較結果：mysql> select * from t; +--------------+ | ip | +--------------+ | 192.168.1.1 | | 192.168.1.3 | | 192.168.1.6 | | 192.168.1.10 | | 192.168.1.20 | | 192.168.1.30 | +--------------+ 6 rows in set (0.00 sec)mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20'; Empty set (0.01 sec)結果沒有如我們所願，竟然是個空集。其實原因就在于字元串的比較是一個字元一個字元的比較，當對應字元相同時候，就比較下一個，直到遇到能區分出大小的字元，才停止比較，後面的字元也将忽略。顯然，在此例中，“192.168.1.3”其實比“192.168.1.20”要“大”，因為“3”比“2”大，而不能用我們日常的思維3<20，是以“ip>=‘192.168.1.3’ and ip<=‘192.168.1.20’”必然是個空集。
在這裡，如果要想實作上面的功能，就可用函數INET_ATON 來實作，将IP 轉換為位元組序後
再比較，如下所示:
結果完全符合我們的要求。mysql> select * from t where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20'); +--------------+ | ip | +--------------+ | 192.168.1.3 | | 192.168.1.6 | | 192.168.1.10 | | 192.168.1.20 | +--------------+ 4 rows in set (0.00 sec) -
PASSWORD(str)函數：傳回字元串str 的加密版本，一個41 位長的字元串。此函數隻用來設定系統使用者的密碼，但是不能用來對應用的資料加密。如果應用方面有加密的需求，可以使用MD5 等加密函數來實作。
下例中顯示了字元串“123456”的PASSWORD 加密後的值：
-
MD5(str)函數：傳回字元串str 的MD5 值，常用來對應用中的資料進行加密。
下例中顯示了字元串“123456”的MD5 值：
mysql> select MD5('123456'); +----------------------------------+ | MD5('123456') | +----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | +----------------------------------+ 1 row in set (0.00 sec)
小結
本章主要對MySQL 常用的各類常用函數通過執行個體做了介紹。MySQL 有很多内建函數，這些内建函數實作了很多應用需要的功能并且擁有很好的性能，如果使用者在工作中需要實作某種功能，最好先查一下MySQL 官方文檔或者幫助，看是否已經有相應的函數實作了我們需要的功能，可以大大提高工作效率。由于篇幅所限，本章并沒有介紹所有的函數，讀者可以去進一步查詢相關文檔。
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)