mysql中用HEX和UNHEX函数处理二进制数据的导入导出&&mysql的hex()和unhex()函数

在一次mysql的数据备份恢复的操作中，碰到一个问题：在使用sqlyog进行数据备份后，然后将数据在另外一个服务器上进行恢复，由于其中一个表中有varbinary字段，导致“’”分隔符被吞噬(由于二进制编码的问题，引号分隔符被当作了数据的一部分)，从而使得数据不能被正常导入。

采用了一些文本工具进行处理，都没有成功，有的虽然可以识别，但会自动的修改了其中的二进制编码的数据，有的则会插入其他的二进制的数据，导致仍然无法读取。

于是考虑其他的手段：读取数据并拼写sql语句，然后进行导入。具体方法为：

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

select HEX(binField) from testTable;

(2)导入时采用UNHEX函数，把16进制的字符串转为二进制的数据导入库中；

insert into testTable binField values(UNHEX(@hexstr));

有以下的代码可以演示HEX和UNHEX的功能：

SELECT HEX('this is a test str')

查询的结果为：

746869732069732061207465737420737472

SELECT UNHEX('746869732069732061207465737420737472')

查询的结果为：this is a test str

还可以直接读取16进制的字符，在字符串前加0x前导：

SELECT 0x746869732069732061207465737420737472

查询结果为：this is a test str

mysql的hex()和unhex()函数

select hex('love');

"6C6F7665"

select unhex(hex('love'));

或者

select x'6C6F7665';

或者

select '0x6C6F7665';

"love"

说明：

十六进制值

MySQL支持十六进制值。在数字上下文中，十六进制数如同整数(64位精度)。在字符串上下文，如同二进制字符串，每对十六进制数字被转换为一个字符：

mysql>SELECT x"4D7953514C"；

-> "MySQL"

mysql> SELECT 0xa+0；

-> 10

mysql> SELECT 0x5061756c；

-> "Paul"

十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST(…AS UNSIGNED)：

mysql> SELECT 0×41，CAST(0×41 AS UNSIGNED)；

-> "A"，65

0x语法基于ODBC。十六进制字符串通常用于ODBC以便为BLOB列提供值。x’hexstring’语法基于标准SQL。

可以用HEX()函数将一个字符串或数字转换为十六进制格式的字符串：

mysql> SELECT HEX('cat')；

-> "636174"

mysql> SELECT 0×636174；

-> "cat"