XXH3

Compute a fast 64-bit xxHash. XXH3 returns the XXH3 hash of a text value as a 64-bit unsigned integer.

XXH3 was added in MariaDB 13.1.

Syntax

XXH3(expr)

Description

XXH3 returns a fast, non-cryptographic xxHash of a text value as a 64-bit number (BIGINT UNSIGNED). It's useful for checksums, change detection, and grouping or bucketing rows. For a 32-bit result, use XXH32().

The argument must be a text value: a string, BLOB/TEXT, JSON, ENUM, or SET. Numbers, dates, and other non-text values are rejected with an error; CAST() them to a string first.

Hashing NULL returns NULL. Hashing an empty string returns 0.

The result depends on the collation, not on the raw bytes. Values that count as equal under the collation hash to the same number (for example, 'abc' and 'ABC' under a case-insensitive collation). The same text in a different character set or collation gives a different number.

When comparing a column to a value, keep both sides in the same collation. If the hashed column and the value use different collations, they won't match, so a hash-based index or lookup can quietly find nothing. Hash the binary form with CAST(... AS BINARY) (or the BINARY operator) when you want a byte-exact hash that ignores collation.

Predicting the partition for KEY partitioning

The same XXH3 algorithm is available as a KEY partitioning algorithm (PARTITION BY KEY ALGORITHM=XXH3). For a table partitioned on a single column, the partition a value lands in is MOD(XXH3(value), number_of_partitions) — handy for knowing where a row will go, or for partition pruning. The collation rule above still applies: the value must use the column's collation to predict correctly.

This shortcut holds only for a plain, single-column KEY. It doesn't apply to LINEAR KEY (unless the number of partitions is a power of two), to multi-column keys, or to NULL values (the server places those itself).

XXH32 is the 32-bit variant. The XXH64() and XXH128() functions are not part of this release.

Examples

SELECT XXH3('abc');
+---------------------+
| XXH3('abc')         |
+---------------------+
| 5167207402487786768 |
+---------------------+

The result depends on the session collation, so a bare literal reproduces only under the same collation (the result above is for the default utf8mb4_uca1400_ai_ci). For a byte-exact hash that doesn't depend on collation, hash the binary form:

SELECT XXH3(CAST('abc' AS BINARY));
+-----------------------------+
| XXH3(CAST('abc' AS BINARY)) |
+-----------------------------+
|         8696274497037089104 |
+-----------------------------+

NULL and the empty string:

SELECT XXH3(NULL), XXH3('');
+------------+----------+
| XXH3(NULL) | XXH3('') |
+------------+----------+
|       NULL |        0 |
+------------+----------+

Equal values under a case-insensitive collation hash to the same number:

SELECT XXH3(_utf8mb4'ABC' COLLATE utf8mb4_general_ci)
     = XXH3(_utf8mb4'abc' COLLATE utf8mb4_general_ci) AS equal_ci;
+----------+
| equal_ci |
+----------+
|        1 |
+----------+

A non-text argument is rejected:

SELECT XXH3(11223344);
ERROR 4079 (HY000): Illegal parameter data type int for operation 'XXH3'

See Also

• XXH32() - the 32-bit variant

• CRC32(), CRC32C() - other non-cryptographic checksums

_{This page is licensed: CC BY-SA / Gnu FDL}