What are the query methods when the field type of mysql query is json?
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!
