Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What's the best data type for store fix length data such as

  • MD5 password 1f3870be274f6c49b3e31a0c6728957f
  • HEX Ccolor f2a709

They don't need character sets. All in absolute [a-z0-9]{6} for color, [a-z0-9]{32} for md5

I use BINARY data type for that. Is this a good choice or not ?

share|improve this question
add comment

1 Answer 1

Storing the literal values in a BINARY column is an efficient choice but only of you want comparisons to be case-sensitive such that 'a' != 'A'.

Given the same data (essentially hexadecimal digits only), if you wanted case insensitivity then you would want to use latin1 where 'a' = 'A'.

Both of these will use 1 byte per character.

However, you can use half a byte per character if you are willing to pack the data on the way in and unpack it on the way out. For HTML color codes, use a 3 byte column BINARY (3).

INSERT INTO html (color) VALUES (UNHEX('f2a709'));

SELECT HEX(color) AS color FROM html;
# would return 'F2A709' 
# use LOWER() around HEX () if you want the lowercase equivalent returned.

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html

share|improve this answer
    
i use binary for hex color, is any performance issue between char(6) and binary(6) ? char need to have process character set layer. –  sweb Jul 24 '13 at 11:49
    
There's a theoretical performance difference between the two because the BINARY data type does not require character set handling, but I would speculate that the difference would be so small that it would be difficult to measure. –  Michael - sqlbot Jul 24 '13 at 21:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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