存储MD5哈希或NULL的最佳MySQL数据类型

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

我有一个PHP应用程序,它存储所有帐户,无论它们是否在一个表中是活动的。该表有一个名为“active”的列,它可以是NULL,表示该帐户处于活动状态,或者包含MD5哈希,这意味着该帐户处于非活动状态。

According to Best practices for efficiently storing md5 hashes in mysql, if the column always contains a MD5 hash and never NULL, then BINARY(16) is preferred and CHAR(32) is the next best choice. Since most of my accounts are active and thus most of the column values will be NULL, am I better off using a different data type such as VARCHAR(32)?

根据在mysql中有效存储md5哈希的最佳实践,如果列始终包含MD5哈希并且永远不是NULL,则首选BINARY(16),并且CHAR(32)是下一个最佳选择。由于我的大多数帐户都是活动的,因此大多数列值都是NULL,我最好使用不同的数据类型,如VARCHAR(32)?

3 个解决方案

#1

There's no point to using VARCHAR. MD5 hashes are always 128-bit, so a CHAR(32) holds the string of hex digits, or BINARY(16) holds the string of bytes after you UNHEX() the hex digits.

使用VARCHAR毫无意义。 MD5哈希值始终为128位,因此CHAR(32)保存十六进制数字字符串,或者BINARY(16)保存UNHEX()十六进制数字后的字节字符串。

Using NULL is independent of data type choice. MySQL can store NULL in lieu of a string, either CHAR or VARCHAR. But in fact, in InnoDB's default row format, MySQL does not store NULLs at all, it stores nothing for columns that are NULL.

使用NULL与数据类型选择无关。 MySQL可以存储NULL来代替字符串,CHAR或VARCHAR。但事实上,在InnoDB的默认行格式中,MySQL根本不存储NULL,它不会为NULL列存储任何内容。

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

Helpful Notes About NULLs:

关于NULL的有用说明:

For the third row, I inserted NULLs in FIELD2 and FIELD3. Therefore in the Field Start Offsets the top bit is on for these fields (the values are 94 hexadecimal, 94 hexadecimal, instead of 14 hexadecimal, 14 hexadecimal). And the row is shorter because the NULLs take no space.

对于第三行,我在FIELD2和FIELD3中插入了NULL。因此,在字段起始偏移中,这些字段的顶部位是打开的(值为94十六进制,94十六进制,而不是14十六进制,14十六进制)。行更短,因为NULL不占用空间。

(emphasis mine)

#2

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

在文件strings / ctype-bin.c中的MySQL源代码中定义了BINARY类型。

This looks like the default C ascii based charset converted into binary. This should in thoery be faster then the CHAR(32) with ascii_bin charset.

这看起来像默认的基于C ascii的charset转换为二进制。这应该比使用ascii_bin字符集的CHAR(32)快得多。

Because less time is needed to write / read the binary and it takes less diskspace in indexes and memory and because the CHAR(32) datatype is 16 bytes larger

因为写入/读取二进制文件所需的时间更少,索引和内存中的磁盘空间更少,因为CHAR(32)数据类型大16字节

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

如果你想使用它,你应该使用这个PHP代码

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

#3

-5

You can use the php function md5(); Is more efficient and if anyone can catch the data when you sent to database, that will be already crypted.

你可以使用php函数md5();效率更高,如果有人在发送到数据库时可以捕获数据,那么这些数据已经被加密了。

#1