为什么mysql HEX会改变二进制列的情况？

Question

The following query changes the case of the md5 column from lower to upper. The MD5 value was provided via PHP's md5() function, and I am using it in a HTML link to send $_GET data to the server. Why does the case change? Is one case more proper than the other? I understand I can deal with it using LOWER().

http://sqlfiddle.com/#!2/414c8/1

CREATE TABLE myTable (
  id INT NOT NULL AUTO_INCREMENT,md5 BINARY(16),
  PRIMARY KEY (id) )
ENGINE = InnoDB;

INSERT INTO myTable(md5)VALUES(UNHEX("06fcf5b90b916bdc533e2badec396b90"));

SELECT id,HEX(md5) FROM myTable;

ID  HEX(MD5)
1   06FCF5B90B916BDC533E2BADEC396B90

Answer 1

It doesn't change case. HEX() just returns the uppercase letters. The way the value is stored has no letters in it at all - it's the actual binary representation. There is no way to preserve the "case" in that column.

You can always make the column wider and store the text itself (without HEX/UNHEX) if you don't want to do the conversion later.