How to query the json and mysql5.6json in the database in MySQL and earlier versions

How to query the json and mysql5.6json in the database in MySQL and earlier versions

Saving data in MySQL sometimes throws messy and uncommon data into a json field.

The following describes the formats that mysql should pay attention to when storing json:

1: Do not convert the data stored in Chinese to transcoding. After transcoding, the query is very troublesome. A parameter is added after compression, which is more convenient than a little bit!

json_encode(array(),JSON_UNESCAPED_UNICODE);

Benefit: in this way, Chinese characters can be better matched for queries.

2: it is best to set the field name when fields are stored in a unified manner, developing a project with a big point is impossible. Developing a uniform field by one can reduce a lot of unnecessary troubles and disagree with the field, leading to a bug (I suffered a loss on it anyway, however, I didn't expect to add a new project to make subsequent tests and modifications take more time than development)

Benefits: reduces the amount of data code processed by queries and reduces the number of bugs displayed on the project page.

3: one-dimensional arrays are not saved.

Cause: the two-dimensional array is not controllable. This is more required.

My habits are my habits. Hahaha, you can beat me!

The main thing is: query uses like.

For example, if a table contains a content field, it is found that the value of 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 understood at a glance, teaching is certainly a full set of teaching, do not do health care, so some people in the code say how to use

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

The above section describes how to query the json in the database in MySQL and later versions. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!