（32）二元之间差（16）和CHAR在数据库中存储的MD5时 [英] Difference between BINARY(16) and CHAR(32) when storing MD5 in database

问题描述

基于各种建议，如<α

href=\"http://stackoverflow.com/questions/247304/mysql-what-data-type-to-use-for-hashed-password-field-and-what-length\">MySQL:使用什么数据类型为哈希密码字段长度是什么？中，我可以存储为MD5或者CHAR（32）或者BINARY（16）。但是，当我这样做使用二进制（16），存储的值是相同的CHAR的前16个字符（32）保存结果，以及在 SELECT MD5（）的结果。什么是后来的16个字符的意义，是他们在二进制列缺少presence而导致数据丢失？

  CREATE TABLE测试（ID INT NOT NULL AUTO_INCREMENT，价值VARCHAR（6），md5_char CHAR（32）NOT NULL，md5_binary BINARY（16）NOT NULL，PRIMARY KEY（ID）） ENGINE = InnoDB的;
INSERT INTO测试（价值，md5_char，md5_binary）VALUES（，MD5（），MD5（）一个！一！一个！）;
INSERT INTO测试（价值，md5_char，md5_binary）VALUES（两％，MD5（两％），MD5（两％））;
INSERT INTO测试（价值，md5_char，md5_binary）VALUES（三〜，MD5（三〜），MD5（三〜））;
SELECT值，MD5（值），md5_char，md5_binary FROM试验;
DROP TABLE测试;+ -------- + ---------------------------------- + ----- ----------------------------- + ------------------ +
|值| MD5（值）| md5_char | md5_binary |
+ -------- + ---------------------------------- + ----- ----------------------------- + ------------------ +
|一！ | 633c8403325f1cf963809e6eb224d77e | 633c8403325f1cf963809e6eb224d77e | 633c8403325f1cf9 |
| 2％| 48bbec047b4451a2018e0f652807b7d0 | 48bbec047b4451a2018e0f652807b7d0 | 48bbec047b4451a2 |
| 3〜| fee453bb4eb68dcdfee07575e75c8cc5 | fee453bb4eb68dcdfee07575e75c8cc5 | fee453bb4eb68dcd |
+ -------- + ---------------------------------- + ----- ----------------------------- + ------------------ +
 




解决方案

目前正在使用的时候失去了校验和的一半 BINARY（16）。当你存储在的MD5校验和BINARY（16）你应该把它存为二进制数据的不的连接以十六进制codeD。这就是：

  INSERT INTO测试（md5_binary）VALUES（UNHEX（MD5（一！）））;
 

可以，如果你想眼球再次使用HEX功能EN code将其为十六进制数与另一个校验和进行比较吧：

  SELECT HEX（md5_binary）FROM试验;
 

使用二进制存储校验代替十六进制文本的好处是，一半的存储需要。

Based on various recommendations such as MySQL: what data type to use for hashed password field and what length?, I could store md5 as either CHAR(32) or BINARY(16). But when I do so using BINARY(16), the stored value is identical to the first 16 characters of the CHAR(32) stored results as well as the first 16 characters of the SELECT MD5() results. What are the significance of the later 16 characters, and is their lack of presence in the binary column resulting in lost data?

CREATE  TABLE test (id INT NOT NULL AUTO_INCREMENT, value VARCHAR(6), md5_char CHAR(32) NOT NULL, md5_binary BINARY(16) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;
INSERT INTO test(value,md5_char,md5_binary) VALUES("one!",md5("one!"),md5("one!"));
INSERT INTO test(value,md5_char,md5_binary) VALUES("two%",md5("two%"),md5("two%"));
INSERT INTO test(value,md5_char,md5_binary) VALUES("three~",md5("three~"),md5("three~"));
SELECT value,md5(value),md5_char,md5_binary FROM test;
DROP TABLE test;

+--------+----------------------------------+----------------------------------+------------------+
| value  | md5(value)                       | md5_char                         | md5_binary       |
+--------+----------------------------------+----------------------------------+------------------+
| one!   | 633c8403325f1cf963809e6eb224d77e | 633c8403325f1cf963809e6eb224d77e | 633c8403325f1cf9 |
| two%   | 48bbec047b4451a2018e0f652807b7d0 | 48bbec047b4451a2018e0f652807b7d0 | 48bbec047b4451a2 |
| three~ | fee453bb4eb68dcdfee07575e75c8cc5 | fee453bb4eb68dcdfee07575e75c8cc5 | fee453bb4eb68dcd |
+--------+----------------------------------+----------------------------------+------------------+

解决方案

Currently you are losing half of the checksum when using BINARY(16). When you store an MD5 checksum in BINARY(16) you should store it as binary data, not encoded in hexadecimal. That is:

INSERT INTO test (md5_binary) VALUES(UNHEX(md5("one!")));

You can use the HEX function to encode it into hex again if you want to eye-ball compare it with another checksum:

SELECT HEX(md5_binary) FROM test;

The benefit of using BINARY to store the checksum instead of hexadecimal text is that half the storage is needed.

这篇关于（32）二元之间差（16）和CHAR在数据库中存储的MD5时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！