Convert binary string to bigint in MySQL?

mysql hash binary md5 bigint
Sean Owen picture Sean Owen · Aug 11, 2009 · Viewed 12.2k times · Source

I am attempting to hash a string to a 64-bit value (bigint) in MySQL. I am aware of the MD5() function, which returns a 128-bit hash as a binary string. I'd be happy to just take the bottom or top 64 bits of this result. However, I cannot figure out how to get from a binary string type to a numeric type of any sort. Any pointers?

Answer

Ken Keenan picture Ken Keenan · Aug 11, 2009

Use the CONV() function to convert the MD5 hash from base 16 to base 10 and CAST to convert it to a number:

select cast(conv(substring(md5(id), 1, 16), 16, 10) as unsigned integer) from SomeTable;

Related questions

What data type to use for hashed password field and what length?

I'm not sure how password hashing works (will be implementing it later), but need to create database schema now. I'm thinking of limiting passwords to 4-20 characters, but as I understand after encrypting hash string will be of different length. …

Read More
php mysqli_connect: authentication method unknown to the client [caching_sha2_password]

I am using php mysqli_connect for login to a MySQL database (all on localhost) <?php //DEFINE ('DB_USER', 'user2'); //DEFINE ('DB_PASSWORD', 'pass2'); DEFINE ('DB_USER', 'user1'); DEFINE ('DB_PASSWORD', 'pass1'); DEFINE ('DB_HOST', …

Read More
What column type/length should I use for storing a Bcrypt hashed password in a Database?

I want to store a hashed password (using BCrypt) in a database. What would be a good type for this, and which would be the correct length? Are passwords hashed with BCrypt always of same length? EDIT Example hash: $2a$10$…

Read More