mysql支持json格式的数据类型, json格式的数据binary为mysql层实现的, 所以字节序是小端. 解析的时候要注意下
innodb存储它的时候是当作big类型来处理的, 所以innodb只要读出该二进制数据即可, 剩下的就交给Mysql我们来处理.
JSON磁盘上的格式
mysql官方还是介绍得很详细的. 文件: sql/json_binary.h
说明: 下面出现的2/4字节 表示: 如果是小small object/array 就使用2字节, large object/array就使用4字节
第一部分, 使用字节表示 数据类型
代码语言：c复制 type ::=
0x00 | // small JSON object
0x01 | // large JSON object
0x02 | // small JSON array
0x03 | // large JSON array
0x04 | // literal (true/false/null) 1:true 2:false 0:null
0x05 | // int16
0x06 | // uint16
0x07 | // int32
0x08 | // uint32
0x09 | // int64
0x0a | // uint64
0x0b | // double
0x0c | // utf8mb4 string
0x0f // custom data (any MySQL data type)简化下就是这样的
代码语言：c复制 value ::=
object |
array |
literal |
number |
string |
custom-data
第二部分 使用2/4字节表示 元素数量
第三部分 使用2/4字节 表示这个json obj/arr大小(不含json类型所使用的1字节)
第三部分 是key-entry 和 vaue-entry (array不需要key-entry)
第四部分 是value 可以是上面的那些数据类型, 也就是还可以是json obj/arr 如果是json obj/arr 就继续第二步到第四部 (递归), 如果是utf8mb4字符串的话, 会有一字节记录数据大小(毕竟value_entry只记录了偏移量....)
画图的话就是这样
代码语言：py复制 - -----------------
| JSON OBJECT/ARRAY |
- -----------------
|
-------------------------------------------------------------------------
| TYPE | ELEMENT_COUNT | KEY-ENTRY(if object) | VALUE-ENTRY | KEY | VALUE |
-------------------------------------------------------------------------
| | |
| | --------------
-------------------------- | | UTF8MB4 DATA |
| KEY-OFFSET | KEY-LENGTH | | --------------
-------------------------- |
|
--------------------------------
| TYPE | OFFSET/VALUE(if small) |
--------------------------------
OBJECT VALUE = KEY_ENTRY VALUE_ENTRY KEY VALUE #KEY肯定是字符串, 所以不需要记录数据类型
ARRAY VALUE = VALUE_ENTRY VALUE #不需要KEY
KEY_ENTRY = KEY_OFFSET(2/4bytes) KEY_LNGTH(2 bytes)
VALUE_ENTRY = TYPE(1byte) OFFSET(2/4 bytes)/VALUE (如果类型是int,literal之类的,就直接是值了, 否则就走OFFSET)
困难点
解析为dict对象很简单, 但有true,false,null的时候, 拼接为sql就有点难度了....
正当我准备人工拼接字符串的时候, 我想起了还有json包, 可以直接使用json.dumps 来做(........)
使用脚本解析
从ibd文件解析出json对象的过程这里就省略了. (感兴趣的可以看下之前讲解的ibd文件解析)
就假设你已经解析出来如下json原始数据了
代码语言：python代码运行次数：0复制(venv) 14:34:40 [root@ddcw21 ibd2sql_v1.0]#python test.py /data/mysql_3314/mysqldata/ibd2sql/t20240102_js.ibd
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (1, 'aa', 'x00x01x00rx00x0bx00x02x00x05{x00t1');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (2, 'aa', 'x00x01x00,x00x0bx00x02x00x0crx00t1x1eAAAAAAAAAAAAAAAAACBBBBBBBBBBBB');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (3, 'aa', 'x00x02x00)x00x12x00x02x00x14x00x02x00x00x16x00x0c&x00a1a2x01x00x10x00x0bx00x02x00x0crx00b1x02b1x02a6');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (4, 'bb', 'x00x01x00x11x00x0bx00x02x00x0crx00t1x03ABC');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (5, 'bb', 'x00x01x00rx00x0bx00x02x00x04x02x00t1');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (6, 'bb', 'x02x02x00*x00x00nx00x00x1ax00x01x00x10x00x0bx00x02x00x0crx00AAx02AAx01x00x10x00x0bx00x02x00x0crx00BBx02BB');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (7, 'bb', "x02x02x007x00x00nx00x00'x00x01x00x1dx00x0bx00x02x00x00rx00AAx01x00x10x00x0bx00x02x00x0crx00CCx02CCx01x00x10x00x0bx00x02x00x0crx00BBx02BB");
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (8, 'bb', 'x02x02x00Dx00x00nx00x004x00x01x00*x00x0bx00x02x00x00rx00AAx01x00x1dx00x0bx00x02x00x00rx00CCx01x00x10x00x0bx00x02x00x0crx00DDx02DDx01x00x10x00x0bx00x02x00x0crx00BBx02BB');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (9, 'bb', 'x02x03x00Tx00x00rx00x007x00x00Gx00x01x00*x00x0bx00x02x00x00rx00AAx01x00x1dx00x0bx00x02x00x00rx00CCx01x00x10x00x0bx00x02x00x0crx00DDx02DDx01x00x10x00x0bx00x02x00x0crx00BBx02BBx01x00rx00x0bx00x02x00x05x02x00FF');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (10, 'bb', 'x02x03x00Tx00x00rx00x007x00x00Gx00x01x00*x00x0bx00x02x00x00rx0013x01x00x1dx00x0bx00x02x00x00rx00CCx01x00x10x00x0bx00x02x00x0crx00DDx02DDx01x00x10x00x0bx00x02x00x0crx00BBx02BBx01x00rx00x0bx00x02x00x05x02x00FF');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (11, 'bb', 'x00x03x00x1fx00x19x00x02x00x1bx00x02x00x1dx00x02x00x04x01x00x04x02x00x04x00x00aabbcc');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (12, 'cc', 'x00x03x00x1fx00x19x00x02x00x1bx00x02x00x1dx00x02x00x05x01x00x05x02x00x05x00x00aabbcc');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (13, 'cc', 'x02x01x00x14x00x00x07x00x01x00rx00x0bx00x02x00x05x01x00aa');好我们现在使用 jsonob去解析该二进制对象为dict/list
代码语言：python代码运行次数：0复制>>> from ibd2sql.mysql_json import jsonob
>>> data = b'x02x03x00Tx00x00rx00x007x00x00Gx00x01x00*x00x0bx00x02x00x00rx0013x01x00x1dx00x0bx00x02x00x00rx00CCx01x00x10x00x0bx00x02x00x0crx00DDx02DDx01x00x10x00x0bx00x02x00x0crx00BBx02BBx01x00rx00x0bx00x02x00x05x02x00FF'
>>>
>>> jsonob(data[1:],int.from_bytes(data[:1],'little'))
<ibd2sql.mysql_json.jsonob object at 0x7f3ef7509dd8>
>>> jsonob(data[1:],int.from_bytes(data[:1],'little')).init()
[{'13': {'CC': {'DD': 'DD'}}}, {'BB': 'BB'}, {'FF': 2}]
>>> 注意该class 第一个参数为数据, 第二个参数为类型(int)
然后把该class 引入到我们的ibd2sql脚本中. 就能得到如下的sql了
代码语言：shell复制(venv) 14:55:09 [root@ddcw21 ibd2sql_v1.0]#python test.py /data/mysql_3314/mysqldata/ibd2sql/t20240102_js.ibd
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (1, 'aa', '{"t1": 123}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (2, 'aa', '{"t1": "AAAAAAAAAAAAAAAAACBBBBBBBBBBBB"}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (3, 'aa', '{"a1": {"b1": "b1"}, "a2": "a6"}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (4, 'bb', '{"t1": "ABC"}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (5, 'bb', '{"t1": false}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (6, 'bb', '[{"AA": "AA"}, {"BB": "BB"}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (7, 'bb', '[{"AA": {"CC": "CC"}}, {"BB": "BB"}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (8, 'bb', '[{"AA": {"CC": {"DD": "DD"}}}, {"BB": "BB"}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (9, 'bb', '[{"AA": {"CC": {"DD": "DD"}}}, {"BB": "BB"}, {"FF": 2}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (10, 'bb', '[{"13": {"CC": {"DD": "DD"}}}, {"BB": "BB"}, {"FF": 2}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (11, 'bb', '{"aa": true, "bb": false, "cc": null}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (12, 'cc', '{"aa": 1, "bb": 2, "cc": 0}');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (13, 'cc', '[{"aa": 1}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (14, 'cc', '[{"13": {"CC": {"DD": "DD"}}}, {"BB": "BB"}, {"FF": 2}]');
REPLACE INTO `db2`.`t20240102_js`(`id`,`name`,`aa`) VALUES (15, 'cc', '{"a1": {"b1": "b1"}, "a2": "a6"}');
我这里做了schma替换, 所以要验证的话, 得再建一张表.
代码语言：sql复制Query OK, 1 row affected (0.00 sec)
(root@127.0.0.1) [(none)]> checksum table db2.t20240102_js, ibd2sql.t20240102_js;
---------------------- ------------
| Table | Checksum |
---------------------- ------------
| db2.t20240102_js | 3802174698 |
| ibd2sql.t20240102_js | 3802174698 |
---------------------- ------------
2 rows in set (0.00 sec)
(root@127.0.0.1) [(none)]>
总结
1. mysql的json是mysql层实现的, 所以字节序是小端 (innodb的基本上都是大端)
2. mysql插入json数据的时候是严格要求json格式的, 即外面是单引号, 里面是双引号. 而且布尔类型和null都是小写.
3. mysql的json类型是标准json类型, 所以使用json包处理的数据是可以直接写入mysql数据库的
参考: mysql源码 sql/json_binary.h 和 RFC7159
注; ibd2sql 1.0 版本 1月底发布, 新增支持debug功能,分区表,json等 和 当前的v0.3版本有丢丢(指尖宇宙.jpg)区别
mysql变长类型读取
mysql的变长类型读取和innodb的变长类型记录有区别(innodb是256*256=64K)
当第一字节 小于128字节时, 就使用1字节存储大小, 直接 读那1字节即可
当第一字节 大于等于 128时候 (即第一bit为1) 就使用2字节表示. 此时 数据为 第一字节 减去 128 (去掉第一bit) 然后加上第二字节*128 即为最终大小.
代码语言：python代码运行次数：0复制-----------------------------------------------------
| 1 bit flag | 7 bit data | if flag, 8 bit data*128 |
-----------------------------------------------------相关解析代码可以参考如下:
代码语言：python代码运行次数：0复制 def read_var(self,offset):
_s = int.from_bytes(self.bdata[offset:offset 1],'little')
size = 1
if _s & (1<<7):
size = 1
_s = self.bdata[offset:offset 2]
_t = int.from_bytes(_s[1:2],'little')*128 int.from_bytes(_s[:1],'little')-128
else:
_t = _s
return size,_t
附脚本
很多注释没有删, 调试的时候 有个t 写成了selt.t 导致满天的print 去找问题.... 后面也懒得删了. 方便后面接入debug
代码语言：python代码运行次数：0复制#@mysql sql/json_binary.h
import struct
import sys
_ = """
- -----------------
| JSON OBJECT/ARRAY |
- -----------------
|
-------------------------------------------------------------------------
| TYPE | ELEMENT_COUNT | KEY-ENTRY(if object) | VALUE-ENTRY | KEY | VALUE |
-------------------------------------------------------------------------
| | |
| | --------------
-------------------------- | | UTF8MB4 DATA |
| KEY-OFFSET | KEY-LENGTH | | --------------
-------------------------- |
|
--------------------------------
| TYPE | OFFSET/VALUE(if small) |
--------------------------------
small 2 bytes large 4 bytes
---------------------------------------------------
TYPE 1 byte
COUNT 2/4 bytes
SIZE 2/4 bytes
VALUE VALUE/OBJECT/ARRAY
---------------------------------------------------
---------------------------------------------------
OBJECT VALUE = KEY_ENTRY VALUE_ENTRY KEY VALUE #KEY肯定是字符串, 所以不需要记录数据类型
ARRAY VALUE = VALUE_ENTRY VALUE #不需要KEY
KEY_ENTRY = KEY_OFFSET(2/4bytes) KEY_LNGTH(2 bytes)
VALUE_ENTRY = TYPE(1byte) OFFSET(2/4 bytes)/VALUE (如果类型是int,literal之类的,就直接是值了, 否则就走OFFSET)
---------------------------------------------------
"""
# type ::=
# 0x00 | // small JSON object
# 0x01 | // large JSON object
# 0x02 | // small JSON array
# 0x03 | // large JSON array
# 0x04 | // literal (true/false/null)
# 0x05 | // int16
# 0x06 | // uint16
# 0x07 | // int32
# 0x08 | // uint32
# 0x09 | // int64
# 0x0a | // uint64
# 0x0b | // double
# 0x0c | // utf8mb4 string
# 0x0f // custom data (any MySQL data type)
# value ::=
# object |
# array |
# literal |
# number |
# string |
# custom-data
class jsonob(object):
def __init__(self,bdata,t):
"""
bdata = json data
t 类型 json类型
"""
self.bdata = bdata
self.t = t
self.offset = 0
self.ssize = 2 if self.t == 0x00 or self.t == 0x02 else 4
self._type = None
self._bdata = b''
#print("BEGIN JSON TO B, CURRENT TYPE:",self.t)
def read_key_entry(self):
"""
read key-entry
"""
#print("READ KEY ENTRY")
key_entry = []
for x in range(self.element_count):
key_offset = self.read_little()
key_length = self.read_little(2)
key_entry.append((key_offset,key_length))
self.key_entry = key_entry
def read_value_entry(self):
#print("READ VALUE ENTRY")
value_entry = []
for x in range(self.element_count):
t = self.read_little(1)
#print("t entry: type:",t)
data = None
if t < 0x04:
#print("READ VALUE ENTRY JSON object/array")
data = self.read_little()
elif t == 0x04: #literal
#print("READ VALUE ENTRY literal")
_data = self.read_little()
if _data == 1:
data = True
elif _data == 2:
data = False
elif _data == 0:
data = None
else:
data = ''
elif t >= 0x05 and t <= 0x0a: #inline data
#print("READ VALUE ENTRY Inline data for INT",t,0x05,0x0a)
data = self.read_inline_data(t)
elif t == 0x0b: #double
#print("READ VALUE ENTRY Double")
#data = struct.unpack('d',self.read(8))[0]
data = self.read_little()
elif t == 0x0c: #string
#print("READ DATA ENTRY STRING",self.offset)
data = self.read_little() #OFFSET
value_entry.append((t,data))
self.value_entry = value_entry
#print("VALUE ENTRY LIST ---------",self.value_entry)
def read_key(self):
#print("READ KEY")
key = []
for x in self.key_entry:
key.append(self.bdata[x[0]:x[0] x[1]].decode() )
self.key = key
def read_value(self):
#print("READ VALUE")
value = []
for x in self.value_entry:
#print("VALUE TYPE:xxxxxxx",x[0])
if x[0] == 0x0c: #字符串
_s,size = self.read_var(x[1])
#size = int.from_bytes(self.bdata[x[1]:x[1] 1],'little') #先都按1字节计算
value.append(self.bdata[x[1] _s:x[1] _s size].decode())
elif x[0] == 0x0b:
value.append(struct.unpack('d',self.bdata[x[1]:x[1] 8])[0])
elif x[0] <= 0x03: #json对象, 又递归
s = self.ssize
size = int.from_bytes(self.bdata[x[1] s: x[1] s s ], 'little')
data = self.bdata[x[1]:x[1] size]
_aa = jsonob(data,x[0])
value.append(_aa.init())
else:
value.append(x[1])
self.value = value
def read_var(self,offset):
"""
读mysql的varchar的 记录长度的大小, 范围字节数量和大小
如果第一bit是1 就表示要使用2字节表示:
后面1字节表示 使用有多少个128字节, 然后加上前面1字节(除了第一bit)的数据(0-127) 就是最终数据
-----------------------------------------------------
| 1 bit flag | 7 bit data | if flag, 8 bit data*128 |
-----------------------------------------------------
"""
_s = int.from_bytes(self.bdata[offset:offset 1],'little')
size = 1
if _s & (1<<7):
size = 1
_s = self.bdata[offset:offset 2]
_t = int.from_bytes(_s[1:2],'little')*128 int.from_bytes(_s[:1],'little')-128
else:
_t = _s
return size,_t
def init(self,):
#print(self.bdata)
self.element_count = self.read_little()
#print("ELEMENT COUNT:",self.element_count)
#print(self.read_little())
self._size = self.read_little()
#print(f"THIS OBJECT SIZE:",self._size, "ACTUAL SIZE:",len(self.bdata))
if self._size != len(self.bdata):
return None
#print("WILL INIT")
if self.t == 0x00 or self.t == 0x01: #object
self._type = "JSON Object"
#print(f"THIS TYPE IS {self._type}")
self.data = {}
self.read_key_entry()
self.read_value_entry()
self.read_key()
self.read_value()
self.data = {k:v for k,v in zip(self.key,self.value)}
elif self.t == 0x02 or self.t == 0x03: #array
self._type = "JSON Array"
#print(f"THIS TYPE IS {self._type}")
self.data = []
self.read_value_entry()
self.read_value()
self.data = self.value
return self.data
def read_little(self,ssize=None):
ssize = self.ssize if ssize is None else ssize
s = int.from_bytes(self.read(ssize),'little')
#print(f"READ LITTLE SIZE: {ssize} bytes bdata:{self._bdata} value:{s} ")
return s
def read(self,n):
_t = self.bdata[self.offset:self.offset n]
self.offset = n
self._bdata = _t
return _t
def _read_int(self,n):
data = self.read(n)
return int.from_bytes(data,'big')
def read_uint(self,n,is_unsigned=True):
_t = self._read_int(n)
_s = n*8 - 1
#print("read uint",self._bdata,_t,_s)
return (_t&((1<<_s)-1))-2**_s if _t < 2**_s and not is_unsigned else (_t&((1<<_s)-1))
def read_int(self,n):
return self.read_uint(n,False)
def read_inline_data(self,t):
n = 0
is_unsigned = True
#print("tread_inline_data TYPE:",t)
if t == 0x05: #int16
n = 2
elif t == 0x06: #uint16
n = 2
is_unsigned = True
elif t == 0x07: #int32
n = 4
elif t == 0x08: #uint32
n = 4
is_unsigned = True
elif t == 0x09: #int64
n = 8
elif t == 0x0a: #uint64
n = 8
is_unsigned = True
#return self.read_uint(n,is_unsigned)
signed = False if is_unsigned else True
rs = int.from_bytes(self.read(n),'little',signed=signed)
#print("tINLINE DATA:",rs)
return rs
