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

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

Let's talk about the formats that MySQL should pay attention to when storing JSON:

1: Be careful not to transcode the Chinese text when saving. Transcoding will make the query very troublesome. When compressing, add an extra parameter at the end, which will make it more convenient!

json_encode(array(),JSON_UNESCAPED_UNICODE);

Advantages: Chinese characters are better matched when searching in this way

2: Unify the fields. It is best to determine the field names when saving. It is impossible for one person to develop a larger project. Unifying the fields can reduce a lot of unnecessary troubles and bugs caused by disagreements on fields (I have suffered a loss on this, but I didn’t think about it when I led everyone to do the project, and the rush caused the subsequent testing and modification to take more time than the development)

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

3: Never store a two-dimensional array if you can store a one-dimensional array

Reason: Two-dimensional arrays are uncontrollable. This is still more based on demand.

Personal habit. This is my habit anyway, 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. Now we need to find the content field whose actid is 123456789.

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

This should be understandable at a glance. Teaching is definitely a full set, and it is also a health care. So some people in the code say how to use it

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

The above is what I introduced to you about how to query JSON in the database in MySQL 5.6 and below. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!