Storing SHA1 hash values in MySQL

by

I would use VARCHAR for variable length data, but not with fixed length data. Because a SHA-1 value is always 160 bit long, the VARCHAR would just waste an additional byte for the length of the fixed-length field.

And I also wouldn’t store the value the SHA1 is returning. Because it uses just 4 bit per character and thus would need 160/4 = 40 characters. But if you use 8 bit per character, you would only need a 160/8 = 20 character long field.

So I recommend you to use BINARY(20) and the UNHEX function to convert the SHA1 value to binary.

I compared storage requirements for BINARY(20) and CHAR(40).

CREATE TABLE `binary` (
    `id` int unsigned auto_increment primary key,
    `password` binary(20) not null
);
CREATE TABLE `char` (
    `id` int unsigned auto_increment primary key,
    `password` char(40) not null
);

With million of records binary(20) takes 44.56M, while char(40) takes 64.57M.
InnoDB engine.

More Related Contents:

  1. Cakephp Career Database Design
  2. How to store multiple options in a single table?
  3. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  4. What column type/length should I use for storing a Bcrypt hashed password in a Database?
  5. What data type to use for hashed password field and what length?
  6. What does character set and collation mean exactly?
  7. How to pivot a MySQL entity-attribute-value schema
  8. Implementing Comments and Likes in database
  9. MySQL: multiple tables or one table with many columns? [closed]
  10. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  11. MySQL – Conditional Foreign Key Constraints
  12. Auto Generate Database Diagram MySQL [closed]
  13. Unique constraint that allows empty values in MySQL
  14. Entity-Attribute-Value Table Design
  15. In SQL, is it OK for two tables to refer to each other?
  16. MySQL ENUM type vs join tables
  17. Relational database design multiple user types
  18. Modeling Product Variants
  19. Facebook like notifications tracking (DB Design)
  20. How to design a multi tenant mysql database
  21. DOs and DONTs for Indexes [closed]
  22. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  23. Difference between two table structure
  24. Proper database model for a user feedback system (an interesting case)
  25. When to use a left outer join?
  26. how to partition a table by datetime column?
  27. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  28. Should I always prefer MySQL InnoDB over MyISAM?
  29. MySQL Relationships
  30. Inventory management with stock options

Leave a Comment