Question 254502 in Mysql Question posted in
The official MySQL documentation can be found here.

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));

  1. Inserted same text for both the columns.

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

  1. 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.
enter image description here

Tags:

2

Answers


  1. 0

    You probably want VARBINARY instead of BINARY.

    https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html

    When BINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is 0x00 (the zero byte). Values are right-padded with 0x00 for inserts, and no trailing bytes are removed for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.

    For VARBINARY, there is no padding for inserts and no bytes are stripped for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.

    You’re comparing 3 against 3. If you look closely, you can see this in your output screenshot; the X2 column is much wider than it has to be, whereas all the other columns fit their (visible) contents.

    Login or Signup to reply.
  2. 0

    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 because BINARY(10) 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search