search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook

MySQL | WEIGHT_STRING method

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Functions
chevron_right
String
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags

MySQL's WEIGHT_STRING(~) method returns a binary string representing the weight string for the given input.

NOTE

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

NULL

NULL

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 |
+---------------------------+----------------------------------------+
WARNING

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.

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!