With the wide application of NOSQL database, the extensible storage mode also has a good support in relational database. A new data type, JSON, has been added in MySQL 5.7
JSON data type meaning
In fact, without the support of JSON data type, we can save the data in this format by varchar type or text type, but why do we need to specifically add the support of this data format? There must be a better place to store this type than varchar or text.
- It ensures strong verification of JSON data type. JSON data column will automatically verify whether the content stored in this column conforms to JSON format, and error will be reported in abnormal format. However, there is no such mechanism for types such as varchar and text.
- MySQL also provides a set of built-in functions for manipulating JSON type data.
- More optimized storage format, JSON data stored in JSON column will be converted to internal specific storage format, allowing quick reading.
- You can modify specific key values based on the characteristics of JSON format. (that is, you don't need to take out the whole content and put it into the program to traverse, find a replacement and plug it back. MySQL's built-in functions allow you to do it through an SQL statement.)
1. New JSON type
CREATE TABLE `user_copy` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, `lastlogininfo` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET FOREIGN_KEY_CHECKS=1;
2. Create JSON value
mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array')); Query OK, 1 row affected (0.19 sec) mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello')); Query OK, 1 row affected (0.09 sec) mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2))); Query OK, 1 row affected (0.14 sec) mysql> SELECT * FROM t_json; +--------------------------------------+ | jdoc | +--------------------------------------+ | [1, 2] | | {"key1": "value1", "key2": "value2"} | | "HELLO" | | ["json_array"] | | {"key": "hello"} | | [{"key": "hello"}, 1, 2] | +--------------------------------------+ 6 rows in set (0.00 sec)
3. Search and modify JSON values
Path syntax
- . keyName: the value of the key name in the JSON object;
- For illegal key names (if there is a space), double quotes must be used in the path reference to "enclose the key names, for example." key name ";
- [index]: the index of JSON array is the value of index, and the index of JSON array also starts from 0;
- [index1 to index2]: the collection of values from index1 to index2 in JSON array;
- . *: all value s in the JSON object;
- [*]: all values in the JSON array;
- prefix**suffix: path starting with prefix and ending with suffix;
- **. keyName refers to multiple paths, such as' {"a": '{"B": 1}, "C":' {"B": 2} ',' $*. B 'refers to paths $. a.b and $. c.b;
- The return result of nonexistent path is NULL;
- The leading $character indicates the JSON document currently in use;
- Example: for array [3, {"a": [5, 6], "b": 10}, [99, 100]]
- $[1] is {"a": [5, 6], "b": 10};
- [1].a is [5, 6];
- $[1].a[1] is 6;
- $[1].b is 10;
- The $[2] [0] is 99.
3.1 search
- JSON object
mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name'); +--------------------------------------------------------+ | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') | +--------------------------------------------------------+ | "Taylor" | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*'); +-----------------------------------------------------+ | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') | +-----------------------------------------------------+ | [29, "Taylor"] | +-----------------------------------------------------+ 1 row in set (0.00 sec)
- JSON array
mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]'); +-----------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[1]') | +-----------------------------------------+ | "b" | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]'); +----------------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') | +----------------------------------------------+ | ["b", "c"] | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]'); +-----------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[*]') | +-----------------------------------------+ | ["a", "b", "c"] | +-----------------------------------------+ 1 row in set (0.00 sec)
3.2 modification
-
JSON_REPLACE and JSON_ Difference of set
// Old value exists mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere'); +----------------------------------------------------------------+ | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') | +----------------------------------------------------------------+ | {"id": 29, "name": "Mere"} | +----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere"); +------------------------------------------------------------+ | JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') | +------------------------------------------------------------+ | {"id": 29, "name": "Mere"} | +------------------------------------------------------------+ 1 row in set (0.00 sec) // Old value does not exist mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere'); +---------------------------------------------------------------+ | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') | +---------------------------------------------------------------+ | {"id": 29, "name": "Taylor"} | +---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere'); +-----------------------------------------------------------+ | JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') | +-----------------------------------------------------------+ | {"id": 29, "cat": "Mere", "name": "Taylor"} | +-----------------------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_INSERT and JSON_ Difference of set
// Old value exists mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4); +-------------------------------------+ | JSON_INSERT('[1, 2, 3]', '$[1]', 4) | +-------------------------------------+ | [1, 2, 3] | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[1]', 4) | +----------------------------------+ | [1, 4, 3] | +----------------------------------+ 1 row in set (0.00 sec) //Old value does not exist mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4); +-------------------------------------+ | JSON_INSERT('[1, 2, 3]', '$[4]', 4) | +-------------------------------------+ | [1, 2, 3, 4] | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[4]', 4) | +----------------------------------+ | [1, 2, 3, 4] | +----------------------------------+ 1 row in set (0.00 sec)