[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Storing Hex values

Posted on 2010-09-08
4
Medium Priority
?
502 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33625885
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.
0
 
LVL 3

Expert Comment

by:gremwell
ID: 33626175
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.
0
 

Author Comment

by:tonelm54
ID: 33627281
Is there a way to insert Hex into a binary field, and return hex?

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

And then:-
select 2hex(`blobData`) from `myTable`
0
 
LVL 8

Accepted Solution

by:
wolfgang_93 earned 2000 total points
ID: 33631432
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)



0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question