MySql and C# hashes don't match while converting them to BIGINT

Question

My MySql tables need non-cryptography hashing so I can query the data faster. MySql database Hash function and C# application must generate the same hash for the given value which it does but only if I keep it to string. I want to convert them to BIGINT so I can avoid the overhead of string comparison. I know that Sha256 is cryptographic hash function but at least MySql and C# generates the same hash string for given input, I don't mind using it for non-cryptographic use. I have tried other online available hash algorithms like MurmurHash3 X86 but with hash collisions. Any help would be appreciated. Thanks!

MySql query:

SELECT SHA2('MyString', 256) AS Sha256, CONV(RIGHT(SHA2('MyString',256), 16), 16, 10) AS BIGINT_Sha256, MD5('MyString') AS MD_5, CONV(RIGHT(MD5('MyString'), 16), 16, 10) AS BIGINT_MD5;

C# Code:

static void Main(string[] args)
    {
        using (var sha256 = SHA256.Create())
        {
            var hashBytes = sha256.ComputeHash(Encoding.ASCII.GetBytes("MyString"));
            var hash = BitConverter.ToString(hashBytes).Replace("-", "").ToLower();

            Console.WriteLine(hash);
        }

        using (var sha256 = SHA256.Create())
        {
            var hashBytes = sha256.ComputeHash(Encoding.ASCII.GetBytes("MyString"));
            var hash = BitConverter.ToInt64(hashBytes, 0);

            Console.WriteLine(hash);
        }

        using (var md5 = MD5.Create())
        {
            var hashBytes = md5.ComputeHash(Encoding.ASCII.GetBytes("MyString"));
            var hash = BitConverter.ToString(hashBytes).Replace("-", "").ToLower();

            Console.WriteLine(hash);
        }

        using (var md5 = MD5.Create())
        {
            var hashBytes = md5.ComputeHash(Encoding.ASCII.GetBytes("MyString"));
            var hash = BitConverter.ToInt64(hashBytes, 0);

            Console.WriteLine(hash);
        }

        Console.ReadLine();
    }

MySql Result:

C# Result:

Where do you convert to a BigInteger in C#? Can you post that part of the code. — Luke Joshua Park
@LukePark this line [var hash = BitConverter.ToInt64(hashBytes, 0);] in my C# code converts to BigInteger. — Nishith Shah
No, it doesn't. That converts to a 64-bit integer. Your SHA256 hash is, as the name suggests, 256 bits in length. — Luke Joshua Park

Nishith Shah Nishith Shah · Accepted Answer · 2017-04-21T19:31:33

This answer helped me to solve this issue.

Rather than using the RIGHT 16 characters, I changed it to LEFT on MySql side.

MySql query:

SET @Value = 'MyString';
SELECT 
  SHA2(@Value, 256) AS Sha256, 
  CAST(CONV(LEFT(SHA2(@Value,256), 16), 16, 10) AS INT) AS BIGINT_Sha256, 
  MD5(@Value) AS MD_5, 
  CAST(CONV(LEFT(MD5(@Value), 16), 16, 10) AS INT) AS BIGINT_MD5;

C# Code:

static void Main(string[] args)
    {
        var value = "MyString";

        using (var sha256 = SHA256.Create())
        {
            var hashBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(value));
            var hash = BitConverter.ToString(hashBytes).Replace("-", "").ToLower();

            Console.WriteLine(hash);
        }

        using (var sha256 = SHA256.Create())
        {
            var hashBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(value));
            var hash = BitConverter.ToInt64(hashBytes.Take(8).ToArray(), 0);
            hash = IPAddress.HostToNetworkOrder(hash);

            Console.WriteLine(hash);
        }

        using (var md5 = MD5.Create())
        {
            var hashBytes = md5.ComputeHash(Encoding.UTF8.GetBytes(value));
            var hash = BitConverter.ToString(hashBytes).Replace("-", "").ToLower();

            Console.WriteLine(hash);
        }

        using (var md5 = MD5.Create())
        {
            var hashBytes = md5.ComputeHash(Encoding.UTF8.GetBytes(value));
            var hash = BitConverter.ToInt64(hashBytes.Take(8).ToArray(), 0);
            hash = IPAddress.HostToNetworkOrder(hash);

            Console.WriteLine(hash);
        }

        Console.ReadLine();
    }

MySql Result:

C# Result:

I will test it for over 30 million records and update the answer.

MySql and C# hashes don't match while converting them to BIGINT

1 Answers