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语句如下：

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是二进制数据，所以首先插入两条用十六进制表示的字节串试一下，提示成功插入，插入两条一样的数据是为了之后修改的时候对比方便：

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)

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

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条不同类型的数据都成功了，我们简单来查一下看看数据和我们插入的是否一样：

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

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

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个字节，测试如下：

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()这个处理字符串的函数呢？恭喜你，答对了，就是使用这个函数，我们来把前四个字节和最后四个字节拼接到一起，测试如下：

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进制的例子：

mysql> select conv('FF',16,10);
+------------------+
| conv('FF',16,10) |
+------------------+
| 255              |
+------------------+
1 row in set (0.00 sec)

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

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变量得到，具体的执行语句如下：

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