mysql - SHA() method gives different result for same value of binary type with different sizes

SHA() method giving different result for same value of binary with different sizes.

1. Created a table, columns with X1 binary(1), X2 binary(10) size.

CREATE TABLE TEST_binary10_2 ( PK bigint NOT NULL, VERSION int NOT NULL, X1 binary(1) DEFAULT NULL, X2 binary(10) DEFAULT NULL, PRIMARY KEY (PK));

2. Inserted same text for both the columns.

insert into TEST_binary10_2 values(2, 1, 3, 3);

3. Tried to retrieve SHA() for both, it returns different value even though it is same value.

SELECT PK, X1, X2, SHA(X1), SHA(X2) FROM TEST_binary10_2;

Result: you can observe SHA(X1) and SHA(X2) is different even though X1, and X2 contains same string. I want to know the reason and a solution.

Try this:

mysql> select hex(x1), hex(x2) from test_binary10_2;
+---------+----------------------+
| hex(x1) | hex(x2)              |
+---------+----------------------+
| 33      | 33000000000000000000 |
+---------+----------------------+

This shows that the two binary strings are not the same. They have different lengths. The latter column is bound to be a 10-byte string of bytes, because the BINARY type is fixed length, and it will pad with zeroes if necessary.