How is the MySQL md5 function introduced?
I am having trouble understanding how input to an md5 function is handled in MySQL 4.1.22. Basically, I cannot recreate the md5sum of a specific combination of values for comparison. I'm guessing it has something to do with the format of the input.
I set up a table with two double columns (direction and height) + a third to store the md5 sum.
With the script setup, I add data to the direction and height fields + create a checksum using the following syntax:
insert into polygons (
direction,
elevation,
md5sum
)
values (
(select radians(20.0)),
(select radians(30.0)),
( md5( (select radians(20.0)) + (select radians(20.0)) ) )
)
which ends like: 0.349065850398866, 0.523598775598299, '0c2cd2c2a9fe40305c4e3bd991812df5'
Later I compare the stored md5sum with the recently calculated one, the newly calculated one is generated with md5('0.349065850398866' + '0.523598775598299')
and I get the following checksum:
'8c958bcf912664d6d27c50f1034bdf34'
If I change the last decimal number in the provided "line" from 9 to 8 0.523598775598298
, I get the same checksum as previously '0c2cd2c2a9fe40305c4e3bd991812df5'
. Any value of the last decimal place between 8 and 0 gives the same checksum.
Using BINARY, (md5( (select BINARY(radians(20.0))) + (select BINARY(radians(20.0))) )
in a custom script creates the same checksum as the original "runtime calculation"
It's worth noting that the original method works for all the other lines I have (55)
I am assuming that I am using the function in a somewhat strange way, but I am not sure about the best way, so in order to find the best way I feel like I need to understand why the current is not working.
source to share
The two numbers you add are stored in binary form, but displayed in decimal form. There is no guarantee that you will get the exact same number if you return the decimal form to the machine.
In this case, this addition gives a slightly different result, which gives a completely different MD5 sum:
mysql> select radians(20.0) + radians(30.0), '0.349065850398866' + '0.523598775598299';
+-------------------------------+-------------------------------------------+
| radians(20.0) + radians(30.0) | '0.349065850398866' + '0.523598775598299' |
+-------------------------------+-------------------------------------------+
| 0.87266462599716 | 0.87266462599717 |
+-------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
If you want to consistently get the same results, you need to store results radians(20.0)
and radians(30.0)
variables somewhere, never relying on their printed representations.
source to share
The radian output (20.0) is computed with more digits than the printed output shows. When the result is passed to the md5 function, the full non-truncated value is used, whereas the printed value will only show a limited number of digits. Thus, it is not the same value that is passed to the md5 function in two cases.
source to share