Mysql中Blob类型字段的插入、查看、截取和拼接

前言

本来并没有太注意到Blob这个类型,在游戏的开发中存储数据常常使用这个类型,这里的使用其实是“机械”的使用,因为应用程序和Mysql数据库之间的逻辑已经封装好了,我只要把对应的数据扔到接口里就行了,可是最近发生了点问题,所以决定深入研究一下Blob类型的操作方法。

问题是这样的,由于应用程序的一个逻辑错误,导致Mysql数据库中有一个Blob类型的字段的前几个字节被写入了错误的值,当然这个问题,我们可以通过应用程序处理,在逻辑中读出Blob字段的值,修改为正确值以后再写回到数据库中,可是这样有些麻烦,并且这些处理逻辑与业务无关。

为了更方便的解决问题,决定使用SQL语句直接修改数据库,将错误的数据恢复正常,因为之前没有直接用SQL修改过Blob类型的字段,所以多花了一点时间用来测试,现在把整个过程记录一下,方便下次直接操作。

在整个处理的过程中用到了查看、截取和拼接三种操作,为了让例子看起来更加精炼,我们把插入也测一下,然后创造出我们想要的精简后的数据,首先还是来看一下数据库版本。

数据库版本

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

创建测试表

测试的表格结构很简单,只需要带有一个Blob类型的字段就尅可以了,为了操作方便再添加一个id,操作的SQL语句如下:

1
2
3
4
5
6
7
8
9
10
11
mysql> create table bloboperation(id int, data blob);
Query OK, 0 rows affected (0.36 sec)

mysql> desc bloboperation;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| data | blob | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.07 sec)

插入数据

因为知道Blob是二进制数据,所以首先插入两条用十六进制表示的字节串试一下,提示成功插入,插入两条一样的数据是为了之后修改的时候对比方便:

1
2
3
4
5
mysql> insert into bloboperation values(1, 0x01020304FFFFFFFF0000000CAACB0000);
Query OK, 1 row affected (0.06 sec)

mysql> insert into bloboperation values(2, 0x01020304FFFFFFFF0000000CAACB0000);
Query OK, 1 row affected (0.04 sec)

插入字节串没有问题,那插入字符串和数字看看会有什么结果,测试语句如下,最后发现均可以正常插入:

1
2
3
4
5
mysql> insert into bloboperation values(3, 'hellworld');
Query OK, 1 row affected (0.04 sec)

mysql> insert into bloboperation values(4, 0);
Query OK, 1 row affected (0.03 sec)

查看数据

上面插入了4条不同类型的数据都成功了,我们简单来查一下看看数据和我们插入的是否一样:

1
2
3
4
5
6
7
8
9
10
mysql> select * from bloboperation;
+------+------------------+
| id | data |
+------+------------------+
| 1 | ÿÿÿÿ ? |
| 2 | ÿÿÿÿ ? |
| 3 | hellworld |
| 4 | 0 |
+------+------------------+
4 rows in set (0.00 sec)

这究竟是什么鬼,除了第3、4条和我们插入的数据一样,前两条数据看起来和我们之前插入数据时完全不一样,其实这时候需要用到一个hex()函数来看Blob类型的数据,查询结果如下:

1
2
3
4
5
6
7
8
9
10
mysql> select id,hex(data),length(data) from bloboperation;
+------+----------------------------------+--------------+
| id | hex(data) | length(data) |
+------+----------------------------------+--------------+
| 1 | 01020304FFFFFFFF0000000CAACB0000 | 16 |
| 2 | 01020304FFFFFFFF0000000CAACB0000 | 16 |
| 3 | 68656C6C776F726C64 | 9 |
| 4 | 30 | 1 |
+------+----------------------------------+--------------+
4 rows in set (0.02 sec)

这回前两条数据正常了,可是后两条数据为什么又看起来不一样了呢,如果你产生了这样的疑问,就需要好好理解一下内存值和表现值的对应关系了,第4条插入语句的中数据0,实际上是被当做字符串存储的,而字符’0’的ASCII码是十进制的48,表示成十六进制就是0x30,也就是上面查到的这样,同理这个打错了的字符串’hellworld’也是这样存储的。

截取数据

本来以为截取数据需要一个特别的函数,没想到用的是字符串截取函数substring(str,startpos,length),第一个参数是需要截取的字符串或字节串,第二个参数起始位置从1开始,第三个参数就是截取的长度。

以第一条数据为例,截取第4到第8个一共5个字节,测试如下:

1
2
3
4
5
6
7
mysql> select id,hex(substring(data,4,5)) from bloboperation where id=1;
+------+--------------------------+
| id | hex(substring(data,4,5)) |
+------+--------------------------+
| 1 | 04FFFFFFFF |
+------+--------------------------+
1 row in set (0.00 sec)

拼接数据

看到上一个函数之后,你应该有所察觉,这个Blob类型的数据处理起来并不麻烦,那么拼接函数会不会用的是concat()这个处理字符串的函数呢?恭喜你,答对了,就是使用这个函数,我们来把前四个字节和最后四个字节拼接到一起,测试如下:

1
2
3
4
5
6
7
mysql> select id,hex(concat(substring(data,1,4),substring(data,13,4))) from bloboperation where id=1;
+------+-------------------------------------------------------+
| id | hex(concat(substring(data,1,4),substring(data,13,4))) |
+------+-------------------------------------------------------+
| 1 | 01020304AACB0000 |
+------+-------------------------------------------------------+
1 row in set (0.00 sec)

进制转换

我们看到id为1的数据有16个字节,实际上在应用程序的内存中对应了4个int类型,每个int类型占用四个字节,为了修改数据,我们需要知道原数据在程序中代表的数字是多少,这就用到进制转换函数conv,可以先进行一个简单转换,16进制转10进制的例子:

1
2
3
4
5
6
7
mysql> select conv('FF',16,10);
+------------------+
| conv('FF',16,10) |
+------------------+
| 255 |
+------------------+
1 row in set (0.00 sec)

通过上面的转换十六进制的FF被转换成了十进制的255,应用到Blob字段也是一样,我们看下id为1的数据第一个int保存的数据是多少:

1
2
3
4
5
6
7
8
mysql> select id,conv(hex(concat(substring(data,4,1),substring(data,3,1),substring(data,2,1),
substring(data,1,1))),16,10) as firstint from bloboperation where id=1;
+------+----------+
| id | firstint |
+------+----------+
| 1 | 67305985 |
+------+----------+
1 row in set (0.01 sec)

现在我们就得到了第一个int类型的值是67305985,可能有的同学会有疑惑,为什么不直接截取前4个字节,而要一个一个的拼接呢?这就涉及到大端数据和小端数据知识了,我们使用的PC机通常是小端的,数据的地位存储在低内存,数据的高位存储在高内存,所以需要把四个字节反过来拼接在一起再进行转换。

实际处理

理解了上面的知识,就可以处理之前遇到的问题了,假设这16个字节代表的4个int类型分别是A,B,C,D,需要处理的问题是当变量D的值是52138的时候把变量B清0。

通过分析判断D变量的值之前有类似的,按照刚才第一个变量那样处理,把B变量清零可以通过A变量拼接0,然后再拼接C变量和D变量得到,具体的执行语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> update bloboperation set data=concat(substring(data,1,4), 0x00000000, substring(data,9,8))
where
conv(
hex(concat(substring(data,16,1),substring(data,15,1),substring(data,14,1),substring(data,13,1))),
16,10)=52138
and id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select id,hex(data),length(data) from bloboperation;
+------+----------------------------------+--------------+
| id | hex(data) | length(data) |
+------+----------------------------------+--------------+
| 1 | 01020304000000000000000CAACB0000 | 16 |
| 2 | 01020304FFFFFFFF0000000CAACB0000 | 16 |
| 3 | 68656C6C776F726C64 | 9 |
| 4 | 30 | 1 |
+------+----------------------------------+--------------+
4 rows in set (0.00 sec)

执行更新后查询发现,第5到8个字节对应的变量B确实被清0了,也就是我们的目标达到了。

总结

  • Blob类型字段的处理常用到的函数hex()substring()concat()conv()

  • 注意conv()函数的第一个参数需要是十六进制表示的字符串,不需要带0x

Albert Shi wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客