MySql特殊类型

mysql 8.0以上

1.获取Mysql中的json内容

SELECT 
  SUM(
    a.`extraData` -> "$.commission"
  ) AS commission,
  a.`type`,a.`ID`
FROMtable  a 
WHERE a.`xxx` = 'YES'
GROUP BY a.`type`;

即调用时为

SELECT column->"$.field" FROM table

2.更新json

UPDATE 
  tableSET
  jsonData = JSON_SET(jsonData, "$.field1", "123") 
WHERE a = 1AND jsonData IS NOT NULL 

JSON_SET提供同时更新多个的参数

JSON_SET(jsonData, "$.field1", "123","$.field2","xxx","$.field3","3434") 

3.以json为条件

SELECT 
    *
FROM 
    table
WHERE
    jsonData->'$.field' = '123'

4.操作JSON内容

https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-array-insert

复制代码

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"]      |
+----------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"]         |
+-------------------------------------+

mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
+------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+

mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z"]                 |
+---------------------------------+

JSON_ARRAY_INSERT

JSON_INSERT、JSON_REPLACE、JSON_SET分别代表，只增，只替换，增+替换