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

This article mainly explains "introduction to the usage of MYSQL JSON". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "introduction to the usage of MYSQL JSON".

Today, we are talking about MYSQL's method of dealing with JSON. If you do not want to use MONGODB, and the amount of data stored in JSON is not very large, MYSQL can also deal with such data. This function starts from MYSQL 5.7 and develops to a more advanced way, if it is OK as a useful supplement to traditional databases.

Old habit, let's make a list to begin with.

1 create a table that supports JSON and insert relevant data into MYSQL

From the above figure, it is very convenient for MYSQL to store data similar to JSON. Compared with the syntax of MONGODB, it is actually more intimate with the traditional DBA.

It is also convenient to query the simple JSON data from the above figure and perform related counting operations. Of course, if you are used to the operation of MONGODB, this way of writing may feel a little strange.

If the data of JSON is displayed in the way of MYSQL, some functions developed for JSON by MYSQL are needed. For example, the figure below in JSON_EXTRACT is the data in json1.

We use the relevant functions to query and format them as MYSQL display.

Of course, in fact, MYSQL's JSON also supports index query, although compared with MONGODB's index, it is not worth mentioning, but it is still mentioned.

Let's do the following experiment.

You can create a table and insert more than 100 pieces of test data.

We create a virtual column for the table test_features and bind the JSON column in the above table

Then add an index on the virtual column (there are two types of virtual columns, and we use one of them)

When we explain the query, we can see that the index has been removed in the execution plan.

Although MYSQL already supports data storage in JSON format, and also provides a large number of functions and simple indexes, there is a big gap compared with databases that only deal with JSON such as MONGODB. If you only store some simple JSON data and the amount is not very large, I feel that MYSQL can be used as a supplement, while if you want to query, aggregate, and have a large amount of JSON data, MONGODB is a good choice, but POSTGRESQL can also be used as an alternative.

Thank you for your reading, the above is the content of "introduction to the usage of MYSQL JSON". After the study of this article, I believe you have a deeper understanding of the introduction of the usage of MYSQL JSON, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.