Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL database JSON type usage, meaning, built-in function", the article explains the content is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "MySQL database JSON type usage, meaning, built-in function" bar!

Overview

In MySQL database, JSON data type was introduced until version 5.7. before that, if you want to save data of JSON format type in a table, you need to rely on data types such as varchar or text. If you use JSON type to build a table in a database earlier than version 5.7, it will obviously not be successful.

How to use JSON types

1. Create a table

Creating a table with JSON data columns in MySQL is not much different from the data type. The specific examples are as follows:

CREATE TABLE tab_base_info (id BIGINT NOT NULL PRIMARY KEY auto_increment, content json)

2. New data

Insert a statement and notice the contents of the JSON data column:

INSERT INTO tab_base_info (content) VALUES ('{"author": "HWB", "toutiao": "https://www.toutiao.com/a6712764513814315528"}');"

Here is a reminder:

The data stored in the JSON column is either NULL or must be in JSON format, or an error will be reported.

The JSON data type has no default value ("DEFAULT NULL" when declared).

JSON data type meaning

In fact, without the support of JSON data type, we can still save data in this format through varchar type or text type, but why add special support for this data format? There must be some advantages over varchar or text to store this type.

To ensure a strong check of the JSON data type, the JSON data column will automatically check whether the content stored in this column conforms to the JSON format, and the abnormal format will report an error, while types such as varchar type and text do not have such a mechanism.

MySQL also provides a set of built-in functions for manipulating JSON-typed data.

For a more optimized storage format, JSON data stored in a JSON column is converted to an internal specific storage format, allowing for fast reading.

Specific key values can be modified based on the features of the JSON format. (that is, instead of taking the whole piece of content out and traversing it in the program and looking for a replacement and then stuffing it back in, MySQL's built-in function allows you to do it with a SQL statement.)

MySQL's built-in functions about JSON

MySQL's operations on JSON data format provide many efficient built-in functions. We can find detailed instructions and instructions on MySQL's official website. Here is a guide to JSON functions:

In fact, you can see from the home page of the JSON feature introduction that these built-in functions support us to create, find, replace, return values and other related operations. For example, we can use the JSON_REPLACE () function when we replace the specified content. However, * the final content substitution is performed through pure SQL statements, and requires the execution of UPDATE statements, such as:

UPDATE tab_base_info SET content = json_replace (content,'$.author', "xiaoming") WHERE id = 1

Where "$. * *" indicates that a matching modified field in the JSON content is found.

Thank you for your reading, the above is the "MySQL database JSON type usage, meaning, built-in function" content, after the study of this article, I believe you on the MySQL database JSON type usage, meaning, built-in function this problem has a deeper understanding, the specific use of the need for you to practice and verify. 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.