-
Mysql内置函数整理--高级函数
基于Mysql 8.0
Xml函数
参考：https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html
Xml函数的处理基于
XPath 1.0Name Description ExtractValue()ExtractValue(xml_frag, xpath_expr)。从xml字符串中抽取数据。 UpdateXML()UpdateXML(xml_target, xpath_expr, new_xml)。更新xml。 SET @xml = 'XY'; SET @i =1, @j = 2; SELECT @i, ExtractValue(@xml, '//b[$@i]'); -- output： 1 X SELECT UpdateXML('ccc', '/a', ' fff ') AS val1, UpdateXML('ccc', '/b', ' fff ') AS val2, UpdateXML('ccc', '//b', ' fff ') AS val3, UpdateXML('ccc', '/a/d', ' fff ') AS val4, UpdateXML('ccc ', '/a/d', ' fff ') AS val5 -- output：fff cccfff ccc fff ccc - 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
很多xpath函数不支持，具体参考官方文档。
空间分析函数
JSON函数
参考：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
创建JSON函数
- JSON_ARRAY([val[, val] …])
创建一个JSON数组
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); +---------------------------------------------+ | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) | +---------------------------------------------+ | [1, "abc", null, true, "11:24:41.000000"] | +---------------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- JSON_OBJECT([key, val[, key, val] …])
创建一个JSON对象
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot'); +-----------------------------------------+ | JSON_OBJECT('id', 87, 'name', 'carrot') | +-----------------------------------------+ | {"id": 87, "name": "carrot"} | +-----------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- JSON_QUOTE(string)
用双引号(“”) 转化字符串。
mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"'); +--------------------+----------------------+ | JSON_QUOTE('null') | JSON_QUOTE('"null"') | +--------------------+----------------------+ | "null" | "\"null\"" | +--------------------+----------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
搜索JSON函数
- JSON_CONTAINS(target, candidate[, path])
搜索target中是否存在指定json对象。
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; Query OK, 0 rows affected (0.00 sec) mysql> SET @j2 = '1'; Query OK, 0 rows affected (0.00 sec) #搜索 属性a，搜索到。 mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.03 sec) # 搜索 属性a，未搜索到。 mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.b') | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec) # 搜索 属性a 值为 一个对象 ，未搜索到。 mysql> SET @j2 = '{"d": 4}'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec) # 搜索 属性c， 值为指定，搜索到。 mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.c') | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断一个doc在指定的多个path上有数据。
one_or_all：‘one’ 或 ‘all’。'one’时，如果有一个匹配，则返回1 。 ‘all’ ，则所有的路径都要匹配，否则返回0 。
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); +---------------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') | +---------------------------------------------+ | 1 | +---------------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); +---------------------------------------------+ | JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') | +---------------------------------------------+ | 0 | +---------------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d'); +----------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.c.d') | +----------------------------------------+ | 1 | +----------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d'); +----------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.a.d') | +----------------------------------------+ | 0 | +----------------------------------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- JSON_EXTRACT(json_doc, path[, path] …)
抽取指定路径的数据。如果匹配多个对象，则返回数组，否则返回单个值(值或对象)。
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); +--------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') | +--------------------------------------------+ | 20 | +--------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); +----------------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') | +----------------------------------------------------+ | [20, 10] | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); +-----------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') | +-----------------------------------------------+ | [30, 40] | +-----------------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- column->path
JSON_EXTRACT() 的别名。可以用在 引用到表的列的语句中。
SELECT c, JSON_EXTRACT(c, "$.id"), g FROM jemp WHERE JSON_EXTRACT(c, "$.id") > 1 ORDER BY JSON_EXTRACT(c, "$.name"); -- 等同于： SELECT c, c->"$.id", g FROM jemp WHERE c->"$.id" > 1 ORDER BY c->"$.name";- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- column->>path
以下3个语句是等同的。可以用在引用表的列的语句中。
JSON_UNQUOTE( JSON_EXTRACT(column, path) ) JSON_UNQUOTE(column -> path) column->>path- 1
- 2
- 3
- JSON_KEYS(json_doc[, path])
抽取对象的顶层属性，构成一个数组。如果指定了path，则顶层属性则为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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- JSON_OVERLAPS(json_doc1, json_doc2) (since 8.0.17 )
比较2个json doc是否有相同的数组元素或者key-value 对。
- 1
- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
在json doc中指定path搜索字符串。返回的是字符串的path。
search_str：和like一样，支持_，%escape_char：如果不设置或设置为NULL，则默认是“\”，mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; # 搜索第一个"abc"，第一个元素 mysql> SELECT JSON_SEARCH(@j, 'one', 'abc'); +-------------------------------+ | JSON_SEARCH(@j, 'one', 'abc') | +-------------------------------+ | "$[0]" | +-------------------------------+ 1 row in set (0.00 sec) # 搜索所有"abc"，第1个元素，第3个元素的x属性 mysql> SELECT JSON_SEARCH(@j, 'all', 'abc'); +-------------------------------+ | JSON_SEARCH(@j, 'all', 'abc') | +-------------------------------+ | ["$[0]", "$[2].x"] | +-------------------------------+ 1 row in set (0.00 sec) #未搜索到 mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi'); +-------------------------------+ | JSON_SEARCH(@j, 'all', 'ghi') | +-------------------------------+ | NULL | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH(@j, 'all', '10'); +------------------------------+ | JSON_SEARCH(@j, 'all', '10') | +------------------------------+ | "$[1][0].k" | +------------------------------+ 1 row in set (0.00 sec) #在第2个元素的第1个元素搜索 10， 查找到k属性。 mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]'); +-----------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') | +-----------------------------------------------+ | "$[1][0].k" | +-----------------------------------------------+ 1 row in set (0.00 sec) #在第3个元素搜索 %b% mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') | +---------------------------------------------+ | "$[2].x" | +---------------------------------------------+ 1 row in set (0.00 sec) #在第4个元素搜索 %b%， mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]'); +-------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') | +-------------------------------------------+ | "$[3].y" | +-------------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- JSON_VALUE(json_doc, path) (since 8.0.21)
返回指定path的值。
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error]) on_empty: {NULL | ERROR | DEFAULT value} ON EMPTY on_error: {NULL | ERROR | DEFAULT value} ON ERROR- 1
- 2
- 3
- 4
- 5
- 1
- value MEMBER OF(json_array) (since 8.0.17)
判断value 是否是 json array的一个元素。
- 1
修改JSON函数
- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
在json array 最后添加元素。在JSON_APPEND() 中 命名为：
JSON_APPEND() ，不再使用。mysql> SET @j = '["a", ["b", "c"], "d"]'; #添加 元素 1 到 第二个元素(数组)中。 mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1]', 1) | +----------------------------------+ | ["a", ["b", "c", 1], "d"] | +----------------------------------+ 1 row in set (0.01 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
在json array 中插入元素，插入在指定的path前。在array中未找到path，则插入在最后。
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]'; mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["a", "x", {"b": [1, 2]}, [3, 4]] | +------------------------------------+ 1 row in set (0.00 sec) #在第100个元素前插入'x'，最终插入到最后了。 mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x'); +--------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[100]', 'x') | +--------------------------------------+ | ["a", {"b": [1, 2]}, [3, 4], "x"] | +--------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- JSON_INSERT(json_doc, path, val[, path, val] …)
在指定path上插入data，如果path不存在，则作为属性。
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; #在属性c上插入值，由于属性c不存在，所以插入了一个值。 #属性a上有值，所以插入不进去了。 mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)); +------------------------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) | +------------------------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": [true, false]} | +------------------------------------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并json doc。等同于
JSON_MERGE_PRESERVE()，在MySQL 8.0.3 被标记为废弃。- 1
- JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)
- JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)
- JSON_REMOVE(json_doc, path[, path] …)
- JSON_REPLACE(json_doc, path, val[, path, val] …)
- JSON_SET(json_doc, path, val[, path, val] …)
JSON_SET()：存在则替换，不存在则插入。
JSON_INSERT()：仅不存在时插入。
JSON_REPLACE() ：仅存在时替换。
- JSON_UNQUOTE(json_val)
把json 对象去掉引号，转换为string。
返回JSON属性函数
- JSON_DEPTH(json_doc)
返回json doc 深度。
mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); +------------------+------------------+--------------------+ | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') | +------------------+------------------+--------------------+ | 1 | 1 | 1 | +------------------+------------------+--------------------+ mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); +------------------------+------------------------+ | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') | +------------------------+------------------------+ | 2 | 2 | +------------------------+------------------------+ mysql> SELECT JSON_DEPTH('[10, {"a": 20}]'); +-------------------------------+ | JSON_DEPTH('[10, {"a": 20}]') | +-------------------------------+ | 3 | +-------------------------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
-
JSON_LENGTH(json_doc[, path])
-
JSON_TYPE(json_val)
-
JSON_VALID(val)
JSON工具函数
-
JSON_PRETTY(json_val)
-
JSON_STORAGE_FREE(json_val)
-
JSON_STORAGE_SIZE(json_val)
JSON表函数(since 8.0)
- JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
把json doc 返回一个表格。
JSON_TABLE( expr, path COLUMNS (column_list) ) [AS] alias column_list: column[, column][, ...] column: name FOR ORDINALITY | name type PATH string path [on_empty] [on_error] | name type EXISTS PATH string path | NESTED [PATH] path COLUMNS (column_list) on_empty: {NULL | DEFAULT json_string | ERROR} ON EMPTY on_error: {NULL | DEFAULT json_string | ERROR} ON ERROR- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
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 '111' ON EMPTY DEFAULT '999' ON ERROR, aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY, bx INT EXISTS PATH "$.b" ) ) AS tt; -- OUTPUT -- aj 是个JSON类型 1 3 "3" 0 2 2 2 0 3 111 {"x": 333} 1 4 0 0 0 5 999 [1, 2] 0- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
JSON 模式校验函数(since 8.0)
- JSON_SCHEMA_VALID(schema,document)
检查json string 是否schema正确。
- JSON_SCHEMA_VALIDATION_REPORT(schema,document)
聚合函数
参考：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
与其他数据库SQL 一样。
Name Description AVG()Return the average value of the argument BIT_AND()Return bitwise AND BIT_OR()Return bitwise OR BIT_XOR()Return bitwise XOR COUNT()Return a count of the number of rows returned COUNT(DISTINCT)Return the count of a number of different values GROUP_CONCAT()Return a concatenated string JSON_ARRAYAGG()Return result set as a single JSON array JSON_OBJECTAGG()Return result set as a single JSON object MAX()Return the maximum value MIN()Return the minimum value STD()Return the population standard deviation STDDEV()Return the population standard deviation STDDEV_POP()Return the population standard deviation STDDEV_SAMP()Return the sample standard deviation SUM()Return the sum VAR_POP()Return the population standard variance VAR_SAMP()Return the sample variance VARIANCE()Return the population standard variance 其他函数
函数 描述 示例 引入 废弃 ANY_VALUE()ONLY_FULL_GROUP_BY模式下，非聚合字段的取值。取任意一个值。标准group by 语句中，一个字段要么在group by中，要么在聚合函数中引用。否则异常。 BIN_TO_UUID()BIN_TO_UUID(binary_uuid), BIN_TO_UUID(binary_uuid, swap_flag) 8.0 DEFAULT()DEFAULT(col_name)。返回字段默认值，如果不存在，则抛出异常。 GROUPING()GROUPING(expr [, expr] …)。ROLLUP，返回分级聚合。 8,0 INET_ATON()INET_ATON(expr)，IPv4转化为int。 SELECT INET_ATON(‘10.0.5.9’);
167773449INET_NTOA()INET_NTOA(expr)。int转化为ipv4。 INET6_ATON()ipv6 INET6_NTOA()ipv6 IS_IPV4()IS_IPV4(expr)，是否是ipv4。 IS_IPV4_COMPAT()IS_IPV4_COMPAT(expr) IS_IPV4_MAPPED()IS_IPV4_MAPPED(expr) IS_IPV6()IS_IPV6(expr) IS_UUID()IS_UUID(string_uuid) 8.0 MASTER_POS_WAIT()MASTER_POS_WAIT(log_name,log_pos[,timeout] [,channel]) 8.0.26 NAME_CONST()NAME_CONST(name,value) SLEEP()SLEEP(duration)。单位：秒。 SOURCE_POS_WAIT()SOURCE_POS_WAIT(log_name,log_pos[,timeout] [,channel]) 8.0.26 UUID()UUID() UUID_SHORT()UUID_SHORT()。 UUID_TO_BIN()UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag) 8.0 VALUES()VALUES(col_name)。在INSERT … ON DUPLICATE KEY UPDATE语句中，在UPDATE 部分，可以通过values©获取插入的字段值。 窗口函数(since 8.0)
参考：https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
函数分类
分类 函数 描述 序号函数 ROW_NUMBER()在一个窗口内的行号，在一个窗口内不会重复。 RANK()在一个窗口内的行号，在一个窗口内会有并列的情况。例如1,1,3,3,5 DENSE_RANK()在一个窗口内的行号，会有并且情况，并且的不会跳过，例如1,1,2,2,3,3,3,4,4,4 分布函数 PERCENT_RANK()等级值百分比 CUME_DIST()累计分布值 前后函数 LEAD()返回当前行的后n行的expr值。LEAD(expr [, N[, default]]) 。N默认为1，default默认为NULL LAG()返回当前行的前n行的expr值。LAG(expr [, N[, default]])。N默认为1，default默认为NULL 首尾函数 FIRST_VALUE()返回第一个expr值。 LAST_VALUE()返回最后一个expr值。 其他函数 NTH_VALUE()返回第n行的expr值。NTH_VALUE(expr, N)。 NTILE()将分区中的数据分为n个桶，记录桶编号。NTILE(N)。 窗口SQL 语法
over_clause: {OVER (window_spec) | OVER window_name} window_spec: [window_name] [partition_clause] [order_clause] [frame_clause] partition_clause: PARTITION BY expr [, expr] ... order_clause: ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ... frame_clause: frame_units frame_extent frame_units: {ROWS | RANGE} frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
注意：
窗口函数，是整个SQL语句中最后执行的部分，意味着窗口函数是在查询结果上进行的。因此不会受到group by ，having ，where 的影响。
窗口函数是对结果集进行操作，并且输入行数等于输出行数，因此窗口函数不应该和
group by一起使用。select a,row_number() over() from t_window group by a -- OUTPUT： row_number 是以返回的分组，计算行号 a1 1 a2 2- 1
- 2
- 3
- 4
- 5
- 6
窗口从句(Frame Specification)
与window窗口函数一起使用窗口包含一个frame子句。frame是当前分区的子集，frame子句指定如何定义该子集。Frame是相对于当前行确定的，这使得frame可以在分区中根据当前行在分区中的位置移动。
作为窗口函数使用的聚合函数，对当前frame中的行记录进行操作。非聚合窗口函数也是如此：
- FIRST_VALUE()
- LAST_VALUE()
- NTH_VALUE()
标准SQL规定，操作整个分区的窗口函数不应该有frame子句。MySQL允许为这样的函数使用一个frame子句，但是会忽略它。即使指定了frame，这些函数也会使用整个分区:
- RANK()
- ROW_NUMBER()
- DENSE_RANK()
- LAG()
- LEAD()
- NTILE()
- PERCENT_RANK()
- CUME_DIST()
在没有frame子句的情况下，默认frame取决于是否存在
ORDER BY子句，如本节后面所述。frame_units指示当前行与frame的行之间的关系类型：ROWS: frame由开始和结束行位置定义。Offset是行号与当前行号的差异。RANGE: frame由一个值范围范围定义。Offset 是行值与当前行值的差异。
未指定，则默认是RANGE。
frame_extent值指示frame的开始和结束点。可以进指定frame的开始点(此种情况，隐式的表示当前行为结束点)。或者使用BETWEEN指示开始和结束点：frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
使用
BETWEEN语法, *frame_start*必须放在 *frame_end*前面。frame_start和*frame_end* 允许值：-
CURRENT ROW:ROWS, 边界是当前行。RANGE`, 边界是当前行的对等行(值相等的行)。 -
UNBOUNDED PRECEDING: 界限是分区的第一行。 -
UNBOUNDED FOLLOWING: 界限是分区的最后一行。 -
exprPRECEDING :ROWS, 边界是当前行的前expr行。RANGE, 边界是 值等于 当前行的值减去expr的行；如果当前行的值是NULL, 边界是该行的对等行。对于
expr PRECEDING(和expr FOLLOWING),expr可以是?参数标记(在一个prepared 语句),一个非负字面量数字, 或INTERVAL val unit形式. INTERVAL 表达式中,val为非负整数值,unit是时间单位。数值或时间表达式上的
RANGE分别要求对数值或时间表达式按顺序进行排序。10 PRECEDING INTERVAL 5 DAY PRECEDING 5 FOLLOWING INTERVAL '2:30' MINUTE_SECOND FOLLOWING- 1
- 2
- 3
- 4
-
expr FOLLOWING:ROWS, 边界是当前行的后expr行.RANGE, 边界是 值等于 当前行的值加上expr的行；如果当前行的值是NULL, 边界是该行的对等行。
在缺少frame子句的情况下，默认frame取决于ORDER BY子句是否存在：
- 有
ORDER BY的情况：默认frame包括从分区起始行到当前行的行，包括当前行的所有对等行(根据ORDER BY子句确定的值等于当前行的行)。默认情况相当于下面这个frame定义：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW- 1
- 没有
ORDER BY的情况：默认frame包括所有分区行(因为没有ORDER BY，所有分区行都是对等的)。默认情况相当于下面这个frame定义：
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING- 1
由于默认frame的不同取决于
ORDER BY的存在与否，因此向查询中添加ORDER BY以获得确定性结果可能会更改原来的结果。(例如，SUM()生成的值可能会改变)。要获得相同的结果，但按ORDER BY排序，可以提供一个显式的frame定义，无论ORDER BY是否存在，都将使用该定义。当当前行值为NULL时，frame定义的意义可能不显著。下面这些例子说明了各种frame 定义是如何应用的：
-- frame 从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。 ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING -- frame 从NULL值开始并在分区end停止。因为ASC排序，NULL作为第一个，因此frame是整个分区。 ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING -- frame 从NULL值开始并在分区end停止。因为DESC排序，NULL作为最后一个，因此只包含值为NULL的行。 ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING -- frame 从NULL值开始并在分区end停止。因为ASC排序，NULL作为第一个，因此frame是整个分区。 ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING -- frame 从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。 ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING -- frame 从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。 ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING -- frame 从第一行开始并NULL停止。因为ASC排序，NULL作为第一个，因此只包含值为NULL的行。 ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
示例
聚合函数
select a,b,count(*) as c , count(*) over() as total ,sum(f) as s , sum(f) over( ) as s_1 , sum(f) over( PARTITION by a ) as s_2 from t_window group by a ,b- 1
- 2
- 3
- 4
- 5
- 6
- 7
# c，是按分组进行count # 这个语句中 total，s_1，s_2 不是我们想要的结果。这个相当于先执行 # select a,b,f from t_window group by a,b # 再在结果集上执行 over。sum不再是聚合函数，而是窗口函数了。 # total ：统计的是group 的行数。 a1 b1 40 4 120 4 2 a1 b2 40 4 120 4 2 a2 b1 40 4 120 4 2 a2 b2 40 4 120 4 2 # 等同于 select a,b,f from t_window group by a,b # output： # f 列随机的。 a1 b1 1 a1 b2 1 a2 b1 1 a2 b2 1 select a,b , count(*) over() as total , sum(f) over( ) as s_1 , sum(f) over( PARTITION by a ) as s_2 from ( select a,b,f from t_window group by a,b ) t # output： #结果是匹配的 a1 b1 4 4 2 a1 b2 4 4 2 a2 b1 4 4 2 a2 b2 4 4 2- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
排序函数
select a,b,c, COUNT(*) AS cnt ,row_number() over() as r1 ,row_number() over(partition by a order by b ) as r2 ,rank() over() as rk1 ,rank() over(partition by a order by d ) as rk2 ,rank() over(partition by a order by b ) as rk3 ,dense_rank() over() as dr1 ,dense_rank() over(partition by a order by d ) as dr2 ,dense_rank() over(partition by a order by b ) as dr3 from t_window group by a ,b,c -- OUTPUT # r1 是在所有记录中进行排序，不重复 # r2 是在 a 的分组类排序，a 分组有1,2,3,4 。b分组也是1,2,3,4 # rk1 是在所有记录中排序，由于未指定分组及排序字段，所以全部都为1 # rk2 是在分组中排序，由于指定排序字段不存在，所以全部都为1 # rk3 是在分组中排序，按b字段排序，b1的都为1，b2的都为3。跳过了2 # dr1 是在所有记录中排序，由于未指定分组及排序字段，所以全部都为1 # dr2 是在分组中排序，由于指定排序字段不存在，所以全部都为1 # dr3 是在分组中排序，按b字段排序，b1的都为1，b2的都为2。没有间隔。 a b c cnt r1 r2 rk1 rk2 rk3 dr1 dr2 dr3 a1 b1 c1 20 1 1 1 1 1 1 1 1 a1 b1 c2 20 2 2 1 1 1 1 1 1 a1 b2 c1 20 3 3 1 1 3 1 1 2 a1 b2 c2 20 4 4 1 1 3 1 1 2 a2 b1 c1 20 5 1 1 1 1 1 1 1 a2 b1 c2 20 6 2 1 1 1 1 1 1 a2 b2 c1 20 7 3 1 1 3 1 1 2 a2 b2 c2 20 8 4 1 1 3 1 1 2- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
前后函数
select a,b,c,f ,lead(f) over(partition by a,b,c order by f) as f_next ,lag(f) over(partition by a,b,c order by f) as f_before from t_window where a = 'a1' and b = 'b1' and d = 1 -- OUTPUT： -- 窗口函数 按照 a，b，c 分区，然后 f 排序。 a b c f f_next f_before a1 b1 c1 1 1 null #a1，b1，c1 这个分组内，第1条记录前面没有记录了，所以为null。 a1 b1 c1 1 2 1 a1 b1 c1 2 2 1 a1 b1 c1 2 3 2 a1 b1 c1 3 3 2 a1 b1 c1 3 4 3 a1 b1 c1 4 4 3 a1 b1 c1 4 5 4 a1 b1 c1 5 5 4 a1 b1 c1 5 null 5 #a1，b1，c1 这个分组内，f 的后面没有记录了，所以为 null。 a1 b1 c2 1 1 null #a1，b1，c2 这个分组内，第1条记录前面没有记录了，所以为null。 a1 b1 c2 1 2 1 a1 b1 c2 2 2 1 a1 b1 c2 2 3 2 a1 b1 c2 3 3 2 a1 b1 c2 3 4 3 a1 b1 c2 4 4 3 a1 b1 c2 4 5 4 a1 b1 c2 5 5 4 a1 b1 c2 5 null 5 #a1，b1，c2 这个分组内，f 的后面没有记录了，所以为 null。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
首尾函数
select a,b,c,f ,first_value(f) over(partition by a,b,c ) as f_first0 ,last_value (f) over(partition by a,b,c ) as f_last0 ,first_value(f) over(partition by a,b,c order by f) as f_first1 ,last_value (f) over(partition by a,b,c order by f) as f_last1 ,first_value(f) over(partition by a,b order by f) as f_first2 ,last_value (f) over(partition by a,b order by f) as f_last2 from t_window where a = 'a1' and b = 'b1' and d = 1 order by a,b,c -- output：按a，b，c 排序了 -- over中不带有order by 语句的last_value ，为整个分区的最后一行，由于没有排序，所以最后一样是 1，就都是1了。f_last0 -- over中带有order by 语句的last_value ，值等于当前行的值，因为 frame的end即为当前行。f_last1，f_last2 a b c f f_first0 f_last0 f_first1 f_last1 f_first2 f_last2 a1 b1 c1 1 2 1 1 1 1 1 a1 b1 c1 2 2 1 1 2 1 2 a1 b1 c1 5 2 1 1 5 1 5 a1 b1 c1 5 2 1 1 5 1 5 a1 b1 c1 2 2 1 1 2 1 2 a1 b1 c1 3 2 1 1 3 1 3 a1 b1 c1 1 2 1 1 1 1 1 a1 b1 c1 3 2 1 1 3 1 3 a1 b1 c1 4 2 1 1 4 1 4 a1 b1 c1 4 2 1 1 4 1 4 a1 b1 c2 3 5 1 1 3 1 3 a1 b1 c2 5 5 1 1 5 1 5 a1 b1 c2 5 5 1 1 5 1 5 a1 b1 c2 4 5 1 1 4 1 4 a1 b1 c2 4 5 1 1 4 1 4 a1 b1 c2 3 5 1 1 3 1 3 a1 b1 c2 2 5 1 1 2 1 2 a1 b1 c2 2 5 1 1 2 1 2 a1 b1 c2 1 5 1 1 1 1 1 a1 b1 c2 1 5 1 1 1 1 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
上述结果，first_value()的结果基本上都是1，但是last_value()的结果很错乱。语法没有错误，逻辑也没有错误，这种理想偏差来自last_value函数的默认语法，因为在开窗函数over()中除了分组和排序，还有一个窗口的从句，在经过排序之后，使用last_value函数生效的范围是第一行至当前行，所以last_value的值都等于f 字段的值。
其他函数
select a,b,c,f ,NTH_VALUE(f,3) over(partition by a,b,c ORDER BY F ) as f_0 ,NTH_VALUE(f,3) over(partition by a,b ORDER BY F ) as f_1 from t_window where a = 'a1' and b = 'b1' and d = 1 order by a,b,c,F a b c f f_0 f_1 a1 b1 c1 1 NULL 1 a1 b1 c1 1 NULL 1 a1 b1 c1 2 2 1 a1 b1 c1 2 2 1 a1 b1 c1 3 2 1 a1 b1 c1 3 2 1 a1 b1 c1 4 2 1 a1 b1 c1 4 2 1 a1 b1 c1 5 2 1 a1 b1 c1 5 2 1 a1 b1 c2 1 NULL 1 a1 b1 c2 1 NULL 1 a1 b1 c2 2 2 1 a1 b1 c2 2 2 1 a1 b1 c2 3 2 1 a1 b1 c2 3 2 1 a1 b1 c2 4 2 1 a1 b1 c2 4 2 1 a1 b1 c2 5 2 1 a1 b1 c2 5 2 1- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
Performance Schema Functions(since 8.0)
附录
窗口函数、聚合函数的测试数据
CREATE TABLE `t_window` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `a` varchar(10) COLLATE utf8mb4_general_ci NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci NOT NULL, `c` varchar(10) COLLATE utf8mb4_general_ci NOT NULL, `d` int DEFAULT NULL, `e` int DEFAULT NULL, `f` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO `t_window`(`a`, `b`, `c`, `d`, `e`, `f`) VALUES ( 'a1', 'b1', 'c1', 1, 1, 1) ,( 'a1', 'b1', 'c1', 1, 1, 2) ,( 'a1', 'b1', 'c1', 1, 1, 3) ,( 'a1', 'b1', 'c1', 1, 1, 4) ,( 'a1', 'b1', 'c1', 1, 1, 5) ,( 'a1', 'b1', 'c1', 1, 2, 1) ,( 'a1', 'b1', 'c1', 1, 2, 2) ,( 'a1', 'b1', 'c1', 1, 2, 3) ,( 'a1', 'b1', 'c1', 1, 2, 4) ,( 'a1', 'b1', 'c1', 1, 2, 5) ,( 'a1', 'b1', 'c1', 2, 1, 1) ,( 'a1', 'b1', 'c1', 2, 1, 2) ,( 'a1', 'b1', 'c1', 2, 1, 3) ,( 'a1', 'b1', 'c1', 2, 1, 4) ,( 'a1', 'b1', 'c1', 2, 1, 5) ,( 'a1', 'b1', 'c1', 2, 2, 1) ,( 'a1', 'b1', 'c1', 2, 2, 2) ,( 'a1', 'b1', 'c1', 2, 2, 3) ,( 'a1', 'b1', 'c1', 2, 2, 4) ,( 'a1', 'b1', 'c1', 2, 2, 5) ,( 'a1', 'b1', 'c2', 1, 1, 1) ,( 'a1', 'b1', 'c2', 1, 1, 2) ,( 'a1', 'b1', 'c2', 1, 1, 3) ,( 'a1', 'b1', 'c2', 1, 1, 4) ,( 'a1', 'b1', 'c2', 1, 1, 5) ,( 'a1', 'b1', 'c2', 1, 2, 1) ,( 'a1', 'b1', 'c2', 1, 2, 2) ,( 'a1', 'b1', 'c2', 1, 2, 3) ,( 'a1', 'b1', 'c2', 1, 2, 4) ,( 'a1', 'b1', 'c2', 1, 2, 5) ,( 'a1', 'b1', 'c2', 2, 1, 1) ,( 'a1', 'b1', 'c2', 2, 1, 2) ,( 'a1', 'b1', 'c2', 2, 1, 3) ,( 'a1', 'b1', 'c2', 2, 1, 4) ,( 'a1', 'b1', 'c2', 2, 1, 5) ,( 'a1', 'b1', 'c2', 2, 2, 1) ,( 'a1', 'b1', 'c2', 2, 2, 2) ,( 'a1', 'b1', 'c2', 2, 2, 3) ,( 'a1', 'b1', 'c2', 2, 2, 4) ,( 'a1', 'b1', 'c2', 2, 2, 5) ,( 'a1', 'b2', 'c1', 1, 1, 1) ,( 'a1', 'b2', 'c1', 1, 1, 2) ,( 'a1', 'b2', 'c1', 1, 1, 3) ,( 'a1', 'b2', 'c1', 1, 1, 4) ,( 'a1', 'b2', 'c1', 1, 1, 5) ,( 'a1', 'b2', 'c1', 1, 2, 1) ,( 'a1', 'b2', 'c1', 1, 2, 2) ,( 'a1', 'b2', 'c1', 1, 2, 3) ,( 'a1', 'b2', 'c1', 1, 2, 4) ,( 'a1', 'b2', 'c1', 1, 2, 5) ,( 'a1', 'b2', 'c1', 2, 1, 1) ,( 'a1', 'b2', 'c1', 2, 1, 2) ,( 'a1', 'b2', 'c1', 2, 1, 3) ,( 'a1', 'b2', 'c1', 2, 1, 4) ,( 'a1', 'b2', 'c1', 2, 1, 5) ,( 'a1', 'b2', 'c1', 2, 2, 1) ,( 'a1', 'b2', 'c1', 2, 2, 2) ,( 'a1', 'b2', 'c1', 2, 2, 3) ,( 'a1', 'b2', 'c1', 2, 2, 4) ,( 'a1', 'b2', 'c1', 2, 2, 5) ,( 'a1', 'b2', 'c2', 1, 1, 1) ,( 'a1', 'b2', 'c2', 1, 1, 2) ,( 'a1', 'b2', 'c2', 1, 1, 3) ,( 'a1', 'b2', 'c2', 1, 1, 4) ,( 'a1', 'b2', 'c2', 1, 1, 5) ,( 'a1', 'b2', 'c2', 1, 2, 1) ,( 'a1', 'b2', 'c2', 1, 2, 2) ,( 'a1', 'b2', 'c2', 1, 2, 3) ,( 'a1', 'b2', 'c2', 1, 2, 4) ,( 'a1', 'b2', 'c2', 1, 2, 5) ,( 'a1', 'b2', 'c2', 2, 1, 1) ,( 'a1', 'b2', 'c2', 2, 1, 2) ,( 'a1', 'b2', 'c2', 2, 1, 3) ,( 'a1', 'b2', 'c2', 2, 1, 4) ,( 'a1', 'b2', 'c2', 2, 1, 5) ,( 'a1', 'b2', 'c2', 2, 2, 1) ,( 'a1', 'b2', 'c2', 2, 2, 2) ,( 'a1', 'b2', 'c2', 2, 2, 3) ,( 'a1', 'b2', 'c2', 2, 2, 4) ,( 'a1', 'b2', 'c2', 2, 2, 5) ,( 'a2', 'b1', 'c1', 1, 1, 1) ,( 'a2', 'b1', 'c1', 1, 1, 2) ,( 'a2', 'b1', 'c1', 1, 1, 3) ,( 'a2', 'b1', 'c1', 1, 1, 4) ,( 'a2', 'b1', 'c1', 1, 1, 5) ,( 'a2', 'b1', 'c1', 1, 2, 1) ,( 'a2', 'b1', 'c1', 1, 2, 2) ,( 'a2', 'b1', 'c1', 1, 2, 3) ,( 'a2', 'b1', 'c1', 1, 2, 4) ,( 'a2', 'b1', 'c1', 1, 2, 5) ,( 'a2', 'b1', 'c1', 2, 1, 1) ,( 'a2', 'b1', 'c1', 2, 1, 2) ,( 'a2', 'b1', 'c1', 2, 1, 3) ,( 'a2', 'b1', 'c1', 2, 1, 4) ,( 'a2', 'b1', 'c1', 2, 1, 5) ,( 'a2', 'b1', 'c1', 2, 2, 1) ,( 'a2', 'b1', 'c1', 2, 2, 2) ,( 'a2', 'b1', 'c1', 2, 2, 3) ,( 'a2', 'b1', 'c1', 2, 2, 4) ,( 'a2', 'b1', 'c1', 2, 2, 5) ,( 'a2', 'b1', 'c2', 1, 1, 1) ,( 'a2', 'b1', 'c2', 1, 1, 2) ,( 'a2', 'b1', 'c2', 1, 1, 3) ,( 'a2', 'b1', 'c2', 1, 1, 4) ,( 'a2', 'b1', 'c2', 1, 1, 5) ,( 'a2', 'b1', 'c2', 1, 2, 1) ,( 'a2', 'b1', 'c2', 1, 2, 2) ,( 'a2', 'b1', 'c2', 1, 2, 3) ,( 'a2', 'b1', 'c2', 1, 2, 4) ,( 'a2', 'b1', 'c2', 1, 2, 5) ,( 'a2', 'b1', 'c2', 2, 1, 1) ,( 'a2', 'b1', 'c2', 2, 1, 2) ,( 'a2', 'b1', 'c2', 2, 1, 3) ,( 'a2', 'b1', 'c2', 2, 1, 4) ,( 'a2', 'b1', 'c2', 2, 1, 5) ,( 'a2', 'b1', 'c2', 2, 2, 1) ,( 'a2', 'b1', 'c2', 2, 2, 2) ,( 'a2', 'b1', 'c2', 2, 2, 3) ,( 'a2', 'b1', 'c2', 2, 2, 4) ,( 'a2', 'b1', 'c2', 2, 2, 5) ,( 'a2', 'b2', 'c1', 1, 1, 1) ,( 'a2', 'b2', 'c1', 1, 1, 2) ,( 'a2', 'b2', 'c1', 1, 1, 3) ,( 'a2', 'b2', 'c1', 1, 1, 4) ,( 'a2', 'b2', 'c1', 1, 1, 5) ,( 'a2', 'b2', 'c1', 1, 2, 1) ,( 'a2', 'b2', 'c1', 1, 2, 2) ,( 'a2', 'b2', 'c1', 1, 2, 3) ,( 'a2', 'b2', 'c1', 1, 2, 4) ,( 'a2', 'b2', 'c1', 1, 2, 5) ,( 'a2', 'b2', 'c1', 2, 1, 1) ,( 'a2', 'b2', 'c1', 2, 1, 2) ,( 'a2', 'b2', 'c1', 2, 1, 3) ,( 'a2', 'b2', 'c1', 2, 1, 4) ,( 'a2', 'b2', 'c1', 2, 1, 5) ,( 'a2', 'b2', 'c1', 2, 2, 1) ,( 'a2', 'b2', 'c1', 2, 2, 2) ,( 'a2', 'b2', 'c1', 2, 2, 3) ,( 'a2', 'b2', 'c1', 2, 2, 4) ,( 'a2', 'b2', 'c1', 2, 2, 5) ,( 'a2', 'b2', 'c2', 1, 1, 1) ,( 'a2', 'b2', 'c2', 1, 1, 2) ,( 'a2', 'b2', 'c2', 1, 1, 3) ,( 'a2', 'b2', 'c2', 1, 1, 4) ,( 'a2', 'b2', 'c2', 1, 1, 5) ,( 'a2', 'b2', 'c2', 1, 2, 1) ,( 'a2', 'b2', 'c2', 1, 2, 2) ,( 'a2', 'b2', 'c2', 1, 2, 3) ,( 'a2', 'b2', 'c2', 1, 2, 4) ,( 'a2', 'b2', 'c2', 1, 2, 5) ,( 'a2', 'b2', 'c2', 2, 1, 1) ,( 'a2', 'b2', 'c2', 2, 1, 2) ,( 'a2', 'b2', 'c2', 2, 1, 3) ,( 'a2', 'b2', 'c2', 2, 1, 4) ,( 'a2', 'b2', 'c2', 2, 1, 5) ,( 'a2', 'b2', 'c2', 2, 2, 1) ,( 'a2', 'b2', 'c2', 2, 2, 2) ,( 'a2', 'b2', 'c2', 2, 2, 3) ,( 'a2', 'b2', 'c2', 2, 2, 4) ,( 'a2', 'b2', 'c2', 2, 2, 5)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
Json的Partial Updates
在 MySQL 5.7 中，对 JSON 文档进行更新，其处理策略是，删除旧的文档，再插入新的文档。即使这个修改很微小，只涉及几个字节，也会替换掉整个文档。很显然，这种处理方式的效率较为低下。
在 MySQL 8.0 中，针对 JSON 文档，引入了一项新的特性-Partial Updates（部分更新），支持 JSON 文档的原地更新。得益于这个特性，JSON 文档的处理性能得到了极大提升。
使用 Partial Updates 需满足以下条件：
-
被更新的列是 JSON 类型。
-
使用 JSON_SET，JSON_REPLACE，JSON_REMOVE 进行 UPDATE 操作，如，
update t set c1=json_remove(c1,'$.id') where id=1; 不使用这三个函数，而显式赋值，就不会进行部分更新，如， update t set c1='{"id": 1, "name": "a"}' where id=1;- 1
- 2
- 3
- 输入列和目标列必须是同一列
update t set c1=json_replace(c1,'$.id',10) where id=1; 否则的话，就不会进行部分更新，如， update t set c1=json_replace(c2,'$.id',10) where id=1;- 1
- 2
- 3
- 变更前后，JSON 文档的空间使用不会增加。
如何在 binlog 中开启 Partial Updates
Partial Updates 不仅仅适用于存储引擎层，还可用于主从复制场景。
主从复制开启 Partial Updates，只需将参数
binlog_row_value_options（默认为空）设置为PARTIAL_JSON。对比 binlog 的内容，不开启，无论是修改前的镜像（before_image）还是修改后的镜像（after_image），记录的都是完整文档。而开启后，对于修改后的镜像，记录的是命令，而不是完整文档，这样可节省近一半的空间。
在将
binlog_row_value_options设置为PARTIAL_JSON后，对于可使用 Partial Updates 的操作，在 binlog 中，不再通过 ROWS_EVENT 来记录，而是新增了一个 PARTIAL_UPDATE_ROWS_EVENT 的事件类型。需要注意的是，binlog 中使用 Partial Updates，只需满足存储引擎层使用 Partial Updates 的前三个条件，无需考虑变更前后，JSON 文档的空间使用是否会增加。
以 MySQL 5.7.36 的查询时间作为基准：
- MySQL 8.0 只开启存储引擎层的 Partial Updates，查询时间比 MySQL 5.7 快 1.94 倍。
- MySQL 8.0 同时开启存储引擎层和 binlog 中的 Partial Updates，查询时间比 MySQL 5.7 快 4.87 倍。
- 如果在 2 的基础上，同时将 binlog_row_image 设置为 MINIMAL，查询时间更是比 MySQL 5.7 快 102.22 倍。
当然，在生产环境，我们一般很少将 binlog_row_image 设置为 MINIMAL。
但即使如此，只开启存储引擎层和 binlog 中的 Partial Updates，查询时间也比 MySQL 5.7 快 4.87 倍，性能提升还是比较明显的
-
相关阅读:
[附源码]计算机毕业设计JAVA小型医院药品及门诊管理
Debian 搭建 WireGuard 服务端
jeecg-boot集成xxl-job调度平台，每秒/每分钟/手动都能执行成功，但是设置固定时间不触发？
308场周赛 leetcode xp_xht123
pg 字符相关操作
前端技能树，面试复习第 26 天—— React Hook 的实现原理 | useState | 生命周期
Android AOP二三事：使用APT仿写ButterKnife
PHP+防止SQL注入的网上二手交易平台 毕业设计-附源码241552
【无标题】
数据通信——传输层TCP（超时时间选择）
- 原文地址：https://blog.csdn.net/demon7552003/article/details/127464854
