MySQL BIN_TO_UUID SQL Function - Binary to UUID (Universal Unique Identifier) |
|
| | BIN_TO_UUID | Syntax: | BIN_TO_UUID(Binary string [, Swap]) | | Return value: | CHAR | | Function type: | Miscellaneous function | |
| | The BIN_TO_UUID() function converts a "Binary string" to a UUID string and returns the result.
The return is a VARCHAR(36) value.
If the "Swap" argument is given and is 1, the first and third sets of hexadecimal digits are swapped. This should only happen if the "Binary string" itself has swapped time values, otherwise the result will not be correct.
BIN_TO_UUID() is the inverse function of UUID_TO_BIN().
If the "Binary string" argument is NULL, the BIN_TO_UUID() function returns NULL.
If either argument is invalid, an error occurs.
This feature was introduced in MySQL 8.0 and later. It does not exist in MariaDB. | SQL Examples for the BIN_TO_UUID function |
1
2
3
4
5
6
7
8
9
10
11
12
|
| set @u=uuid();
SELECT @u as `uuid`;
set @b=uuid_to_bin(@u);
SELECT hex(@b);
SELECT BIN_TO_UUID(@b);
SELECT BIN_TO_UUID(@b, 0);
SELECT BIN_TO_UUID(@b, 1);
select BIN_TO_UUID(null);
|
|
| uuid |
|---|
|
|---|
| 48e8c7cc-de2f-11ed-90b4-80ee73e9b514 |
|
|
| hex(@b) |
|---|
|
|---|
| 48E8C7CCDE2F11ED90B480EE73E9B514 |
|
|
| BIN_TO_UUID(@b) |
|---|
| varchar(36) BINARY |
|---|
| 48e8c7cc-de2f-11ed-90b4-80ee73e9b514 |
|
|
| BIN_TO_UUID(@b, 0) |
|---|
| varchar(36) BINARY |
|---|
| 48e8c7cc-de2f-11ed-90b4-80ee73e9b514 |
|
|
| BIN_TO_UUID(@b, 1) |
|---|
| varchar(36) BINARY |
|---|
| de2f11ed-c7cc-48e8-90b4-80ee73e9b514 |
|
|
| bin_to_uuid(null) |
|---|
| varchar(36) BINARY |
|---|
| NULL |
|
|
| Example of creating a replacement function for BIN_TO_UUID() in MariaDB |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
| /* Use MULTI-QUERY-Option in myway SQL manager */
DROP FUNCTION IF EXISTS BIN_TO_UUID;
CREATE FUNCTION BIN_TO_UUID(id varbinary(16))
RETURNS varchar(36)
BEGIN
set @hex=hex(id);
rETURN concat(
SUBSTR(@hex, 1, 8), '-',
SUBSTR(@hex, 9, 4), '-',
SUBSTR(@hex, 13, 4), '-',
SUBSTR(@hex, 17, 4), '-',
SUBSTR(@hex, 21)
);
END;
set @u=unhex('0A0C9A88DE3111ED90B480EE73E9B514');
select BIN_TO_UUID(@u);
|
|
| BIN_TO_UUID(@u) |  |
|
|---|
| varchar(36) BINARY |
|---|
| 0A0C9A88-DE31-11ED-90B4-80EE73E9B514 |
|
| Example of creating a replacement function for BIN_TO_UUID() with "Swap" argument in MariaDB |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
| /* Use MULTI-QUERY-Option in myway SQL manager */
DROP FUNCTION IF EXISTS BIN_TO_UUID;
CREATE FUNCTION BIN_TO_UUID(id varbinary(16), swp tinyint)
RETURNS varchar(36)
BEGIN
SET @hex=hex(id);
IF swp=0 THEN
RETURN concat(
substr(@hex, 1, 8), '-',
substr(@hex, 9, 4), '-',
substr(@hex, 13, 4), '-',
substr(@hex, 17, 4), '-',
substr(@hex, 21)
);
ELSE
RETURN concat(
substr(@hex, 9, 8), '-',
substr(@hex, 5, 4), '-',
substr(@hex, 1, 4), '-',
substr(@hex, 17, 4), '-',
substr(@hex, 21)
);
END IF;
END;
SET @u=unhex('0A0C9A88DE3111ED90B480EE73E9B514');
SELECT BIN_TO_UUID(@u, 0);
SELECT BIN_TO_UUID(@u, 1);
|
|
| BIN_TO_UUID(@u, 0) | |
|
|---|
| varchar(36) BINARY |
|---|
| 0A0C9A88-DE31-11ED-90B4-80EE73E9B514 |
|
|
| BIN_TO_UUID(@u, 1) | |
|
|---|
| varchar(36) BINARY |
|---|
| DE3111ED-9A88-0A0C-90B4-80EE73E9B514 |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the BIN_TO_UUID() function in MySQL and MariaDB databases | In MySQL the BIN_TO_UUID() function is used to convert a binary string of a UUID (Universally Unique Identifier) into a readable form. The function converts the binary representation of a UUID into a character string in the format "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee", where each character group is a part of the UUID represents.
If UUIDs have been stored in a binary format (16 byte binary string), they can be converted to their string representation using the BIN_TO_UUID() function, to display UUIDs in a more human-readable format, or to query and compare UUIDs based on their string representation. The function can be used in combination with other functions or operations to manipulate and work with UUIDs in SQL queries, such as performing string operations on them and then converting them back to a binary UUID format. | | Further MySQL und MariaDB SQL Miscellaneous functions | |
| | | More information about the BIN_TO_UUID SQL function: |
|
|
|
|
