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

This article will explain in detail how to deal with JSON in MYSQL8. The content of the article is of high quality, so Xiaobian shares it with you as a reference. I hope you have a certain understanding of related knowledge after reading this article.

First we define why we want to use MYSQL 8 JSON format instead of text type.

1 When data is input, it can detect whether the data meets the JSON standard.

2 When processing data, query by key value pair, and process after reading the data in the field.

In a word, more standardized, faster, professional processing JSON, MYSQL OK

Why should a traditional database start focusing on unstructured, semi-structured data, because requirements, requirements determine everything, what is the format of information transmitted in different systems now, XML, plaintext, OMG, JSON JSON. If all traditional databases can't handle JSON, then it's likely that for some reason, some business scenarios won't need databases like MYSQL ,PG anymore and MONGODB will replace them. Fortunately PG is born JSON good, MYSQL 8 part of the ambition is also JSON, so MONGODB refueling ah.

Below is a table that accepts data from other companies.

MYSQL 5.7 itself also supports JSON, and I wrote an article before, but it was a bad review, too bad. MYSQL 8 should be above version 8.014 if it uses JSON, otherwise it will suffer in arrays. In MySQL 8.0, the optimizer can perform a partial in-place update of a JSON column instead of deleting the old document and writing the entire new document to the column, which is also conditional.

CREATE TABLE `t_clue_info` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fk_applyid` int(11) NOT NULL COMMENT '' requisition id'',

`context` json DEFAULT NULL COMMENT '' interface results',

`cdate` datetime NOT NULL COMMENT '' creation time',

PRIMARY KEY (`id`),

KEY `ix_fk_applyid` (`fk_applyid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982873,'{"apply_id":"cm-387237","info":"sim-093823","date":"2020-03-18 02:00:09"}',"2020-03-18 02-09-09");

Summary: 1 The inserted JSON field must be in JSON format

2 Use single quotes when inserting, which is the correct JSON format

Let's continue to increase the difficulty and let MYSQL start storing arrays.

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09","contant":"the informatiton is from car100"}',"2020-03-18 02-09-19");

insert into t_tmall_clue_info (fk_applyid,context,cdate) values (1982823,'{"apply_id":"cm-387234","info":"sim-093856","tag":[12,34,56],"date":"2020-03-11 09:00:09","contant":"the informatiton is from car100","add-in":"re-092878"}',"2020-03-18 02-09-19");

If you are careful, you will find that my input above and the display below, some interesting places, the display time and your input field order has little to do with it, he will automatically arrange some similar things neatly (as far as possible), I can't say whether this is good or... But it seems to be much better than using text, at least you can see which one is missing in JSON at a glance.

The query method is also getting more and more interesting.

SELECT * FROM t_tmall_clue_info WHERE context->>'$.info' = 'sim-093856';

Multi-Valued Indexes in MYSQL 8 are actually created for MYSQL JSON arrays. A multivalued index can have multiple index records. Multivalued indexes are used to index JSON arrays. cases

How to add a multivalued cable to the table above. "tag":[12,34,56]

CREATE INDEX idx_tmall_tag ON t_tmall_clue_info ( (CAST(context->'$.tag' AS UNSIGNED ARRAY)) );

SELECT * FROM t_tmall_clue_info WHERE JSON_CONTAINS(context->'$.tag',cast('[78]' AS JSON));

SELECT * FROM t_tmall_clue_info WHERE JSON_OVERLAPS(context->'$.tag',cast('[78]' AS JSON));

The difference between Json_contains and json_overlays is that one contains the values of an array, the other does not, and must contain all the values of the array you query.

After adding the index, query the values in the array, which can be indexed.

SELECT * FROM t_tmall_clue_info WHERE 78 MEMBER OF(context->'$.tag');

Of course, it is possible to query only one value in an array.

Here are a few things to note.

1 If the multivalued key portion has an empty array, no entries are added to the index and index scans cannot access data records.

Multivalued indexes are virtual indexes on virtual columns, so they must obey the same rules as secondary indexes on virtually generated columns.

Here are some other ways to do it

How to display only something that needs to be displayed, and you can see that if there is no value in here, MySQL will display null.

select context-> '$.tag',context-> '$.date' from t_tmall_clue_info;

Both of the above statements can display related information, but the symbols are different

->

->>

In the presentation phase, it is equivalent to the choice between "" and "". Such a symbol is called inline path operator.

In fact, most of the requirements we encounter at present are queries, metadata will not be modified, so UDPATE things, let's talk about it next time.

Also, even if you use non-array information in MYSQL, it's easy to turn it into an array.

SELECT JSON_ARRAY(date_add(now(), interval 1 day), date_add(now(), interval 2 day), date_add(now(), interval 3 day), date_add(now(), interval 4 day), date_add(now(), interval 5 day)) as array1;

Of course, if you want to turn a bunch of values into KEY VALUE, it is also very simple. json_object can also help you.

How to deal with JSON in MYSQL8 is shared here. I hope the above content can be of some help to everyone and learn more. If you think the article is good, you can share it so that more people can see it.

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.