Michael J. Radwin

Tales of a software engineer who keeps kosher and hates the web.

MySQL User Defined Functions for FNV (Fowler/Noll/Vo) Hash

Sometimes you only need a 32- or 64-bit hash function. One of my favorites at Yahoo and something we’re using at Fraudwall Technologies is the FNV (Fowler/Noll/Vo) Hash.

If you’d like to use FNV inside of MySQL, you might find our udf_fnv.c useful. For example:


mysql> select FNV1A_64('The quick brown fox jumps over the lazy dog.');

+----------------------------------------------------------+

| FNV1A_64('The quick brown fox jumps over the lazy dog.') |

+----------------------------------------------------------+

| 75c4d4d9092c6c5a                                         |

+----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select FNV1A_32('The quick brown fox jumps over the lazy dog.');

+----------------------------------------------------------+

| FNV1A_32('The quick brown fox jumps over the lazy dog.') |

+----------------------------------------------------------+

| ecaf981a                                                 |

+----------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

The functions behave similarly to the MySQL built-ins MD5() and SHA1() in the sense that they return hex strings. The module defines 32- and 64-bit versions of all three variants of the FNV hash: FNV-0, FNV-1, and FNV-1a. Enjoy.