Search code examples
mysqlvarcharethereumvarbinary

Best datatype to store hexidecimal and hex characters in the database

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().

Solution

  • 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