php php如何通过mysql存储和读取json数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7602204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
php how to store and read json data via mysql?
提问by fish man
php how to store and read json data via mysql?
php如何通过mysql存储和读取json数据？
mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");
then, how to update datavalue? read out the data, then insert it with an json_encode and decode process, or only easy way update?
那么，如何更新data值呢？读出数据，然后用 json_encode 和解码过程插入它，或者只是简单的方法更新？
[{"id": "1", "value": "yes"}]
then insert another change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}]...
然后插入另一个更改[{"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"}]
[{"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"}]
然后更新另一个，更改为 [{"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.
我想问一下，如何更智能、更高效地进行这个mysql查询处理？感谢您的更多建议。
回答by Mathieu Dumoulin
Technically, you are going the wrong way with that. MySQL is used to store each of your ID/VALUE seperately. For the sake of NOT changing your code we'll look at your solution first but then i'll explain the "better" way of doing it.
从技术上讲，你这样做是错误的。MySQL 用于单独存储您的每个 ID/VALUE。为了不更改您的代码，我们将首先查看您的解决方案，然后我将解释“更好”的方法。
First, you need to make your JSON as a variable, not part of your SQL:
首先，您需要将 JSON 设为变量，而不是 SQL 的一部分：
mysql_query("INSERT INTO text (data) VALUES (".mysql_real_escape_string(array(json_encode('id' => $uid, 'value' => 'yes'))).")");
mysql_query("INSERT INTO text (data) VALUES (".mysql_real_escape_string(array(json_encode('id' => $uid, 'value' => 'yes'))).")");
instead of
代替
mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");
mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");
This first part will allow you to at least instead the data correctly into mysql. I am ASSUMING your table has an ID and that you will be using it to update or delete
第一部分将允许您至少将数据正确地导入 mysql。我假设你的表有一个 ID，你将使用它来更新或删除
When you retrieve your data, you can json_decode the $row['data'] to get your data back from the row and work with it. To update it, just do:
检索数据时，您可以对 $row['data'] 进行 json_decode 以从行中取回数据并使用它。要更新它，只需执行以下操作：
mysql_query("UPDATE text SET data = "'.mysql_real_escape_string(json_encode($myJsonToBeData)).'" WHERE rowid = '.$myrowid)
mysql_query("UPDATE text SET data = "'.mysql_real_escape_string(json_encode($myJsonToBeData)).'" WHERE rowid = '.$myrowid)
Now, for the RIGHT way to do this:
现在，对于执行此操作的正确方法：
The right way to do this would be to have these fields into your table: ID, JSONID, JSONVALUE and use this SQL instead:
执行此操作的正确方法是将这些字段放入您的表中：ID、JSONID、JSONVALUE 并改用此 SQL：
SELECT * FROM text WHERE id = $rowid
INSERT INTO text VALUES(NULL, $jsonid, $jsonvalue)
UPDATE text SET jsonid = $jsonid, jsondata = $jsondata
SELECT * FROM text WHERE id = $rowid
INSERT INTO text VALUES(NULL, $jsonid, $jsonvalue)
UPDATE text SET jsonid = $jsonid, jsondata = $jsondata
This is pretty basic, but it will allow you to have any number of entries in your database that make it searchable, indexed, sortable, queryable, etc...
这是非常基本的，但它允许您在数据库中拥有任意数量的条目，使其可搜索、索引、可排序、可查询等......
回答by KoZm0kNoT
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.
是的，但是....WordPress 将其大量数据存储为编码的 JSON 字符串，例如用户功能。将数组存储为离散的数据位可以避免对数据库进行多次读取，并允许您在一次读取中获得大量数据。如果您从未看到需要 SELECT JSON 字符串的各个部分，我不明白为什么这样做是不可接受的。MySQL 也必须这么认为，因为如果您愿意，它具有允许对 JSON 字符串中的各个字段进行 SELECT 的功能（请参阅 MySQL EXPLAIN 关键字）。但是我确实同意，如果您要在一个字段上进行大量 SELECT，它应该有一个自己的。这完全取决于您将如何使用数据。
回答by daiscog
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.
您可以通过不将 JSON 存储在单个字段中，而是通过使用 JSON 对象的属性名称作为字段名称创建适当的 MySQL 表来更有效地执行此操作。
Storing a text-string encoded representation of your data in a database completely destroys the point of using databases in the first place.
将数据的文本字符串编码表示存储在数据库中完全破坏了使用数据库的意义。
