Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore specialized utilities. This category includes functions for benchmarking, managing UUIDs, converting IP addresses, and handling binary data formats.
Convert bytes to a human-readable string. This function formats a numeric byte count into units like KiB, MiB, GiB, up to EiB.
FORMAT_BYTES(double)Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), PiB (pebibytes) or EiB (exbibytes).
The binary prefixes (kibi, mebi, gibi, tebi, pebi and exbi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera, peta and exa can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
This function is similar to the function, except that function does not display exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
Pause query execution. This function pauses the script for a specified number of seconds, returning 0 if successful or 1 if interrupted.
SLEEP(duration)Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part given in microseconds.
Statements using the SLEEP() function are not .
This page is licensed: GPLv2, originally from
SELECT SLEEP(5.5);
+------------+
| SLEEP(5.5) |
+------------+
| 0 |
+------------+
1 row in set (5.50 sec)Convert an integer to an IPv4 address. This function takes a numeric IP value and returns its dotted-quad string representation.
INET_NTOA(expr)Given a numeric IPv4 network address in network byte order (4 or 8 byte), returns the dotted-quad representation of the address as a string.
192.168.1.1 corresponds to 3232235777 since 192 x 2563 + 168 x 256 2 + 1 x 256 + 1 = 3232235777.
This page is licensed: GPLv2, originally from
Convert an IPv6 address to binary. This function takes an IPv6 (or IPv4) address string and returns it as a VARBINARY(16) string.
Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address.
No trailing zone ID's or traling network masks are permitted. For IPv4 addresses, or IPv6 addresses with IPv4 address parts, no classful addresses or trailing port numbers are permitted and octal numbers are not supported.
The returned binary string will be or for IPv6 and IPv4 addresses respectively.
Returns NULL if the argument is not understood.
MariaDB starting with
INET6_ATON can take as an argument.
INET6_ATON cannot take as an argument.
Data Type
This page is licensed: CC BY-SA / Gnu FDL
Convert an IPv4 address to an integer. This function takes a dotted-quad IP string (e.g., '1.2.3.4') and returns its numeric representation.
SELECT FORMAT_BYTES(1000)FORMAT_BYTES(1024);
+--------------------+--------------------+
| FORMAT_BYTES(1000) | FORMAT_BYTES(1024) |
+--------------------+--------------------+
| 1000 bytes | 1.00 KiB |
+--------------------+--------------------+
SELECT FORMAT_BYTES(1000000),FORMAT_BYTES(1048576);
+-----------------------+-----------------------+
| FORMAT_BYTES(1000000) | FORMAT_BYTES(1048576) |
+-----------------------+-----------------------+
| 976.56 KiB | 1.00 MiB |
+-----------------------+-----------------------+
SELECT FORMAT_BYTES(1000000000),FORMAT_BYTES(1073741874);
+--------------------------+--------------------------+
| FORMAT_BYTES(1000000000) | FORMAT_BYTES(1073741874) |
+--------------------------+--------------------------+
| 953.67 MiB | 1.00 GiB |
+--------------------------+--------------------------+
SELECT FORMAT_BYTES(1000000000000),FORMAT_BYTES(1099511627776);
+-----------------------------+-----------------------------+
| FORMAT_BYTES(1000000000000) | FORMAT_BYTES(1099511627776) |
+-----------------------------+-----------------------------+
| 931.32 GiB | 1.00 TiB |
+-----------------------------+-----------------------------+
SELECT FORMAT_BYTES(1000000000000000),FORMAT_BYTES(1125899906842624);
+--------------------------------+--------------------------------+
| FORMAT_BYTES(1000000000000000) | FORMAT_BYTES(1125899906842624) |
+--------------------------------+--------------------------------+
| 909.49 TiB | 1.00 PiB |
+--------------------------------+--------------------------------+INET6_ATON(expr)IS_IPV4() is stricter than INET_ATON(), but as strict as INET6_ATON(), in determining the validity of an IPv4 address. This implies that if IS_IPV4 returns 1, the same expression will always return a non-NULL result when passed to INET_ATON(), but that the reverse may not apply.This page is licensed: CC BY-SA / Gnu FDL
IS_IPV4(expr)SELECT IS_IPV4('1110.0.1.1');
+-----------------------+
| IS_IPV4('1110.0.1.1') |
+-----------------------+
| 0 |
+-----------------------+
SELECT IS_IPV4('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV4('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 0 |
+--------------------------------------+NULL if the argument is not understood.This is calculated as follows: 192 x 2563 + 168 x 256 2 + 1 x 256 + 1.
This page is licensed: GPLv2, originally from fill_help_tables.sql
INET_ATON(expr)This function is used internally when replicating stored procedures. It makes little sense to use it explicitly in SQL statements, and it was not supposed to be used like that.
This page is licensed: GPLv2, originally from fill_help_tables.sql
NAME_CONST(name,value)SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+Convert a binary IPv6 address to string. This function takes a binary IPv6 address (like from INET6_ATON) and returns its human-readable string representation.
INET6_NTOA(expr)Given an IPv6 or IPv4 network address as a numeric binary string, returns the address as a nonbinary string in the connection character set.
The return string is lowercase, and is platform independent, since it does not use functions specific to the operating system. It has a maximum length of 39 characters.
Returns NULL if the argument is not understood.
This page is licensed: CC BY-SA / Gnu FDL
Release all named locks held by the session. This function frees all user-level locks acquired with GET_LOCK() in the current connection.
Wait for a replica to reach a log position. This function blocks until the replica has read and applied updates up to a specified position in the master's binary log.
Check validity of an IPv6 address. This function returns 1 if the argument is a valid IPv6 address string, and 0 otherwise.
SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+SELECT INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+SELECT HEX(INET6_ATON('10.0.1.1'));
+-----------------------------+
| HEX(INET6_ATON('10.0.1.1')) |
+-----------------------------+
| 0A000101 |
+-----------------------------+
SELECT HEX(INET6_ATON('48f3::d432:1431:ba23:846f'));
+----------------------------------------------+
| HEX(INET6_ATON('48f3::d432:1431:ba23:846f')) |
+----------------------------------------------+
| 48F3000000000000D4321431BA23846F |
+----------------------------------------------+RELEASE_ALL_LOCKS()log_name,log_posNULLMASTER_POS_WAIT()NULLIf a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.
The connection_name is used when you are using multi-source-replication. If you don't specify it, it's set to the value of the default_master_connection system variable.
Statements using the MASTER_POS_WAIT() function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
10NULLNULLstrIf the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table contains information about locks of this kind (as well as metadata locks).
Statements using the IS_FREE_LOCK function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
INET6 data type
This page is licensed: CC BY-SA / Gnu FDL
IS_IPV6(expr)The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
IS_IPV4_COMPAT(expr)Wait for a replica to apply a GTID. This function blocks until the replica has executed transactions up to the specified Global Transaction ID.
This function takes a string containing a comma-separated list of global transaction id's (similar to the value of, for example, gtid_binlog_pos). It waits until the value of has the same or higher seq_no within all replication domains specified in the gtid-list; in other words, it waits until the replica has reached the specified GTID position.
An optional second argument gives a timeout in seconds. If the timeout expires before the specified GTID position is reached, then the function returns -1. Passing NULL or a negative number for the timeout means no timeout, and the function will wait indefinitely.
If the wait completes without a timeout, 0 is returned. Passing NULL for the gtid-list makes the function return NULL immediately, without waiting.
The gtid-list may be the empty string, in which case MASTER_GTID_WAIT() returns immediately. If the gtid-list contains fewer domains than , then only those domains are waited upon. If gtid-list contains a domain that is not present in @@gtid_slave_pos, then MASTER_GTID_WAIT() will wait until an event containing such domain_id arrives on the replica (or until timed out or killed).
MASTER_GTID_WAIT() can be useful to ensure that a replica has caught up to a master. Simply take the value of on the master, and use it in a MASTER_GTID_WAIT() call on the replica; when the call completes, the replica will have caught up with that master position.
MASTER_GTID_WAIT() can also be used in client applications together with the session variable. This is useful in a read-scaleout setup, where the application writes to a single master but divides the reads out to a number of replica to distribute the load. In such a setup, there
is a risk that an application could first do an update on the master, and then a bit later do a read on a replica, and if the replica is not fast enough, the data read from the slave might not include the update just made, possibly confusing the application and/or the end-user. One way to avoid this is to request the value of on the master just after the update. Then before doing the read on the replica, do a MASTER_GTID_WAIT() on the value obtained from the master; this will ensure that the read is not performed until the replica has replicated sufficiently far for the update to have become visible.
Note that MASTER_GTID_WAIT() can be used even if the replica is configured not to use GTID for connections (). This is because from MariaDB 10, GTIDs are always logged on the master server, and always recorded on the replica servers.
MASTER_GTID_WAIT() is global; it waits for any master connection to reach the specified GTID position. works only against a specific connection. This also means that while MASTER_POS_WAIT() aborts if its master connection is terminated with or due to an error, MASTER_GTID_WAIT() continues to wait while replicas are stopped.
MASTER_GTID_WAIT() can take its timeout as a floating-point value, so a timeout in fractional seconds is supported, eg. MASTER_GTID_WAIT("0-1-100", 0.5). (The minimum wait is one microsecond, 0.000001 seconds).
Since MASTER_GTID_WAIT() looks only at the seq_no part of the GTIDs, not the server_id, care is needed if a replica becomes diverged from another server so that two different GTIDs with the same seq_no (in the same domain) arrive at the same server. This situation is in any case best avoided; setting is recommended, as this will prevent any such out-of-order sequence numbers from ever being replicated on a replica.
This page is licensed: CC BY-SA / Gnu FDL
Check who holds a named lock. This function returns the connection identifier of the client holding the lock, or NULL if the lock is free.
IS_USED_LOCK(str)Checks whether the lock named str is in use (that is, locked). If so, it returns the connection identifier of the client that holds the lock. Otherwise, it returns NULL. str is case insensitive.
If the plugin is installed, the table contains information about locks of this kind (as well as ).
Statements using the IS_USED_LOCK function are .
This page is licensed: GPLv2, originally from
Access values in ON DUPLICATE KEY UPDATE. This function retrieves the value that would have been inserted into a column if no key conflict occurred.
VALUE(col_name)In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the clause to refer to column values from the portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.
The VALUES() function is meaningful only in INSERT ... ON DUPLICATE KEY UPDATE statements and returns NULL otherwise.
This function was renamed to VALUE(), because it's incompatible with the standard Table Value Constructors syntax.
The VALUES() function can still be used but only in INSERT ... ON DUPLICATE KEY UPDATE statements; it's a syntax error otherwise.
This page is licensed: GPLv2, originally from
MASTER_POS_WAIT(log_name,log_pos[,timeout,["connection_name"]])IS_FREE_LOCK(str)SELECT IS_IPV6('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV6('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.02 sec)
SELECT IS_IPV6('10.0.1.1');
+---------------------+
| IS_IPV6('10.0.1.1') |
+---------------------+
| 0 |
+---------------------+SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 1 |
+------------------------------------------+
SELECT IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f'));
+-----------------------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f')) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+SELECT INET6_NTOA(UNHEX('0A000101'));
+-------------------------------+
| INET6_NTOA(UNHEX('0A000101')) |
+-------------------------------+
| 10.0.1.1 |
+-------------------------------+
SELECT INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F'));
+-------------------------------------------------------+
| INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F')) |
+-------------------------------------------------------+
| 48f3::d432:1431:ba23:846f |
+-------------------------------------------------------+SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 0 |
+---------------------+
SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 1 |
+---------------------+MASTER_GTID_WAIT(gtid-list[, timeout)MASTER_GTID_WAIT() allows one to specify a timeout of zero in order to do a non-blocking check to see if the replicas have progressed to a specific GTID position (MASTER_POS_WAIT() takes a zero timeout as meaning an infinite wait). To do an infinite MASTER_GTID_WAIT(), specify a negative timeout, or omit the timeout argument.
MASTER_GTID_WAIT() does not return the number of events executed since the wait started, nor does it return NULL if a replica thread is stopped. It always returns either 0 for successful wait completed, or -1 for timeout reached (or NULL if the specified gtid-pos is NULL).
timeout10NULLA lock is released with RELEASE_LOCK(), when the connection terminates (either normally or abnormally). A connection can hold multiple locks at the same time, so a lock that is no longer needed needs to be explicitly released.
The IS_FREE_LOCK function returns whether a specified lock is free or not, and the IS_USED_LOCK whether the function is in use or not.
Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.
It is also possible to recursively set the same lock. If a lock with the same name is set n times, it needs to be released n times as well.
str is case insensitive for GET_LOCK() and related functions. If str is an empty string or NULL, GET_LOCK() returns NULL and does nothing. timeout supports microseconds.
If the metadata_lock_info plugin is installed, locks acquired with this function are visible in the Information Schema METADATA_LOCK_INFO table.
This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.
Statements using the GET_LOCK function are not safe for statement-based replication.
The patch to permit multiple locks was contributed by Konstantin "Kostja" Osipov (MDEV-3917).
Multiple locks can be held:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
Timeout example: Connection 1:
Connection 2:
After 10 seconds...
Deadlocks are automatically detected and resolved. Connection 1:
Connection 2:
Connection 1:
Connection 2:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Returns a 16-byte globally unique identifier (GUID), similar to the UUID function, but without the - character.
This page is licensed: CC BY-SA / Gnu FDL
The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);GET_LOCK(str,timeout)SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_USED_LOCK('lock1');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_USED_LOCK('lock1') |
+-----------------------+-----------------------+
| 0 | 46 |
+-----------------------+-----------------------+
SELECT IS_FREE_LOCK('lock2'), IS_USED_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock2') | IS_USED_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | NULL |
+-----------------------+-----------------------+SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_FREE_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_FREE_LOCK('lock2') |
+-----------------------+-----------------------+
| 0 | 0 |
+-----------------------+-----------------------+
SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2');
+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | 1 |
+-----------------------+-----------------------+SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)SELECT GET_LOCK('lock4',10);
+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock4',10);+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
+----------------------+
| GET_LOCK('lock5',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionSYS_GUID()SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| 2C574E45BA2811EBB265F859713E4BE4 |
+----------------------------------+IS_IPV4_MAPPED(expr)SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 0 |
+------------------------------------------+
SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1'));
+-----------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1')) |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+Generate a time-ordered UUID (v7). This function returns a version 7 UUID, which is sortable by creation time.
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
Returns a Universally Unique Identifier (UUID) version 7. To generate a version 1 UUID, see the function. To generate a version 4 UUID, see .
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v7() function are .
v1
- Return short (64 bit) Universal Unique Identifier
- UUID without the - character for Oracle compatibility
This page is licensed: CC BY-SA / Gnu FDL
Generate a random UUID (v4). This function returns a version 4 UUID, which is generated using random numbers.
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
Returns a Universally Unique Identifier (UUID) version 4. To generate a version 1 UUID, see the function. To generate a version 7 UUID, see .
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID_v4() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v4() function are not .
v1
- Return short (64 bit) Universal Unique Identifier
- UUID without the - character for Oracle compatibility
This page is licensed: CC BY-SA / Gnu FDL
Generate a Universally Unique Identifier (v1). This function returns a standard 128-bit UUID as a 36-character string with hyphens.
Returns a Universally Unique Identifier (UUID) version 1. Functions to generate v4 and v7 UUIDs are available from . See UUIDv4 and respectively.
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
The first three numbers are generated from a timestamp.
The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MariaDB uses a randomly generated 48-bit number.
Statements using the UUID() function are not .
The function generates a UUIDv1 and the results are generated according to the "DCE 1.1:Remote Procedure Call" (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 ().
- Return short (64 bit) Universal Unique Identifier
- UUID without the - character for Oracle compatibility
This page is licensed: GPLv2, originally from
UUID_v7()UUID_v4()UUID()NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released.str is case insensitive. If str is an empty string or NULL, RELEASE_LOCK() returns NULL and does nothing.
Statements using the RELEASE_LOCK function are not safe for statement-based replication.
The DO statement is convenient to use with RELEASE_LOCK().
Connection1:
Connection 2:
Connection 1:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
This page is licensed: GPLv2, originally from fill_help_tables.sql
RELEASE_LOCK(str)SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2'), RELEASE_LOCK('lock3');
+-----------------------+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') | RELEASE_LOCK('lock3') |
+-----------------------+-----------------------+-----------------------+
| 1 | 0 | NULL |
+-----------------------+-----------------------+-----------------------+SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543bSELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543b
CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL, u UUID DEFAULT UUID_v4(), UNIQUE KEY(u));SELECT UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| cd41294a-afb0-11df-bc9b-00241dd75637 |
+--------------------------------------+Generate a short 64-bit UUID. This function returns a unique, monotonically increasing integer suitable for use as a compact identifier.
Returns a "short" universally unique identifier as a 64-bit unsigned integer (rather than a string-form 128-bit identifier as returned by the UUID() function).
The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:
The server_id of the current host is unique among your set of master and replica servers.
server_id is between 0 and 255.
You don't set back your system time for your server between mariadbd restarts.
You do not invoke UUID_SHORT()
The UUID_SHORT() return value is constructed this way:
Statements using the UUID_SHORT() function are not .
; Return full (128 bit) Universally Unique Identifier
- an alternative to auto_increment available from
- UUID without the - character for Oracle compatibility
This page is licensed: GPLv2, originally from
UUID_SHORT()(server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 21517162376069120 |
+-------------------+CREATE TABLE t1 (a BIGINT UNSIGNED DEFAULT(uuid_short()) PRIMARY KEY);
INSERT INTO t1 VALUES(),();
SELECT * FROM t1;
+-------------------+
| a |
+-------------------+
| 98113699159474176 |
| 98113699159474177 |
+-------------------+