如何在MySQL数据库中存储JSON字符串 [英] How to store JSON string in MySQL db

问题描述

我正在使用以下代码将JSON数据存储在MySQL表中.如果JSON较短，但会中断较长的文本，则效果很好. "field_json"是一个LONGTEXT.

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.

$sql = sprintf("UPDATE mytable 
    SET field_json = '$json_string'
    WHERE id = $userid");
$result = mysql_query($sql);

我得到的错误是:

无效的查询:您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本以获取正确的语法 在'G附近使用 '，"用户名:" C0WB0Y，"姓氏:"，" id:31874363}，{" pathToPhoto:" 22960/phot' 在第2行

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注入的影响:

Use place holders otherwise you are susceptible to SQL injection: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

否则，这里有一个快速解决方法: http://php .net/manual/en/function.mysql-real-escape-string.php

Otherwise, here's a quick fix: 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);

编辑

请使用PDO( http://www.php.net/manual/zh/book.pdo.php ). mysql扩展名从5.5起已被弃用

EDIT

Please use PDO ( http://www.php.net/manual/en/book.pdo.php ). The mysql extension has been deprecated as of 5.5

这篇关于如何在MySQL数据库中存储JSON字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！