MySQL | WEIGHT_STRING method
Start your free 7-days trial now!
MySQL's WEIGHT_STRING(~) method returns a binary string representing the weight string for the given input.
The weight string represents the comparison and sorting value of a string. If two strings have equal weight strings they are considered equal, while if WEIGHT_STRING(str1) < WEIGHT_STRING(str2) then str1 < str2. (str1 sorts before str2).
Parameters
1. str | string
The string to return the weight string for.
2. AS {CHAR | BINARY} (N) | optional
Cast the input string to a given type and length.
3. flags | flag | optional
Currently this parameter is unused.
Return value
The return value depends on the following cases:
Input | Return value |
|---|---|
Non-binary string | Contains collation weights for the string |
Binary string | Same as input |
|
|
Examples
Non-binary string
Notice that the WEIGHT_STRING(~) includes the collation weights for the string:
SET @s = _utf8mb4 'abc' COLLATE utf8mb4_0900_ai_ci;SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+| @s | HEX(@s) | HEX(WEIGHT_STRING(@s)) |+------+---------+------------------------+| abc | 616263 | 1C471C601C7A |+------+---------+------------------------+
Binary string
Notice for binary strings the WEIGHT_STRING(~) is equivalent to the input string:
SET @s = CAST('abc' AS BINARY);SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------------+---------+------------------------+| @s | HEX(@s) | HEX(WEIGHT_STRING(@s)) |+------------+---------+------------------------+| 0x616263 | 616263 | 616263 |+------------+---------+------------------------+
NULL
The weight string of NULL is NULL:
SELECT WEIGHT_STRING(NULL);
+------------------------------------------+| WEIGHT_STRING(NULL) |+------------------------------------------+| NULL |+------------------------------------------+
AS clause
The optional AS clause can be used to cast the input string to either a CHAR or BINARY string of N characters for CHAR or N bytes for BINARY. Padding for CHAR uses spaces while padding for BINARY uses 0x00 bytes.
SET NAMES 'latin1';SELECT HEX(WEIGHT_STRING('abc')), HEX(WEIGHT_STRING('abc' AS CHAR(5)));
+---------------------------+--------------------------------------+| HEX(WEIGHT_STRING('abc')) | HEX(WEIGHT_STRING('abc' AS CHAR(5))) |+---------------------------+--------------------------------------+| 414243 | 4142432020 |+---------------------------+--------------------------------------+
The latin1 value for space is 32, which is expressed in hexadecimal as 20.
When we cast 'abc' as CHAR(5), two space characters are padded on the right in the returned binary string.
When we cast as binary, we see two pairs of 00 right-padded in the result instead of spaces:
SELECT HEX(WEIGHT_STRING('abc')), HEX(WEIGHT_STRING('abc' AS BINARY(5)));
+---------------------------+----------------------------------------+| HEX(WEIGHT_STRING('abc')) | HEX(WEIGHT_STRING('abc' AS BINARY(5))) |+---------------------------+----------------------------------------+| 414243 | 6162630000 |+---------------------------+----------------------------------------+
MySQL documentation states that WEIGHT_STRING(~) is a debugging method and is only intended for internal use as its behavior can change between MySQL versions without notice.
