All geek questions in one place

Best MySQL data type for storing MD5 or NULL hash

I have a PHP application that stores all accounts regardless of whether they are active or not in the same table. The table has a column called "active" that is either NULL, which means the account is active, or contains an MD5 hash, which means that the account is inactive.

According to the Recommendation for efficient storage of md5 hashes in mysql , if a column always contains an MD5 hash and never has NULL, then BINARY (16) is preferred and CHAR (32) is the next best choice. Since most of my accounts are active and therefore most of the column values ​​will be NULL, should I use a different data type like VARCHAR (32)?

+6
source share
3 answers

It makes no sense to use VARCHAR. MD5 hashes are always 128-bit, so CHAR (32) contains a string of hexadecimal digits, or BINARY (16) contains a string of bytes after UNHEX () hexadecimal digits.

Using NULL is independent of the choice of data type. MySQL can store NULL instead of a string, either CHAR or VARCHAR. But in fact, in the standard InnoDB format, by default MySQL does not store NULL at all, it does not store anything for columns that are NULL.

Link: http://dev.mysql.com/doc/internals/en/innodb-field-contents.html

  • Useful notes about NULL:

    For the third row, I inserted NULL in FIELD2 and FIELD3. Therefore, in offsets of the beginning of the field, the upper bit is turned on for these fields (values ​​are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the line is shorter because NULLs do not take up space.

(my emphasis)

+12
source

In the MySQL source code, the BINARY type is defined in the / ctype -bin.c files.

This is similar to the default C ascii encoding converted to binary. This should happen faster than ascii_bin encoded CHAR (32).

Since it takes less time to write / read a binary file and it takes up less disk space in the indexes and memory and because the data type CHAR (32) is more than 16 bytes

If you want to use this you must use this php code

<?php md5 ( "password", true ); // true returns the binary what is 16 bytes long MySQl BINARY(16) ?>
0
source

You can use the php function md5 (); More efficient, and if someone can catch the data when sent to the database, it will already be encrypted.

-5
source

Source: https://habr.com/ru/post/954800/

More articles:


All Articles