MySQL can also be used as Mongo?

Make writing a habit together! This is the second day of my participation in the “Gold Digging Day New Plan · April More text challenge”. Click here for more details.

MySQL 5.7.8 and above provides a new json field format, and provides a number of functions to manipulate JSON-type data, so that MySQL can also store document data like Mongo. Let’s take a look at how we can apply this type.

1. Create a document table

First, we create a table to save the document, specifying the primary key, the document name, the document content, where the document content field we can set to JSON format, so that we can use this field to save json format data, just like Mongo.

Before the JSON format was available, the creation might be stored in BLOB, TEXT, or VARCHAR, and converted to the corresponding object in the project using deserialization.

CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key'.`name` varchar(255) DEFAULT NULL COMMENT 'Document name'.`content` json DEFAULT NULL COMMENT 'Document contents',
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Note: JSON columns cannot have non-null defaults and take up roughly the same space as LONGBLOB or LONGTEXT.

** Trivia: ** What are the advantages of using JSON format storage over using string sequences?

• Automatic validation of JSON document format, error format cannot be inserted.
• Optimized storage format. Json documents stored in JSON-formatted columns will be stored in BLOB format and transferred directly in binary format without being converted to document format for presentation.

2. Initialize document table data

We add several pieces of data to the document table, select the data with representative type, including value is an array, value is an object, and confirm whether they can be added.

insert into `document` VALUES(1.'log'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "thread" : "thread01"}');
insert into `document` VALUES(2.'log'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "thread" : "thread02"}');
insert into `document` VALUES(3.'log'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "thread" : "thread03"}');
insert into `document` VALUES(4.'log_thread_arr'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "threadGroup" : [" thread01 ", "thread02"]}');
insert into `document` VALUES(5.'log_thread_info'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "threadInfo" : {" threadNo ":" 01 ", "threadName" : "thread01"}}');
Copy the code

Add successfully, as shown in the following figure:

Let’s try adding a piece of data that doesn’t fit the JSON format to make sure it can be saved.

insert into `document` VALUES(3.'log'.'{" IP ":" 127.0.0.1 "and" port ":" 8080 ", "thread" : "t');
Copy the code

Data that does not conform to the format cannot be saved, with the following error message:

Invalid JSON text: “Missing a closing quotation mark in string.” at position 51 in value for column ‘document.content’.

3. Json related functions

The following are some common json functions, and there are many more in the system besides the ones listed below.

You can also use the following functions for columns that are not in JSON format.

3.1 JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] …) If the value is not present in json, it will return Null. We can also write it after WHERE as a criterion, but we do not write it this way. We usually write WHERE as 3.3

-- Query a value in JSONSELECT JSON_EXTRACT(document.content, '$.ip') FROM document;
SELECT JSON_EXTRACT(document.content, '$.thread') FROM document; -- can be used as WHERE after the conditionSELECT
	JSON_EXTRACT( document.content, '$.thread' ) 
FROM
	document 
WHERE
  JSON_EXTRACT( document.content, '$.thread' )  IS NOT NULL; -- Query json information at the next levelSELECT JSON_EXTRACT(document.content, '$.threadInfo.threadNo') FROM document;
Copy the code

Query result:

3.2 – >

-> JSON column return value; The equivalent of JSON_EXTRACT ()

SELECT * from document WHERE document.content->'$.thread' IS NOT NULL;
Copy the code

Query result:

3.3 JSON_SET

JSON_SET(json_doc, path, val[, path, val] …) Insert the data into the JSON document

UPDATE document SET document.content = JSON_SET(document.content, '$.ip'.'0.0.0.0') WHERE id = 3;
Copy the code

3.4 JSON_CONTAINS, JSON_CONTAINS_PATH

JSON_CONTAINS(target, candidate[, path]) contains a specific object

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …) Whether to contain corresponding fields

-- Whether to include a specific object
SELECT * from document WHERE JSON_CONTAINS(document.content,JSON_OBJECT("thread"."thread01"));
-- Whether to include fields
SELECT * from document WHERE 
JSON_CONTAINS_PATH(document.content, 'one'.'$.thread');
Copy the code

Query result:

3.5 JSON_OBJECT, JSON_ARRAY

JSON_OBJECT([key, val[, key, val] …] ) is to convert a key-value pair into a JSON object, as in 3.4

JSON_ARRAY([val[, val] …] Convert a group value to a JSON array

SELECT * from document WHERE JSON_CONTAINS(document.content->'$.threadGroup', JSON_ARRAY("thread01"));
Copy the code

Query result:

3.6 JSON_INSERT

JSON_INSERT(json_doc, path, val[, path, val] …) Insert the fields

UPDATE document set document.content=JSON_INSERT(document.content, '$.id'.'1'.'$.name'.'log01') 
WHERE id=1
Copy the code

3.7 JSON_REPLACE

JSON_REPLACE(json_doc, path, val[, path, val] …)

UPDATE document SET document.content = JSON_REPLACE(document.content, '$.ip'.'0.0.0.0') WHERE id = 1;
Copy the code

3.8 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] …) Delete data from a JSON document

UPDATE document SET document.content = JSON_REMOVE(document.content, '$.name') WHERE id = 1;
Copy the code

3.9 JSON_TYPE

JSON_TYPE(json_val) Queries the attribute type of a CERTAIN JSON field

SELECT JSON_TYPE(document.content->'$.ip') from document ;
Copy the code

The general types are INTEGER, STRING, ARRAY, and OBJECT

3.10 JSON_KEYS

JSON_KEYS(json_doc[, path]) returns an array of all the keys in the document

SELECT JSON_KEYS(document.content) from document ;
Copy the code

Query result:

3.11 JSON_UNQUOTE

JSON_UNQUOTE(json_val) dereferences and extracts the data directly to string

3.12 JSON_LENGTH

JSON_LENGTH(json_doc[, path]) The outermost layer of JSON or the length of the specified path. The scalar length is 1. The length of an array is the number of array elements, and the length of an object is the number of object members.

4. How to optimize the retrieval efficiency of JSON text

If we store data in JSON format, search performance drops dramatically when there is a large amount of data, so how can we optimize?

In JSON format, indexes cannot be created directly. We need to construct virtual generated columns for fields commonly used for query in JSON and add indexes to virtual generated columns to optimize query performance.

There are two types of generated columns: stored generated columns and virtual generated columns.

• Store build column

  Storage Generated columns are similar to common columns. The values of these columns are automatically calculated and stored when data is inserted or updated, occupying storage space.

• Virtual generated column

  Virtually generated columns take no storage space and are only evaluated when read (similar to views).

  Virtual columns support NOT NULL, UNIQUE, primary key, CHECK, and foreign key constraints in addition to indexes, but do NOT support the DEFAULT value.

Grammar - GENERATED column col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT'string'] -- generate virtual columnsALTER TABLE document ADD COLUMN 
v_log_id VARCHAR(32) 
GENERATED ALWAYS 
AS (JSON_UNQUOTE(JSON_EXTRACT(document.content, '$.id'))) VIRTUAL NULL; Add an index to the generated columnCREATE INDEX idx_v_log_id ON document(v_log_id); SELECT * FROM document WHERE v_log_id = SELECT * FROM document WHERE v_log_id ='01'
Copy the code

Query result:

5. To summarize

When we have json document storage requirements and it is not necessary to introduce MongoDB into the project, we can consider using the JSON format of MySQL. Its built-in functions are powerful enough to support our daily use. When the query bottleneck occurs, we can also increase the index by using MySQL to virtually generate columns to optimize the query efficiency. In addition, since it is very inconvenient to extend fields in the production environment, jSON-formatted fields are very extensible, so it is very suitable for use in extended fields, so that some of our less important but needed data can be put into extended fields.

Thank you for reading, if you feel helpful, please click a thumbs-up, thanks a million!!