MySQL存儲Json字符串遇到的問題與解決方法
環(huán)境依賴
Python 2.7
MySQL 5.7
MySQL-python 1.2.5
Pandas 0.18.1
在日常的數(shù)據(jù)處理中，免不了需要將一些序列化的結(jié)果存入到MySQL中。這里以插入JSON數(shù)據(jù)為例，討論這種問題發(fā)生的原因和解決辦法?，F(xiàn)在的MySQL已經(jīng)支持JSON數(shù)據(jù)格式了，在這里不做討論；主要討論如何保證存入到MySQL字段中的JsonString能被正確解析。
問題描述
# -*- coding: utf-8 -*-
import MySQLdb
import json
mysql_conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306, charset='utf8')
mysql_cur = mysql_conn.cursor()
increment_id = 1
dic = {"value": "<img src=\"xxx.jpg\">", "name": "小明"}
json_str = json.dumps(dic, ensure_ascii=False)
sql = "update demo set msg = '{0}' where id = '{1}'".format(json_str, increment_id)
mysql_cur.execute(sql)
mysql_conn.commit()
mysql_cur.close()
應(yīng)用場景抽象如上所示，將一個字典經(jīng)過經(jīng)過Json序列化后作為一個表字段的值存入到Mysql中，按照如上的方式更新數(shù)據(jù)時，發(fā)現(xiàn)落庫的JsonString反序列化失?。宦鋷旖Y(jié)果和反序列化結(jié)果分別如下所示：
原因分析
對于字符串中包含引號等其他特殊符號的處理思路在大多數(shù)編程語言中都是相通的：即就是通過轉(zhuǎn)義符來保留所需要的特殊字符。Python中也不例外，如上所示，對于一個字典{"value": "<img src="xxx.jpg">", "name": "小明"}，要想在編譯器里正確的表示它，就需要通過對轉(zhuǎn)義包裹xxx.jps的兩個雙引號，不然會提示錯誤，所以它的正確寫法為：{"value": "<img src=\"xxx.jpg\">", "name": "小明"}；將序列化后的String作為參數(shù)傳入待執(zhí)行的sql語句中，通過編輯器的debug模式查看的效果如下所示：
而這句sql經(jīng)過編譯器解析后傳入到MySQL去執(zhí)行的本質(zhì)為：'update demo set msg = '{"source": "<img src="xxx.jpg">", "type": "圖片"}' where id = '1'，因此落庫的實際結(jié)果其實并不是目標字典對應(yīng)的序列化結(jié)果，而是目標數(shù)據(jù)對應(yīng)的字面字符串值。
解決方案
可以通過轉(zhuǎn)義符替換、修改sql書寫方式或通過DataFrame.to_sql()三種方式來解決。
方案一 轉(zhuǎn)義符替換
通過上文可以了解到，是因為\\"xxx.jpg\\"的本質(zhì)即就是"xxx.jpg"，所以數(shù)據(jù)庫讀到的也就是{"source": "<img src="xxx.jpg">", "type": "圖片"}，從而導(dǎo)致插入的結(jié)果并不能被正確反序列化。可以通過簡單粗暴的轉(zhuǎn)義符替換方式來解決這個問題：json_str.replace('\\', '\\\\')，這樣就保證最終的解析結(jié)果為\"xxx.jpg\"。
方案二 修改sql書寫方式
def execute(self, query, args=None):
del self.messages[:]
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.unicode_literal.charset)
if args is not None:
# 通過調(diào)用內(nèi)置的解析函數(shù)literal，將目標參數(shù)按照原義解析
# 解析的依據(jù)詳見源碼的MySQLdb.converters
if isinstance(args, dict):
query = query % dict((key, db.literal(item))
for key, item in args.iteritems())
else:
query = query % tuple([db.literal(item) for item in args])
try:
r = None
r = self._query(query)
except TypeError, m:
if m.args[0] in ("not enough arguments for format string",
"not all arguments converted"):
self.messages.append((ProgrammingError, m.args[0]))
self.errorhandler(self, ProgrammingError, m.args[0])
else:
self.messages.append((TypeError, m))
self.errorhandler(self, TypeError, m)
except (SystemExit, KeyboardInterrupt):
raise
except:
exc, value, tb = sys.exc_info()
del tb
self.messages.append((exc, value))
self.errorhandler(self, exc, value)
self._executed = query
if not self._defer_warnings: self._warning_check()
return r
查看MySQL-python的execute源碼(如上所示)可以發(fā)現(xiàn)，在傳入待執(zhí)行的sql語句的同時，還可以傳入?yún)?shù)列表/字典；讓MySQL-Python來幫我們進行sql語句的拼接和解析操作，修改上述樣例的實現(xiàn)方式：
increment_id = 1
dic = {"value": "<img src=\"xxx.jpg\">", "name": "小明"}
json_str = json.dumps(dic, ensure_ascii=False)
sql = "update demo set msg = %s where id = %s"
mysql_cur.execute(sql, [json_str, increment_id])
mysql_conn.commit()
mysql_cur.close()
通過走讀源碼發(fā)現(xiàn)參數(shù)經(jīng)過literal()方法將Python的對象轉(zhuǎn)化為對應(yīng)SQL數(shù)據(jù)的字符串格式；在編譯器Debug模式下可以看到最終將\\"xxx.jpg\\"轉(zhuǎn)化為\\\\\\"xxx.jpg\\\\\\"。至于為什么是六個反斜杠我自己也不太清楚；不過姑且可以這樣理解：把literal方法的操作可以假定為有一次的序列化，因為給定的數(shù)據(jù)源是\"，所以序列化的結(jié)果為應(yīng)該為\\"，即就是四個反斜杠；因為\“代表的即就是”，而期望落庫的結(jié)果為"，所以需要再添加兩個反斜杠。這種解釋不是那么準確和嚴謹，但是有利于幫助理解，若有了解底層機制和原理的，還請留言指教。
推薦使用
方案三 DataFrame.to_sql()
處理數(shù)據(jù)離不開Panda工具包；Pandas的DataFrame.to_sql()方法可以便捷有效的實現(xiàn)數(shù)據(jù)的插入需求；同樣該方法也能有效的規(guī)避上述這種序列化結(jié)果錯誤的情況，因為DataFrame.to_sql()底層的實現(xiàn)邏輯類似于方案二，也是通過參數(shù)解析的方式來拼接sql語句，核心源碼如下所示，同于不難發(fā)現(xiàn)，DataFrame.to_sql()只能支持insert操作，適用場景比較局限。對于有唯一索引的表，當待插入數(shù)據(jù)與數(shù)據(jù)表中有沖突時會報錯，實際使用時需要格外注意。
def insert_statement(self):
names = list(map(text_type, self.frame.columns))
flv = self.pd_sql.flavor
wld = _SQL_WILDCARD[flv] # wildcard char
escape = _SQL_GET_IDENTIFIER[flv]
if self.index is not None:
[names.insert(0, idx) for idx in self.index[::-1]]
bracketed_names = [escape(column) for column in names]
col_names = ','.join(bracketed_names)
wildcards = ','.join([wld] * len(names))
# 只支持Insert操作
insert_statement = 'INSERT INTO %s (%s) VALUES (%s)' % (
escape(self.name), col_names, wildcards)
return insert_statement
補充：
補充：不同情況
1.模糊查詢json類型字段
存儲的數(shù)據(jù)格式（字段名 people_json）：
{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}代碼如下（示例）：
select * from table_name where people_json->'$.name' like '%zhang%'
2.精確查詢json類型字段
存儲的數(shù)據(jù)格式（字段名 people_json）：
{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}代碼如下（示例）：
select * from table_name where people_json-> '$.age' = 13
3.模糊查詢JsonArray類型字段
存儲的數(shù)據(jù)格式（字段名 people_json）：
[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]代碼如下（示例）：
select * from table_name where people_json->'$[*].name' like '%zhang%'
4.精確查詢JsonArray類型字段
存儲的數(shù)據(jù)格式（字段名 people_json）：
[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]代碼如下（示例）：
select * from table_name where JSON_CONTAINS(people_json,JSON_OBJECT('age', "13"))
總結(jié)
到此這篇關(guān)于MySQL存儲Json字符串遇到的問題與解決方法的文章就介紹到這了,更多相關(guān)MySQL存儲Json字符串內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章介紹了MySQL數(shù)據(jù)庫與其基本操作，文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05
MySQL 配置文件 my.cnf / my.ini 區(qū)別解析
充分理解 MySQL 配置文件中各個變量的意義對我們有針對性的優(yōu)化 MySQL 數(shù)據(jù)庫性能有非常大的意義，這篇文章主要介紹了MySQL 配置文件 my.cnf / my.ini 區(qū)別,需要的朋友可以參考下2022-11-11
坑人的Mysql5.7問題(默認不支持Group By語句)
這篇文章主要介紹了坑人的Mysql5.7問題(默認不支持Group By語句),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
IDEA的database插件無法連接mysql的解決辦法(08001錯誤)
用navicat鏈接數(shù)據(jù)庫正常,mysql控制臺操作正常,但是用IDEA的數(shù)據(jù)庫插件鏈接一直報 08001 錯誤,本文就給大家介紹一下IDEA的database插件無法連接mysql報08001錯誤的解決辦法,需要的朋友可以參考下2024-07-07
MySQL實現(xiàn)批量更新不同表中的數(shù)據(jù)
這篇文章主要介紹了MySQL實現(xiàn)批量更新不同表中的數(shù)據(jù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-05-05
MySql批量插入時如何不重復(fù)插入數(shù)據(jù)
Mysql插入不重復(fù)的數(shù)據(jù)，當大數(shù)據(jù)量的數(shù)據(jù)需要插入值時，要判斷插入是否重復(fù)，然后再插入，那么如何提高效率，本文就詳細的介紹一下，感興趣的可以了解一下2021-06-06
解決SQLyog連接MySQL出現(xiàn)錯誤Plugin caching_sha2_password co
當使用SQLyog連接MySQL時,如果遇到插件caching_sha2_password無法加載的錯誤,可以通過更改密碼并將其標識為mysql_native_password來解決,具體步驟包括：打開命令提示符窗口,登錄MySQL,修改密碼并更換插件,然后使用新密碼連接SQLyog2025-01-01
這篇文章主要介紹了MySQL系統(tǒng)及自定義變量方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
MySQL數(shù)據(jù)庫刪除數(shù)據(jù)自增ID不連續(xù)的實現(xiàn)示例
本文介紹了MySQL數(shù)據(jù)庫刪除數(shù)據(jù)后自增ID不連續(xù)的問題,文中通過2種方法解決,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11
一文帶你永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)
在MySQL啟動時會檢查當前系統(tǒng)的時區(qū)并根據(jù)系統(tǒng)時區(qū)設(shè)置全局參數(shù)system_time_zone的值,下面這篇文章主要給大家介紹了關(guān)于如何永久擺脫Mysql時區(qū)錯誤問題(idea數(shù)據(jù)庫可視化插件配置)的相關(guān)資料,需要的朋友可以參考下2022-08-08