php how to store and read json data via mysql?

Question

php how to store and read json data via mysql?

mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");

then, how to update data value? read out the data, then insert it with an json_encode and decode process, or only easy way update?

[{"id": "1", "value": "yes"}]

then insert another change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}]...

Or even if a long long value.

[{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"}]

then update another one, change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"},{"id": "10001", "value": "yes"}]

I wanna to ask, how to do this mysql query processing more wiser and efficiently? Thanks for more suggestion.

Answer 1

You can do this more efficiently by NOT storing JSON in a single field but by creating a proper MySQL table with the JSON object's property names as field names.

Storing a text-string encoded representation of your data in a database completely destroys the point of using databases in the first place.

Answer 2

Yes but....WordPress stores a lot of its data as encoded JSON strings, such as the user capabilities. Storing an array as a discrete bit of data takes away having to do multiple reads on the database and allows you to get a lot of data on one read. If you never see the need to SELECT the individual parts of the JSON string I don't see why it isn't acceptable to do it this way. MySQL must think so too because it has functions to allow a SELECT on the individual fields within a JSON string if you so desired (see MySQL EXPLAIN keyword). But I do agree if you were going to do a lot of SELECTs on a field it should have one of its own. It all depends on how you are going to use the data.