Json 数据类型与函数

Json 类型与使用

MySQL 从5.7开始就支持JavaScript 对象表示 (Java Script Object Mptatopm JSON) 仅将数据库类型修改成 JSON 即可,

JSON 类型的表

CREATE TABLE `test` (
  `id` int NOT NULL COMMENT 'ID',
  `json` json NOT NULL COMMENT 'json',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

通过JSON进行过滤和查询

-- 显示JSON某个字段
SELECT json->'$.data' FROM test;

-- 通过JSON过滤数据
SELECT json->'$.data' FROM test WHERE json->'$.data' = 1;

Json 函数

JSON_PRETTY()

以优雅的格式显示JSON args0: json 字段

SELECT JSON_PRETTY(json)  FROM test;

JSON_CONTAINS_PATH()

判断Json是否存在此字段 args0: json 字段 args1: ‘one’ 表示至少存在一个 ‘all’表示全部存在 args2: json结构字符 args2: json结构字符 (N ..)

SELECT JSON_CONTAINS_PATH(json, 'one', '$.data') FROM test;

JSON_SET()

替换JSON中的某个值 args0: json 字段 args1: 表示key 结构字符 args2: 表示value 对象 [args1, args2, .. N]

UPDATE test SET json = JSON_SET(json, '$.name', '李大双', '$.age', 18) WHERE id = 1;

JSON_INSERT()

插入新值但不替换现有的值

UPDATE test SET json = JSON_INSERT(json, '$.alias', '狗子', '$.age', 18) WHERE id = 1;

JSON_REPLACE()

仅替换现有的值

UPDATE test SET json = JSON_REPLACE(json, '$.alias', '狗子', '$.age', 18) WHERE id = 1;

CTE 的使用

MySQL8 支持公用表达式 (CTE)，支持递归和非递归二种。公共表达式允许使用命名的临时结果集，这是通过允许在SELECT 语句和某些其他语句签名使用WITH子句来实现的。

CTE 非递归

公共表达式（CTE）与派生表达类似，但它的声明会放在查询块之前，而不是FROM子句中。 CTE语法：WITH [CTE名称] AS (子查询) 读到这里是不是感觉很懵逼，它到底有什么用? 看下面例子

-- 场景查询工资较上一年比例; 下面第3行第5行重复子查询
SELECT 100 * ((a.sum - b.sum) / a.number) AS "rate" FROM
    (SELECT SUM(salary) AS "sum", SUM(year) AS "year" GROUP BY year) a
LEFT JOIN
    (SELECT SUM(salary) AS "sum", SUM(year) AS "year" GROUP BY year) b
WHERE a.year = b.year - 1;

使用CTE 之后

-- 创建CTE
WITH SALARY_TABLE AS (SELECT SUM(salary) AS "sum", SUM(year) AS "year" GROUP BY year)

-- 在查询中使用
SELECT 100 * ((a.sum - b.sum) / a.number) AS "rate" FROM SALARY_TABLE AS a
LEFT JOIN SALARY_TABLE AS b
WHERE a.year = b.year - 1;

CTE 递归

递归CTE子查询包括两部分: seed 查询和 recursive查询, 由UNION ALL 或者 UNION DISTINCT 分隔。 CTE语法：WITH RECURSIVE [CTE名称] AS (子查询) ; 子查询必须包含UNION ALL 或 UNION DISTINCT 直到连接下面的查询没有数据返回为止，**务必避免死循环** !!

-- 递归的CTE
WITH RECURSIVE cte_name AS (
    SELECT * FROM USER WHERE id = 2 
  UNION ALL
    SELECT a.* FROM USER a INNER JOIN cte_name b ON a.pid = b.id 
) 
SELECT * FROM cte_name;

窗口函数特性

对于查询中的每一行，都可以使用窗口函数，利用与该行相关的行执行计算；这就是窗口函数。

CUME_DIST()

累积分布值

SELECT CUME_DIST() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

DENSE_RANK()

分区内当前行的等级（无间隔）

SELECT DENSE_RANK() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

FIRST_VALUE()

窗口帧中第一行的参数值

SELECT CUME_DIST() OVER w AS 'Rank', LAST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

LAG()

落后于分区内当前行的那一行的参数值

SELECT CUME_DIST() OVER w AS 'Rank', LAST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

LAST_VALUE()

窗口帧中最末行的参数值

SELECT RANK() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

LEAD()

领先于分区内当前行的那一行的参数值

SELECT CUME_DIST() OVER w AS 'Rank', LEAD(salary,0) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

NTH_VALUE()

窗口帧中的第N行的参数值

SELECT CUME_DIST() OVER w AS 'Rank', NTH_VALUE(salary,1) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

NTILE()

分区内当前行的桶编号

SELECT NTILE(3) OVER w AS 'Rank', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

PERCENT_RANK()

百分比排名值

SELECT PERCENT_RANK() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

RANK()

分区中当前行的等级（有间隔）

-- 根据字段计算出排行第几
SELECT RANK() OVER(  ORDER BY salary DESC), u.* FROM USER u

ROW_NUMBER()

分区内当前行的编号

-- 通过OVER内的排序规则给数据排序并生成顺序ID
SELECT ROW_NUMBER() OVER(ORDER BY salary DESC), u.* FROM USER u

多次窗口函数

在SELECT 中定义函数, 在FROM 后面进行使用

SELECT RANK() OVER w AS 'Rank', FIRST_VALUE(salary) OVER w AS 'F', u.* FROM USER u
WINDOW w AS (ORDER BY salary DESC)

分割结果

窗口函数还可以对结果进行分区计算

-- 根据分区查找数据
SELECT ROW_NUMBER() OVER(PARTITON BY pid ORDER BY salary DESC), u.* FROM USER u

Generated Column (生成列)

生成列（Generated Column）的值是根据列定义中包含的表达式计算得出的。生产列包含二种类型： Virtual 生成列：当从表中读取记录时，将计算该列。 Stored 生成列: 当想表中写入新记录时，将计算该列并将其作为常规列存储在表中。

Virtual 虚拟列

虚拟列; 通过多个物理列动态计算出来结果, 虚拟列不占用任何存储空间

-- 创建表
CREATE TABLE `test` (
  `id` int NOT NULL COMMENT 'ID',
  `json` json NOT NULL COMMENT 'json',
  `f_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `full_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (concat(`f_name`,_utf8mb4'_',`s_name`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 插入数据
INSERT INTO `test`(`id`, `json`, `f_name`, `s_name`) VALUES (1, '{}', '1', '2');

-- 查询
SELECT * FROM test;

Stored 存储列

存储列; 类似触发器监听每次插入数据或修改数据时更新列，是实际存储到磁盘的每次插入或者修改数据的时候不需要修改此字段，此字段会自动更新

CREATE TABLE `test` (
  `id` int NOT NULL COMMENT 'ID',
  `json` json NOT NULL COMMENT 'json',
  `f_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `full_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (concat(`f_name`,_utf8mb4'_',`s_name`)) VIRTUAL NOT NULL,
  `stored_name` varchar(100) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (concat(`f_name`,_utf8mb4'_',`s_name`)) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

MySQL8 最流行数据库

MySQL的JSON、CTE、窗口函数以及Generated Column说明

Json 数据类型与函数

Json 类型与使用

Json 函数

JSON_PRETTY()

JSON_CONTAINS_PATH()

JSON_SET()

JSON_INSERT()

JSON_REPLACE()

CTE 的使用

CTE 非递归

CTE 递归

窗口函数特性

CUME_DIST()

DENSE_RANK()

FIRST_VALUE()

LAG()

LAST_VALUE()

LEAD()

NTH_VALUE()

NTILE()

PERCENT_RANK()

RANK()

ROW_NUMBER()

多次窗口函数

分割结果

Generated Column (生成列)

Virtual 虚拟列

Stored 存储列