Does mysql have a json type?-Mysql Tutorial-php.cn

Does mysql have a json type?

青灯夜游

Release： 2020-10-02 12:03:03

Original

3625 people have browsed it

Mysql has json type. MySQL has introduced the JSON data type since version 5.7, which can directly operate json data; but below MySQL version 5.7, saving JSON format data in the table requires relying on data types such as varchar or text.

Does mysql have a json type?

JSON type in MySQL

json is a very easy-to-use data type, before mysql5.7 People use string to store json, but there is a problem that json cannot be manipulated. After 5.7, json data can be directly manipulated.

There is nothing to say about the creation, it is just json. There is no need to bring the length. The default is 0
You can use string insertion for updates and insertions.
Queries, I personally don’t like to hand over data. For background processing, let's leave it to the database. The example is as follows:
A certain table in the database has a content_json field, which contains such data!

{
  "bill": [
    {
      "bill": [
        {
          "id": "C81AEAD03F90000142E81B405F6FADC0",
          "uuid": "cfd872541b3b4507a50c2b2eda6bef28",
          "billid": "kjdzyljgmzsfzypj",
          "pageno": [],
          "billver": "V1",
          "billname": "新增测试",
          "fjNumber": "",
          "trueName": "",
          "allPageno": [
            {
              "top": 13,
              "left": 7
            }
          ],
          "billValue": {},
          "isOtherZL": "",
          "billNumber": "",
          "fjTMNumber": ""
        },
        {
          "id": "C81AED69D6400001A07818551D9EBEC0",
          "uuid": "05d87c8052cd44209c684407d200b7ec",
          "billid": "opztsfpsgd",
          "pageno": [],
          "billver": "V1",
          "billname": "发票申购",
          "fjNumber": "",
          "trueName": "",
          "allPageno": [
            {
              "top": 13,
              "left": 7
            }
          ],
          "isOtherZL": "",
          "billNumber": "",
          "fjTMNumber": ""
        }
      ],
      "index": "",
      "dependBjBill": {
        "formula": "",
        "keyView": ""
      },
      "codeCondition": {
        "formula": "",
        "keyView": ""
      },
      "billRuleCondition": {
        "formula": "",
        "keyView": ""
      }
    },
    {
      "bill": [
        {
          "id": "C81AED84903000019B29EAB0196014CE",
          "uuid": "0d93fe614d09489cbad8fa5a3d160289",
          "billid": "kjdzcwgwht",
          "pageno": [],
          "billver": "V1",
          "billname": "财务顾",
          "fjNumber": "",
          "trueName": "",
          "allPageno": [
            {
              "top": 39,
              "left": 7
            }
          ],
          "isOtherZL": "",
          "billNumber": "",
          "fjTMNumber": ""
        }
      ],
      "index": "",
      "dependBjBill": {
        "formula": "",
        "keyView": ""
      },
      "codeCondition": {
        "formula": "",
        "keyView": ""
      },
      "billRuleCondition": {
        "formula": "",
        "keyView": ""
      }
    }
  ],
  "questions": [],
  "relyonCondition": {}
}

Copy after login

The structure is like this, we want to query the bottom uuid! Check according to conditionsThe bill under the bill is an array. The bill inside is still an array. So how to check the bottom uuid?
Does mysql have a json type? ；
Personally, I prefer to use a method similar to lambda. After all, this is a stylish and beautiful operation:

SELECT content_json->'$.bill[*].bill[*].uuid' 
from  b 
WHERE JSON_CONTAINS(content_json->'$.bill[*].bill[*].uuid' ,'["cfd872541b3b4507a50c2b2eda6bef28"]')

Copy after login

Query results Does mysql have a json type?

Code Analysis:
content_json->'$.bill[].bill[].uuid' ;

represents the content_json field
"$" is a required symbol point to represent the object
* represents all

Then the meaning of the code is to query the uuid of all bills (second) in the content_json field to form an array and return it;
The conditions followed by where It means that there is a certain value. This value can be one or multiple
JSON_CONTAINS (field, parameter);
The field must be the specified value to be checked, such as content_json->' $.bill[].bill[].uuid' ;
The parameter must be a json type string, which is not acceptable.

Recommended tutorial: mysql video tutorial

The above is the detailed content of Does mysql have a json type?. For more information, please follow other related articles on the PHP Chinese website!