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

This article mainly shows you the "mysql query field type for json what are the query methods", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql query field type is json what are the query methods" this article.

The table structure is as follows:

Id varchar (32) info json

Data:

Id = 1info = {"age": "18", "disname": "Xiaoming"}

-

Now I need to get the value of disanme in info. The query method is as follows:

1.

Select t.idrecoveryJSONEMEXTRACT (t.inforecoveryplay.disname`) as disname from tableName t where 1

Results:

Id = 1, disname= "Xiaoming"

The disname values found by the above sql are in double quotation marks, and sometimes we don't need double quotation marks, so we need to use the following method.

two。

Select t.idret t.info->'$.disname'as disname from tableName t where 1room1

Results:

Id = 1, disname= Xiaoming

Ps: let's take a look at the mysql query json field

Construction table sentence

Create Table CREATE TABLE `test` (`id` int (10), `user` json DEFAULT NULL COMMENT 'user information', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Insert data

It should be noted that json data requires single quotation marks to distinguish

INSERT INTO test (id,USER) VALUES (1) name: "tom", "age": 18, "money": 3000}'); INSERT INTO test (id,USER) VALUES (2) name: "jack", "age": 20, "money": 100}'); INSERT INTO test (id,USER) VALUES (3) name: "tony", "age": 21, "money": 100}') INSERT INTO test (id,USER) VALUES (4 age'{"name": "danny", "age": 21, "money": 20}'); INSERT INTO test (id,USER) VALUES (5) {"name": "janny", "age": 23, "money": 20}')

The table data is as follows

Query statement

SELECT id,JSON_EXTRACT (USER,'$.name') FROM test

Here are the query results

The above is all the contents of the article "what are the query methods when the mysql query field type is json?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.