php 如何在 MySQL db 中存储 JSON 字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8990762/
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
How to store JSON string in MySQL db
提问by uwe
I'm storing JSON data in a MySQL table using the code below. It works fine if the JSON is short but breaks for longer text. The "field_json" is a LONGTEXT.
我使用下面的代码将 JSON 数据存储在 MySQL 表中。如果 JSON 很短但对于较长的文本会中断，则它可以正常工作。“field_json”是一个 LONGTEXT。
$sql = sprintf("UPDATE mytable
SET field_json = '$json_string'
WHERE id = $userid");
$result = mysql_query($sql);
The error I'm getting is:
我得到的错误是：
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'G '","username":"C0WB0Y","lastName":"","id":31874363},{"pathToPhoto":"22960/phot' at line 2
无效查询：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'G'","username":"C0WB0Y","lastName":"","id":31874363},{"pathToPhoto":" 附近使用的正确语法22960/照片'在第 2 行
回答by Martin Samson
Use place holders otherwise you are susceptible to SQL injection: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
使用占位符，否则您很容易受到 SQL 注入的影响：http: //php.net/manual/en/mysqli.quickstart.prepared-statements.php
Otherwise, here's a quick fix: http://php.net/manual/en/function.mysql-real-escape-string.php
否则，这里有一个快速修复：http: //php.net/manual/en/function.mysql-real-escape-string.php
$sql = sprintf(
"UPDATE mytable SET field_json = '%s' WHERE id = '%s'",
mysql_real_escape_string($json_string),
mysql_real_escape_string($userid)
);
$result = mysql_query($sql);
EDIT
编辑
Please use PDO ( http://www.php.net/manual/en/book.pdo.php). The mysqlextension has been deprecated as of 5.5
请使用 PDO ( http://www.php.net/manual/en/book.pdo.php)。从mysql5.5 开始，该扩展已被弃用
回答by nickb
Escape the JSON string:
转义 JSON 字符串：
$json_string = mysql_real_escape_string( $json_string);
$sql = sprintf("UPDATE mytable
SET field_json = '$json_string'
WHERE id = $userid");
$result = mysql_query($sql);
回答by Maximilian Zellhofer
You need to escape the quotes in your JSON string - otherwise they terminate the SQL-Query resulting in the exception you got.
您需要对 JSON 字符串中的引号进行转义 - 否则它们会终止 SQL-Query，从而导致您获得异常。
回答by Harry Forbess
try this
尝试这个
$json_string = mysql_real_escape_string( $json_string );
$sql = sprintf("UPDATE mytable
SET field_json = '$json_string'
WHERE id = $userid");
$result = mysql_query($sql);
