Shulou(Shulou.com)06/01 Report--

Editor to share with you what MySQL JSON data types, I believe most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL 5.7adds support for JSON data types. Previously, if we want to store data of JSON type, we can only do JSON.stringify () and JSON.parse () operations by ourselves, and there is no way to query the data in JSON. All operations must be read out after parse, which is very troublesome. Once the native JSON data types are supported, we can directly query and modify JSON data, which is much more convenient than before.

To facilitate the demonstration, I first create a user table, where the info field is used to store the user's basic information. To define a field as data of type JSON, it is very simple to simply use the field name followed by JSON.

CREATE TABLE user (id INT (11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30) NOT NULL, info JSON); copy code

After the table is successfully created, we will follow the classic CRUD data operation to show how to operate on the JSON data type.

Add data

It's easy to add data, but you need to understand that MySQL's storage of JSON is essentially a string storage operation. It's just that when it is defined as a JSON type, some indexes of the data will be created internally to facilitate subsequent operations. So you need to use string wrapping when adding JSON data.

Mysql > INSERT INTO user (`name`, `info`) VALUES ('lilei',' {"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85,90,100]}'); Query OK, 1 row affected (0.00 sec) copy code

In addition to spelling JSON yourself, you can also call MySQL's JSON creation function to create it.

JSON_OBJECT: quickly create JSON objects, odd column key, even column value, using the method JSON_OBJECT (key,value,key1,value1) JSON_ARRAY: quickly create an JSON array Use the method JSON_ARRAY (item0, item1, item2) mysql > INSERT INTO user (`name`, `info`) VALUES ('hanmeimei', JSON_OBJECT (- >' sex', 'female',->' age', 18,-> 'hobby', JSON_ARRAY (' badminton', 'sing'),->' score', JSON_ARRAY (90,95,100)->) Query OK, 1 row affected (0.00 sec) copy code

However, it is very troublesome for JavaScript engineers to use strings to write or to use native functions to create JSON, which is far less useful than JS native objects. Therefore, in the think-model module, we have added the support for automatic JSON.stringify () for data of JSON data type, so we can just pass in JS object data directly.

Because the automatic serialization and parsing of data is based on the field type, in order not to affect the running project, you need to configure jsonFormat: true in the module to turn on this feature.

/ / adapter.jsconst MySQL = require ('think-model-mysql'); exports.model = {type:' mysql', mysql: {handle: MySQL,... JsonFormat: true}}; copy code / / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userId = await this.model ('user'). Add ({name:' lilei', info: {sex: 'male', age: 16, hobby: [' basketball', 'football'], score: [85,90,100]}}) Return this.success (userId);}} copy the code

Let's take a look at what the final data stored in the database looks like.

Mysql > SELECT * FROM `user` +-+ | id | name | info | | +-+-- -+ | 1 | lilei | {"age": 18 "sex": "male", "hobby": ["basketball", "football"], "score": [85,90,100]} | | 2 | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90,95 100]} | +-+ 2 rows in set (0.00 sec) copy code to query data

In order to better support the operation of JSON data, MySQL provides some methods of JSON data manipulation class. The main methods related to query operations are as follows:

JSON_EXTRACT (): get part of the JSON data according to Path, using the method JSON_EXTRACT (json_doc, path [, path]...)->: the equivalent of JSON_EXTRACT ()->: the equivalent of JSON_EXTRACT () and JSON_UNQUOTE (): query whether the JSON data contains the specified data in the specified Path. If it is included, return 1, otherwise return 0. Use the method JSON_CONTAINS (json_doc, val [, path]) JSON_CONTAINS_PATH (): query whether the specified path exists, return 1 if it exists, 0 otherwise. One_or_all can only have values of "one" or "all". One means that as long as one exists, and all means that all exist. Use the method JSON_CONTAINS_PATH (json_doc, one_or_all, path [, path]...) JSON_KEYS (): get all the key values of the JSON data under the specified path. Use the method JSON_KEYS (json_doc [, path]), similar to the Object.keys () method in JavaScript. JSON_SEARCH (): the query contains the Paths of the specified string and is returned as a JSON Array. The string of the query can be matched with'%'or'_'in LIKE. Use the method JSON_SEARCH (json_doc, one_or_all, search_str [, escape_char [, path]...]), similar to the findIndex () operation in JavaScript.

Here we do not describe each method one by one, but only propose some scenario examples how to operate.

Returns the age and sex of the user

The purpose of this example is to tell you how to get part of the JSON data and return it according to the normal table field. This can be done using JSON_EXTRACT or the equivalent-> operation. According to the example, you can see that the data returned by sex is in quotation marks. At this time, you can use JSON_UNQUOTE () or directly use-> > to remove the quotation marks.

Mysql > SELECT `name`, JSON_EXTRACT (`info`,'$.age') as `age`, `info`->'$.sex'as sex FROM `user` +-+ | name | age | sex | +-+ | lilei | 18 | "male" | | hanmeimei | 16 | "female" | +- -+ 2 rows in set (0.00 sec) copy code

Here we first come across the writing of Path, and MySQL uses the Path description of this string to help us map to the corresponding data. Similar to the operation of objects in JavaScript, through. Get the attributes of the next level, and get the array elements through [].

The difference is that it needs to be represented by $, which is also easier to understand. In addition, you can use * and * wildcards, such as. * represents the values of all members of the current level, and [*] represents the values of all members of the current array. * * like LIKE, you can connect prefixes and suffixes, for example, a path that begins with an and ends with b.

The way to write the path is very simple, and it will also appear in the following content. The query above is written in think-model as

/ / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userModel = this.model ('user'); const field = "name, JSON_EXTRACT (info,' $.age') AS age, info- >'$.sex'as sex"; const users = await userModel.field (field) .where ('1cm 1'). Select (); return this.success (users) }} copy code returns mysql > SELECT `name` FROM `user` WHERE JSON_CONTAINS (`info`,'"male"','$.sex') AND JSON_SEARCH (`info`, 'one',' basketball', null,'$hobby'); +-+ | name | +-+ | lilei | +-+ 1 row in set, 1 warning (0.00 sec) copy code

This example is simply to show you how to query and search attributes and arrays. It is important to note that the JSON_CONTAINS () query string needs to be wrapped with "" due to the problem string without type conversion, or JSON_QUOTE ('male') can be used.

If you are using MySQL 8, you can also use the new JSON_VALUE () instead of JSON_CONTAINS (). The advantage of the new method is that it will have a type conversion to avoid the embarrassment of double quotation marks. If you don't need a return path, JSON_SEARCH () can also be replaced here with the new MEMBER OF or JSON_OVERLAPS () method.

Mysql > SELECT `name` FROM `user` WHERE JSON_VALUE (`info`,'$.sex') = 'male' AND' basketball' MEMBER OF (JSON_VALUE (`info`,'$.hobby')) +-+ | name | +-+ | lilei | +-+ 1 row in set (0.00 sec) mysql > SELECT `name` FROM `user` WHERE JSON_VALUE (`info`,'$.sex') = 'male' AND JSON_OVERLAPS (JSON_VALUE (`info`,' $.hobby'), JSON_QUOTE ('basketball')); +-+ | name | +-+ | lilei | +-+ 1 row in set (0.00 sec) copy code

The query above is written in think-model as

/ / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userModel = this.model ('user'); const where = {_ string: ["JSON_CONTAINS (info,'\" male\ ",'$.sex')", "JSON_SEARCH (info, 'one',' basketball', null,'$.hobby')"]} Const where1 = {_ string: ["JSON_VALUE (`info`,'$.sex') = 'male'", "' basketball' MEMBER OF (JSON_VALUE (`info`,'$.hobby')")]} Const where2 = {_ string: ["JSON_VALUE (`info`,'$.sex') = 'male'", "JSON_OVERLAPS (JSON_VALUE (`info`,' $.hobby'), JSON_QUOTE ('basketball')")} const users = await userModel.field (' info`) .where (where). Select (); return this.success (users);}} copy code modification data

Among the JSON operation functions provided by MySQL, the main methods related to modification operations are as follows:

JSON_APPEND/JSON_ARRAY_APPEND: these two names are two names for the same feature. MySQL 5.7was updated to JSON_ARRAY_APPEND for JSON_APPEND,MySQL 8, and the previous name was discarded. This method literally adds values to the array. Use the method JSON_ARRAY_APPEND (json_doc, path, val [, path, val]...) JSON_ARRAY_INSERT: add values to the array, unlike JSON_ARRAY_APPEND (), which can be interpolated at a specified location. Use method JSON_ARRAY_INSERT (json_doc, path, val [, path, val]...) JSON_INSERT/JSON_REPLACE/JSON_SET: all of the above three methods insert data into JSON, and they all use JSON_ [INSERT | REPLACE | SET] (json_doc, path, val [, path, val].), but there are some differences in insertion principles. JSON_INSERT: insert JSON_REPLACE when the path does not exist: replace JSON_SET when the path exists: JSON_REMOVE: remove the data of the specified path regardless of whether the path exists or not. Use the method JSON_REMOVE (json_doc, path [, path]...)

Because the JSON_INSERT, JSON_REPLACE, JSON_SET, and JSON_REMOVE methods support the manipulation of properties and arrays, the first two JSON_ARRAY methods use slightly less. Let's continue to give a few examples based on the previous data.

Modify the user's age mysql > UPDATE `user`user`info` = JSON_REPLACE (`info`,'$.age', 20) WHERE `name` = 'lilei';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT JSON_VALUE (`info`,' $.age') as age FROM `user`WHERE `name` = 'lilei';+-+ | age | +-+ | 20 | +-+ 1 row in set (0.00 sec) copy code

The examples of JSON_INSERT and JSON_SET are similar, so I won't do much demonstration here. If it corresponds to think-model, you need to use EXP conditional expression processing, which is written as follows

/ / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userModel = this.model ('user'); await userModel.where ({name:' lilei'}). Update ({info: ['exp', "JSON_REPLACE (info,' $.age', 20)"]}); return this.success () }} copy code modify user's hobby mysql > UPDATE `user`user`info` = JSON_ARRAY_APPEND (`info`,'$.hobby', 'badminton') WHERE `name` =' lilei';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT JSON_VALUE (`info`,'$.hobby') as hobby FROM `user`WHERE `name` = 'lilei' +-- + | hobby | +-- + | ["basketball", "football" "badminton"] | +-+ 1 row in set (0.00 sec) copy code

JSON_ARRAY_APPEND is more convenient than JSON_INSERT when manipulating arrays, at least you don't need to know the length of the array. The corresponding to think-model is written as

/ / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userModel = this.model ('user'); await userModel.where ({name:' lilei'}). Update ({info: ['exp', "JSON_ARRAY_APPEND (info,' $.hobby', 'badminton')"]}); return this.success () }} copy code to delete the user's score mysql > UPDATE `user`user`info` = JSON_REMOVE (`info`,'$.score [0]') WHERE `name` = 'lilei';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT `name`, JSON_VALUE (`info`,' $.score') as score FROM `user`WHERE `name` = 'lilei' +-+-+ | name | score | +-+-+ | lilei | [90,100] | +-+-+ 1 row in set (0.00 sec) copy code

Deleting this piece is similar to the previous modification, and there is not much to say. But many times we just want to delete the value when we operate on the array, but we don't know what the Path of this value is. At this point, you need to take advantage of the JSON_SEARCH () method mentioned earlier, which finds the path based on the value. For example, if we want to delete the badminton option in lilei's interest, we can write like this.

Mysql > UPDATE `user`user`info` = JSON_REMOVE (`info`, JSON_UNQUOTE (JSON_SEARCH (`info`, 'one',' badminton')) WHERE `name` = 'lilei';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT JSON_VALUE (`info`,' $.hobby') as hobby FROM `user`WHERE `name` = 'lilei' +-- + | hobby | +-+ | ["basketball", "football"] | +-- + 1 row in set (0.00 sec) copy code

It is important to note here that because JSON_SEARCH does not do type conversion, the matched path string needs to be JSON_UNQUOTE (). It is also very important that JSON_SEARCH cannot look up numeric type data, and it is not clear whether this is Bug or Feature. That's why instead of using score for examples, I switched to hobby. If the numeric type, it can only be taken out and processed in the code.

Mysql > SELECT JSON_VALUE (`info`,'$.score') FROM `user`WHERE `name` = 'lilei' +-- + | JSON_VALUE (`info`,'$.score') | +-+ | [90 ] | +-+ 1 row in set (0.00 sec) mysql > SELECT JSON_SEARCH (`info`, 'one', 90, null,' $.score') FROM `user`WHERE `name` = 'lilei' +-- + | JSON_SEARCH (`info`, 'one', 90, null) '$.score') | +-- + | NULL | +-- -+ 1 row in set (0.00 sec) copy code

The above corresponding to think-model is written as

/ / user.jsmodule.exports = class extends think.Controller {async indexAction () {const userModel = this.model ('user'); / / Delete the score await userModel.where ({name:' lilei'}). Update ({info: ['exp', "JSON_REMOVE (info,' $.score [0])"]}) / / delete await userModel.where ({name: 'lilei'}) .update ({info: [' exp', "JSON_REMOVE (`info`, JSON_UNQUOTE (JSON_SEARCH (`info`, 'one',' badminton'))]}); return this.success ();}} copy code postscript

Due to a recent requirement, there is a pile of data. It is necessary to record the sorting of this pile of data so that it is convenient to output according to the sort. In general, you must add an order field to each piece of data to record the sorting of that piece of data. However, because of the batch operation, it is particularly troublesome to use a single field to store at such a time. At the suggestion of my colleague on the server side, I solved this problem by using the JSON field to store the array.

Also because of this, we learned about MySQL's support for JSON, and made some optimizations to think-model to add support for JSON data types. Since most JSON operations need to be performed through built-in functions, this itself can be done through EXP conditional expressions. So you only need to optimize the addition and query of JSON data.

On the whole, with the JSON operation functions provided, there is no problem with MySQL's support for JSON to meet some of the daily requirements. In addition to being used as WHERE conditions and query fields, other ORDER, GROUP, JOIN and other operations also support JSON data.

However, compared with MongoDB, which naturally supports JSON, it is still much more troublesome in terms of operation. Especially in the type conversion piece, it is found to be very easy to fall after using it for a period of time. When to put quotation marks, when not to use quotation marks, when to need quotation marks, when not to need quotation marks, all these are easy to make beginners freak out. In addition, JSON_SEARCH () does not support digital search, which is also a big hole.

These are all the contents of the JSON data type of MySQL. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.