php脚本l导出mysq的blob格式数据-hex和unhex的用法

前言

之前我们介绍过使用php脚本导出sql语句到测试服中的流程和注意点， 之前有个问题还没有解决的，就是mysql中blob类型数据是导不成功的。 这次找到了解决方法，这里记录一下。

什么是blob类型？

我们在【mysql常用数据类型中】介绍过了blob类型，这里也说一下： 在mysql中,blob是一个二进制的对象，它是一个可以存储大量数据的容器(如图片，音乐等等)，且能容纳不同大小的数据。 在mysql中有四种blob类型，他们的区别就是可以容纳的信息量不容分别是以下四种:

①tinyblob类型 最大能容纳255b的数据

②blob类型 最大能容纳65kb的

③mediumblob类型 最大能容纳16mb的数据

④longblob类型 最大能容纳4gb的数据

场景

我们的一个表有个字段的数据是存储了一些会员信息，这些信息是由几个字段组成，然后json_encode 并且压缩后存储在字段类型为blob的字段中，这些信息以blob格式存储在数据库中，直接用sql查询出来都是乱码，无法显示，不信你可以尝试查询下。

当我们导出成sql语句时，如果不做特殊处理，这些字段也会是乱码，会导致sql结构破坏掉，不能完整执行。

解决方案 找了下资料，发现mysql有两个函数，是可以格式化这些二进制数据的。 下面是两个函数的介绍

hex 和unhex 建议先看一下官方免费手册中的说明。

hex(nors)

if nors is a number, returns a string representation of the hexadecimal value of n, where n is a longlong (bigint) number. this is equivalent to conv(n,10,16).

翻译：如果nors是数字，则返回十六进制值n的字符串表示，其中n是longlong（bigint）数字。 这相当于conv（n，10,16）。

if nors is a string, returns a hexadecimal string representation of nors where each character in nors is converted to two hexadecimal digits. the inverse of this operation is performed by the unhex() function.

翻译：如果nors是字符串，则返回nors的十六进制字符串表示形式，其中nors中的每个字符都转换为两个十六进制数字。 此操作的反转由unhex（）函数执行。

1. mysql> select hex(255);
2. -> 'ff'
3. mysql> select 0x616263;
4. -> 'abc'
5. mysql> select hex('abc');
6. -> 616263

unhex(str)

performs the inverse operation of hex(str). that is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. the resulting characters are returned as a binary string.

翻译：执行hex（str）的逆操作。 也就是说，它将参数中的每对十六进制数字解释为数字，并将其转换为数字表示的字符。 结果字符作为二进制字符串返回。

1. mysql> select unhex('4d7953514c');
2. -> 'mysql'
3. mysql> select 0x4d7953514c;
4. -> 'mysql'
5. mysql> select unhex(hex('string'));
6. -> 'string'
7. mysql> select hex(unhex('1267'));
8. -> '1267'

the characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'a' .. 'f', 'a' .. 'f'. if unhex() encounters any nonhexadecimal digits in the argument, it returns null:

翻译：参数字符串中的字符必须是合法的十六进制数字：'0'..'9'，'a'..'f'，'a'..'f'。 如果unhex（）在参数中遇到任何非十六进制数字，则返回null：

1. mysql> select unhex('gg');
2. +-------------+
3. | unhex('gg') |
4. +-------------+
5. | null |
6. +-------------+

a null result can occur if the argument to unhex() is a binary column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. for example 'aa' is stored into a char(3) column as 'aa ' and retrieved as 'aa' (with the trailing pad space stripped), so unhex() for the column value returns 'a'. by contrast 'aa' is stored into a binary(3) column as 'aa\0' and retrieved as 'aa\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so unhex() for the column value returns null.

翻译：如果unhex（）的参数是binary列，则可能会出现null结果，因为在存储时会使用0x00字节填充值，但这些字节在检索时不会被剥离。 例如，'aa'作为'aa'存储在char（3）列中，并作为'aa'检索（尾随填充空间被剥离），因此列值的unhex（）返回'a'。 相比之下，'aa'作为'aa \ 0'存储在binary（3）列中，并作为'aa \ 0'检索（尾随填充0x00字节未被剥离）。 '\ 0'不是合法的十六进制数字，因此列值的unhex（）返回null。

综上，简单点理解就是

导出时采用hex函数读取数据，把二进制的数据转为16进制的字符串；

1. select hex(binfield) from testtable;
2. //查询出来的数据是一串可以展示的字符串，可以导出为sql语句，
3. //但是要恢复，要是用其逆转函数unhex将数据格式化导入

---

1. //导入时采用unhex函数，把16进制的字符串转为二进制的数据导入库中；
2. insert into testtable binfield values(unhex(@hexstr));
3. //这样格式化后，可以完整的将数据导进去。