text
MySQL started to support json natively in 5.7.8. This article will give a brief description of the usage of json types in mysql, hoping to be useful to you.
CREATE TABLE testproject ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `skill` JSON NOT NULL, `student` JSON NOT NULL, PRIMARY KEY (`id`) );
View table structure:
So the JSON fields are created.
**Note: * * JSON type cannot have default value.
Insert JSON
Insert a string in json format, either in the form of an object or an array.
INSERT INTO `testproject` (student, skill) VALUES ('{"id": 1, "name": "ggjg"}', '["java", "go", "vue"]'); INSERT INTO `testproject` (student, skill) VALUES ('{"id": 5, "name": "guogege"}', '[]');
When inserting json, the database will verify the json, and an error will be reported if it does not conform to the json specification.
Query JSON:
Query the data in json in the form of column - > path, where the object type path represents $. Path, while the array type is $[index]
Query the record with json object id 1 in the student field of testproject table:
SELECT * FROM testproject WHERE student->'$.id'= 1;
Query the record with json object id 1 or 5 in the student field of testproject table:
SELECT * FROM testproject WHERE student->'$.id' in (1,5); SELECT * FROM testproject WHERE student->'$.id' = 1 or student->'$.id' = 5;
You can also use the function JSON extract:
The column - > path method is limited. The data source must be a table field. Otherwise, an error will be reported.
Query as follows to find that student - > '$. Name' contains double quotes:
SELECT id, student->'$.id', student->'$.name', skill->'$[0]', skill->'$[2]' FROM testproject;
This is not what we want. We can use the JSON_UNQUOTE function to remove the double quotes. From MySQL 5.7.13, we can also use the operator - > > which is equivalent to JSON_UNQUOTE.
Because JSON is different from string, if you compare string with JSON field, it will not be equal:
mysql> SELECT * FROM testproject WHERE student = '{"id": 1, "name": "ggjg"}'; Empty set (0.00 sec)
At this time, string can be converted to JSON form through CAST:
mysql> SELECT * FROM testproject WHERE student = CAST('{"id": 1, "name": "ggjg"}' as JSON); +----+-----------------------+---------------------------+ | id | skill | student | +----+-----------------------+---------------------------+ | 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} | +----+-----------------------+---------------------------+ 1 row in set (0.01 sec)
It should be noted that element search in JSON strictly distinguishes variable types, such as integer and string.
mysql> SELECT * FROM testproject WHERE student->'$.id' = '1'; Empty set (0.00 sec) mysql> mysql> SELECT * FROM testproject WHERE student->'$.id' = 1; +----+-----------------------+---------------------------+ | id | skill | student | +----+-----------------------+---------------------------+ | 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} | +----+-----------------------+---------------------------+ 1 row in set (0.00 sec)
You can see that search string 1 and integer 1 have different results.
In addition to the above search in the form of column - > path, you can also use the json? Contains function. However, contrary to the form of column - > path, the second parameter of json? Contains does not accept integers, no matter whether the json element is an integer or a string, otherwise this error will occur:
mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, 1, '$.id'); ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required. mysql>
You have to use a string here:
mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, '1', '$.id'); +----+-----------------------+---------------------------+ | id | skill | student | +----+-----------------------+---------------------------+ | 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} | +----+-----------------------+---------------------------+ 1 row in set (0.00 sec)
For JSON queries of array type, for example, skill contains 3 data. The JSON_CONTAINS function is also used. The second parameter also needs to be a string:
mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '"go"'); +----+-----------------------+---------------------------+ | id | skill | student | +----+-----------------------+---------------------------+ | 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} | +----+-----------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '1'); +----+-----------+------------------------------+ | id | skill | student | +----+-----------+------------------------------+ | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+-----------+------------------------------+ 1 row in set (0.00 sec)
Update data
MySQL does not support the update operation in the form of column - > path.
If the whole json is updated, it is similar to the insert time:
mysql> select * from testproject where id = 10; +----+-----------------------+---------------------------+ | id | skill | student | +----+-----------------------+---------------------------+ | 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} | +----+-----------------------+---------------------------+ 1 row in set (0.00 sec) mysql> UPDATE testproject SET skill = '["js", "java"]' WHERE id = 10; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from testproject where id = 10; +----+----------------+---------------------------+ | id | skill | student | +----+----------------+---------------------------+ | 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} | +----+----------------+---------------------------+ 1 row in set (0.00 sec)
The following are used by the JSON? Array? Append and JSON? Array? Insert functions:
json? Array? Append is appended after json.
JSON? Array? Insert is inserted at the specified subscript.
mysql> select * from testproject; +----+----------------+------------------------------+ | id | skill | student | +----+----------------+------------------------------+ | 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT json_array_append(skill, '$', 'c') from testproject; +------------------------------------+ | json_array_append(skill, '$', 'c') | +------------------------------------+ | ["js", "java", "c"] | | ["c"] | | [1, 2, 3, "c"] | +------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT json_array_insert(skill, '$[1]', 'php') from testproject; +-----------------------------------------+ | json_array_insert(skill, '$[1]', 'php') | +-----------------------------------------+ | ["js", "php", "java"] | | ["php"] | | [1, "php", 2, 3] | +-----------------------------------------+ 3 rows in set (0.00 sec) mysql>
Use the following functions: JSON replace, JSON set, JSON insert, and JSON remove:
json_replace: only replace the existing old values, otherwise ignore;
JSON set: replace the old value and insert a new value that does not exist;
json_insert: inserts a new value, but does not replace the existing old value;
json_remove() deletes the element.
json_replace:
mysql> select * from testproject; +----+----------------+--------------------------------+ | id | skill | student | +----+----------------+--------------------------------+ | 10 | ["js", "java"] | {"id": 1, "name": "smallsoup"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+--------------------------------+ 3 rows in set (0.00 sec) mysql> mysql> UPDATE testproject SET student->'$.name' = 'smallsoup' where student->'$.id' = 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio n for the right syntax to use near '->'$.name' = 'smallsoup' where student->'$.id' = 1' at line 1 mysql> mysql> UPDATE testproject SET student = json_replace(student, '$.name', 'soup') WHERE student->'$.id' = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from testproject; +----+----------------+------------------------------+ | id | skill | student | +----+----------------+------------------------------+ | 10 | ["js", "java"] | {"id": 1, "name": "soup"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+------------------------------+ 3 rows in set (0.00 sec)
json_set:
mysql> select * from testproject; +----+----------------+------------------------------+ | id | skill | student | +----+----------------+------------------------------+ | 10 | ["js", "java"] | {"id": 1, "name": "soup"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+------------------------------+ 3 rows in set (0.00 sec) mysql> UPDATE testproject SET student = json_set(student, '$.name', 'small', '$.age', 22) WHERE student->'$.id'= 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from testproject; +----+----------------+---------------------------------------+ | id | skill | student | +----+----------------+---------------------------------------+ | 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+---------------------------------------+ 3 rows in set (0.00 sec)
json_insert:
mysql> select * from testproject; +----+----------------+---------------------------------------+ | id | skill | student | +----+----------------+---------------------------------------+ | 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+---------------------------------------+ 3 rows in set (0.00 sec) mysql> UPDATE testproject SET student = json_insert(student, '$.name', 'soup', '$.addr', 'Suzhou') WHERE student->'$.id'= 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from testproject; +----+----------------+---------------------------------------------------------+ | id | skill | student | +----+----------------+---------------------------------------------------------+ | 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "Suzhou", "name": "small"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+---------------------------------------------------------+ 3 rows in set (0.00 sec)
json_remove() :
mysql> select * from testproject; +----+----------------+---------------------------------------------------------+ | id | skill | student | +----+----------------+---------------------------------------------------------+ | 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "Suzhou", "name": "small"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+---------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> UPDATE testproject SET student = json_remove(student, '$.name', '$.age') WHERE student->'$.id' = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from testproject; +----+----------------+------------------------------+ | id | skill | student | +----+----------------+------------------------------+ | 10 | ["js", "java"] | {"id": 1, "addr": "Suzhou"} | | 11 | [] | {"id": 5, "name": "guogege"} | | 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} | +----+----------------+------------------------------+ 3 rows in set (0.00 sec)
You can see that name and age are removed.
The above only lists the descriptions of some functions. The official functions provided by mysql are as follows:
For more usage, please refer to the official document:
https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
This public account is free * * to provide csdn download service and massive it learning resources * * if you are ready to enter the IT pit and aspire to become an excellent program ape, these resources are suitable for you, including but not limited to java, go, python, springcloud, elk, embedded, big data, interview materials, front-end and other resources. At the same time, we have set up a technology exchange group. There are many big guys who will share technology articles from time to time. If you want to learn and improve together, you can reply [2] in the background of the public account. Free invitation plus technology exchange groups will learn from each other and share programming it related resources from time to time.
Scan the code to pay attention to the wonderful content and push it to you at the first time
