Best datatype to store hexidecimal and hex characters in the database

Question

I'm using the Ethereum api. I want to store the information from the api into a mysql table.

The address data looks like:

0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be
0x1d80982502f3bb75654df13aa32bbd5ac9cab7d6
0xaf13bbdbe1ff53c2df7109a53c217320d2d76ee2
...

I've been using just varchar column for these characters. Are there a better data type? I'm thinking maybe varbinary but I don't know if there's any advantages. The disadvantage is that the sql code will be messier as I will have to HEX() and UNHEX().

Answer 1

For me no specific type exists, you need to choose between text and binary :

CHAR(40) : (charset is not important here)

• pros : simplicity
• cons : you need more space disk (~+100%)
• cons : you risk to store invalid non hexa data

BINARY(20) :

• pros : reduce space disk
• pros : you can't store invalid data
• cons: you need to convert (if you need see hexa value)

For me data consistency is the most important point : I prefer binary option.

• unhex('FF') == unhex('ff') but 'FF' <> 'ff'
• trying to store unhex('zz') raises an error, with text no error raised)

Moreover Hex/unHex are very simple function

Hint : You can store your data in BINARY column and create an hexa view to see easily hexa value.

Here is an example to store data in binary zone http://rextester.com/SEV11235

Answer 2

Advantage CHAR: simplicity.

Advantage BINARY: you save 22 bytes per row.

In my opinion, the complexity increase isn't worth the space saving unless you're looking at a minimum of million-row databases. Even then I probably wouldn't bother.

In either case, if you care that much you can save a byte or two (and possibly improve your DB structure optimization) by using a fixed-length column rather than variable.