| UUID_TO_BIN | Syntax: | UUID_TO_BIN(String [, Swap]) | | Return value: | BINARY | | Function type: | Miscellaneous function | |
|
|
The UUID_TO_BIN() function converts a UUID "string" to a binary UUID and returns the result.
The binary return UUID is a VARBINARY(16) value.
If the swap argument is given and is 1, the first and third sets of hexadecimal digits are swapped. This shifts the faster varying part to the right and can improve indexing efficiency when the result is stored in an indexed column of a table.
If the "string" argument is NULL, the UUID_TO_BIN() function returns NULL.
If either argument is invalid, an error occurs.
With only one argument, UUID_TO_BIN() has the same function as UNHEX(REPLACE(@uuid, '-', ''))
This feature was introduced in MySQL 8.0 and later. It does not exist in MariaDB. |
SQL Examples for the UUID_TO_BIN function |
| set @u=uuid();
SELECT @u as `uuid`;
SELECT hex(uuid_to_bin(@u));
SELECT hex(unhex(replace(@u,'-','')));
SELECT hex(uuid_to_bin(@u, 0));
SELECT hex(uuid_to_bin(@u, 1));
select uuid_to_bin(null);
|
|
| uuid |
|---|
|
|---|
| 3bf1d51f-de0d-11ed-90b4-80ee73e9b514 |
|
|
| hex(uuid_to_bin(@u)) |
|---|
| varchar(32) BINARY |
|---|
| 3BF1D51FDE0D11ED90B480EE73E9B514 |
|
|
| hex(unhex(replace(@u,'-',''))) |
|---|
|
|---|
| 3BF1D51FDE0D11ED90B480EE73E9B514 |
|
|
| hex(uuid_to_bin(@u, 0)) |
|---|
| varchar(32) BINARY |
|---|
| 3BF1D51FDE0D11ED90B480EE73E9B514 |
|
|
| hex(uuid_to_bin(@u, 1)) |
|---|
| varchar(32) BINARY |
|---|
| 11EDDE0D3BF1D51F90B480EE73E9B514 |
|
|
| uuid_to_bin(null) |
|---|
| varchar(16) BINARY |
|---|
| NULL |
|
|
|
Example of creating a replacement function for UUID_TO_BIN() in MariaDB |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
| /* Use MULTI-QUERY-Option in myway SQL manager */
DROP FUNCTION IF EXISTS UUID_TO_BIN;
CREATE FUNCTION UUID_TO_BIN(id varCHAR(36))
RETURNS BINARY(16)
BEGIN
RETURN UNHEX(REPLACE(id, '-', ''));
END;
select hex(uuid_to_bin(uuid()));
|
|
| hex(uuid_to_bin(uuid())) |
|---|
| varchar(32) BINARY |
|---|
| 21D93C5BDE0D11EDA01C80EE73E9B513 |
|
|
Example of creating a replacement function for UUID_TO_BIN() 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
|
| /* Use MULTI-QUERY-Option in myway SQL manager */
DROP FUNCTION IF EXISTS UUID_TO_BIN;
CREATE FUNCTION UUID_TO_BIN(id varCHAR(36), swp tinyint)
RETURNS BINARY(16)
BEGIN
if swp=0 then
rETURN UNHEX(REPLACE(id, '-', ''));
else
rETURN UNHEX(concat(
SUBSTR(id, 15, 4),
SUBSTR(id, 10, 4),
SUBSTR(id, 1, 8),
SUBSTR(id, 20, 4),
SUBSTR(id, 25)
));
end if;
END;
set @u=uuid();
select hex(uuid_to_bin(@u, 0));
select hex(uuid_to_bin(@u, 1));
|
|
| hex(uuid_to_bin(@u, 0)) |
|---|
| varchar(32) BINARY |
|---|
| 423449CDDE1011EDA01C80EE73E9B513 |
|
|
| hex(uuid_to_bin(@u, 1)) |
|---|
| varchar(32) BINARY |
|---|
| 11EDDE10423449CDA01C80EE73E9B513 |
|
|
|
| The examples were created with the MyWAY SQL manager: |
How to use the UUID_TO_BIN() function in MySQL and MariaDB databases |
In MySQL the UUID_TO_BIN() function is used to get a UUID (Universally Unique Identifier) from their string representation to their binary form. The function takes a UUID string as input and returns the corresponding binary representation as a 16-byte binary string, for example to store UUIDs in a binary column or in a binary format. The input to the UUID_TO_BIN() function must be a valid UUID string in the format "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee.
The function can be used in combination with other functions or operations to manipulate and work with UUIDs in SQL queries, for example to perform binary operations on them and then convert them back to strings if needed. |
|
Further MySQL und MariaDB SQL Miscellaneous functions |
|
