没有必要用UTF-8存储16进制数据,采用UTF-8存储16进制数据不会增加磁盘空间的占用,但是当你使用排序(order by)、统计(group by)、隐式临时表(MySQL查询时自建的临时表)等的时候,需要耗费多达3倍的内存和硬盘空间,至少在MySQL上是这样的
所以在Mysql中存储16进制数据,用binary就好。但是,由于显示的字符集是utf8,所以看见的是乱码,于是要用hex()函数转化一下。
如果在导入sql脚本时遇到字符集无法识别的情况,在mysql命令行加入 “ --default-character-set=utf8 ”
-
For a string argument str, HEX() returns a hexadecimal string representation of str where each character instr is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function.
For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed byCONV(HEX(N),16,10).
mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc')); -> 'abc', 616263, 'abc' mysql> SELECT HEX(255), CONV(HEX(255),16,10); -> 'FF', 255