JSON
从mysql 5.7 开始，开始支持json类型。之前是存储为字符串。json数据类型，提供了自动验证的json文档和优化的格式存储。
JSON文档，以二进制存储，提供以下功能：
检索方式：
使用 ->>
例如：列名->>'$.address.city' 即可输出键值address下的city对应的值。
JSON函数：
优雅浏览：
JSON_PRETTY(列名)
查找：
可以使用上面的检索方式于 where语句中；
也可以使用JSON_CONTAINS ，找到返回1，否则返回0.
例如：检查address.city 是否存在：后面的city1可以写一个，也可以多写个其他列，比如city2
select json_contains_path(列名，'one',"$.address.city1","$.address.city2")
如果想确定，city1和city2 同时存在，将 ONE 改成 ALL;
修改：
mysql 8之前，需要整列更新。
json_set() 替换现有的和插入新的；
json_insert() 仅入值新值；
json_replace() 仅替换现有值；
注：如果json_insert() 的语句包含和原有值不一样的内容，是不会替换的，只增加新值。同样的，json_replace()函数一样，如果包含新值是不会增加的，只替换；
删除：
JSON_REMOVE（COL,"$.ADDRESS.CITY1"） 那city1的值就被删掉了。
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
12.17.1 JSON Function Reference
Table 12.21 JSON Functions
Name | Description |
-> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT(). |
->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). |
JSON_ARRAY() | Create JSON array |
JSON_ARRAY_APPEND() | Append data to JSON document |
JSON_ARRAY_INSERT() | Insert into JSON array |
JSON_CONTAINS() | Whether JSON document contains specific object at path |
JSON_CONTAINS_PATH() | Whether JSON document contains any data at path |
JSON_DEPTH() | Maximum depth of JSON document |
JSON_EXTRACT() | Return data from JSON document |
JSON_INSERT() | Insert data into JSON document |
JSON_KEYS() | Array of keys from JSON document |
JSON_LENGTH() | Number of elements in JSON document |
JSON_MERGE() (deprecated) | Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE() |
JSON_MERGE_PATCH() | Merge JSON documents, replacing values of duplicate keys |
JSON_MERGE_PRESERVE() | Merge JSON documents, preserving duplicate keys |
JSON_OBJECT() | Create JSON object |
JSON_OVERLAPS() (introduced 8.0.17) | Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0) |
JSON_PRETTY() | Print a JSON document in human-readable format |
JSON_QUOTE() | Quote JSON document |
JSON_REMOVE() | Remove data from JSON document |
JSON_REPLACE() | Replace values in JSON document |
JSON_SCHEMA_VALID() (introduced 8.0.17) | Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not |
JSON_SCHEMA_VALIDATION_REPORT() (introduced 8.0.17) | Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure |
JSON_SEARCH() | Path to value within JSON document |
JSON_SET() | Insert data into JSON document |
JSON_STORAGE_FREE() | Freed space within binary representation of JSON column value following partial update |
JSON_STORAGE_SIZE() | Space used for storage of binary representation of a JSON document |
JSON_TABLE() | Return data from a JSON expression as a relational table |
JSON_TYPE() | Type of JSON value |
JSON_UNQUOTE() | Unquote JSON value |
JSON_VALID() | Whether JSON value is valid |
JSON_VALUE() (introduced 8.0.21) | Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type |
MEMBER OF() (introduced 8.0.17) | Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0) |
MySQL supports two aggregate JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). See Section 12.20, “Aggregate (GROUP BY) Functions”, for descriptions of these.
MySQL also supports “pretty-printing” of JSON values in an easy-to-read format, using the JSON_PRETTY() function. You can see how much storage space a given JSON value takes up, and how much space remains for additional storage, using JSON_STORAGE_SIZE() and JSON_STORAGE_FREE(), respectively. For complete descriptions of these functions, see Section 12.17.8, “JSON Utility Functions”.
