• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

Storing Hex values

Good morning,
I have a collection of HEX values, which are 36 characters long I need to store efficently in a database.

My problem is I dont know what to use. If I use VARCHAR(36) then it seems to waste a lot of space. If I store it as Blobs I cant index the values.

The table has 2 coloums, one being a bigint with autonumbering, and the other being the field with the Hex values in.

Any suggestions that I can use to optimise my table would be appriciated.

Thank you
1 Solution
If they are ALWAYS going to be 36-char, then BINARY(18) would be most efficient*.
Otherwise VARBINARY(18) for varying lengths up to hex(36).

*Storage-wise: Each BINARY byte will hold the equivalent data of 2 HEX values, but you need some processing to encode/decode between hex and binary.
Note that to have an index BLOB and BINARY fields you need to specify a prefix length.

"Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length  characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns"

See http://dev.mysql.com/doc/refman/5.0/en/create-index.html for more details.
tonelm54Author Commented:
Is there a way to insert Hex into a binary field, and return hex?

Insert into myTable (`blobData`) VALUES (Hex2Bin(0x3451346342456));

And then:-
select 2hex(`blobData`) from `myTable`
This worked for me for MySQL version 5.0 :

mysql> create table fubar (aa binary(18));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into fubar values(0xF1E2D3C4B5010203040506070809A1A2B3C4);
Query OK, 1 row affected (0.01 sec)

mysql> select hex(aa) from fubar;
| hex(aa)                              |
| F1E2D3C4B5010203040506070809A1A2B3C4 |
1 row in set (0.00 sec)


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now