0

In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it.

The sha algorithm produce a binary stream out of the box. I can transform it hexadecimal or base64 before storing it. I wonder if it is convenient or necessary for my needs

  • Double transform bin --> hex --> b64 is just a waste of resources. Use the fixed-size BINARY(32) column for storing the data. It has no collation so no overhead for processing and also it is indexed way better that collated charsets. – Kondybas Jan 2 at 14:06
2

If you are starting with hex, use UNHEX() when storing and HEX() when fetching. Use BINARY(32) for the datatype. This occupies a constant 32 bytes.

If you are starting with binary, then be careful when escaping to store/load. Probably best to do a double convert each way.

Be sure to have lots of RAM. Once the index becomes bigger than the buffer_pool, the system will slow down because of the randomness of sha256 (or any other hash or uuid).

If you go with Base64, then use CHAR(43) COLLATE ascii_bin, which will be a constant 43 bytes, and will be case-sensitive.

256 is gross overkill. If you have about 2^85 entries, there is only one chance in 2^85 of having any hash collisions. For a mere MD5, those numbers are 9 trillion.

0

According to this answer https://stackoverflow.com/questions/10979100/how-to-store-binary-data-in-mysql

The recommendation would be to store it as a Base64 encoded string.

Short pieces of binary data, such as password hashes, work very well by simply base64-encoding them and storing the resulting string as a VARCHAR

  • as sha256 is fixed length why not using CHAR? CHAR(44) as explained here? – masciugo Jan 2 at 13:56
  • @masciugo BINARY(32) even better! – Kondybas Jan 2 at 14:07
  • @Kondybas thank you but I may need to do some manual searches/checks on the DB so, if there are not so many drawbacks, I would prefer using readable CHARs – masciugo Jan 2 at 15:08
  • @masciugo You can convert the values bin->char and char->bin right in the query and keep the table in the most efficient and compact representation. Hashing is intended to speed up the search so it would be reasonable to keep it as fast as possible. – Kondybas Jan 2 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.