How to query json details in the database in mysql5.6 and below versions

MySQL Sometimes when saving data, some messy and infrequently used data will be thrown into a json field. So how to query the json in the database and mysql storage json note? What about those formats? Next, I will give you a detailed introduction through this article. Friends who need it can refer to it

When saving data in MySQL, sometimes some messy and infrequently used data will be thrown into a json field

Let’s talk about the formats that mysql should pay attention to when storing json:

#1: Pay attention to saving in Chinese and do not convert it into transcoding. After transcoding, the query will be very troublesome, and it will be compressed later. Bringing one more parameter is more convenient!

json_encode(array(),JSON_UNESCAPED_UNICODE);

Benefits: In this way, Chinese characters can better match the query when querying

2: The fields are unified. It is best to start setting the field names when saving. It is impossible to develop a larger project by one person. Developing unified fields can reduce a lot of unnecessary troubles and bugs caused by field disagreements (anyway, I suffered a loss on this, but when I led everyone to do the project, I didn't expect that adding rush would make subsequent testing modifications more time-consuming than development)

Benefits: Reduce the amount of query data code processing and reduce project page display bugs

3: Can save one-dimensional arraysNever save two-dimensional arrays

Reason: The two-dimensional array is not controllable. This is determined by more needs.

Personal habits. Anyway, this is my habit, hahaha. If you are unhappy, you can hit me!

Let’s get down to business: of course the query uses like

For example, there is a content field in a table, and now we need to find the actid of 123456789

5.7：select * from table where content->'$.actid' like '%123456789%';
5.6：select * from table where content like '%"actid":"123456789"%'

This should be You can understand it at a glance. Teaching is definitely a complete set. Isn’t it the same as doing health care? So some people in the code said how to use it

$id="123456789";
$sql="select * from table where content like '%\"actid":\""$id"\"%\'";

The above is the detailed content of How to query json details in the database in mysql5.6 and below versions. For more information, please follow other related articles on the PHP Chinese website!