MENU


Miscellaneous

DEFAULT(column) returns the default value configured for a table column. NAME_CONST(name, value) returns value, labeling the result column name — mainly useful inside stored routines and replication. BIT_COUNT(arg) returns the number of bits set to 1 in arg. SLEEP(sec) pauses execution for sec seconds.
SELECT BIT_COUNT(29);

4 — 29 is 11101 in binary, which has four 1-bits.


SELECT BIT_COUNT(29);

BIT_COUNT(29)
4

Advisory Locks

GET_LOCK(str, timeout) attempts to obtain an application-level lock named str, waiting up to timeout seconds. RELEASE_LOCK(str) releases a lock acquired with GET_LOCK(). IS_USED_LOCK(str) returns the connection ID holding the lock str, or NULL if it is not in use. IS_FREE_LOCK(str) returns 1 if str is not locked, 0 if it is, or NULL on error. MASTER_POS_WAIT(log_name, log_pos [, timeout]) blocks until a replica has applied every update up to the given position in the source's binary log, returning the number of log events waited for.
SELECT GET_LOCK('my_app_lock', 10);
…
SELECT RELEASE_LOCK('my_app_lock');

A common pattern for coordinating exclusive access to a resource across connections, without relying on table locks.


SELECT GET_LOCK('my_app_lock', 10);

SELECT RELEASE_LOCK('my_app_lock');

SELECT GET_LOCK('my_app_lock', 10):
GET_LOCK('my_app_lock', 10)
1
SELECT RELEASE_LOCK('my_app_lock'):
RELEASE_LOCK('my_app_lock')
1

-- IS_USED_LOCK() returns the holding connection's ID, which varies
-- per session, so no fixed result is shown here.
SELECT IS_USED_LOCK('my_app_lock');

Network Addresses

INET_ATON(ip_address) returns an integer representing an IPv4 address. INET_NTOA(int) returns the dotted-quad IP address represented by int. INET6_ATON(ip_address) returns a binary string representing an IPv6 address. INET6_NTOA(str) returns the IPv6 address text represented by str. IS_IPV4(str) and IS_IPV6(str) test whether str is a valid IPv4/IPv6 address. IS_IPV4_COMPAT(str) and IS_IPV4_MAPPED(str) test whether str (a binary string as returned by INET6_ATON()) is a valid IPv4-compatible or IPv4-mapped IPv6 address.
SELECT INET_ATON('192.168.1.1');
SELECT INET_NTOA(3232235777);

3232235777; '192.168.1.1'


SELECT INET_ATON('192.168.1.1');

SELECT INET_NTOA(3232235777);

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

Unique Identifiers

UUID() returns a Universally Unique Identifier per DCE 1.1 (The Open Group, October 1997), globally unique in space and time, formatted like '6ccd780c-baba-1026-9564-0040f4311e29'. UUID_SHORT() returns a more compact alternative as a 64-bit unsigned integer, formatted like 92395783831158784.

XML

ExtractValue(xml_frag, xpath_expr) returns the text (CDATA) of the first text node that is a child of an element matched by the given XPath expression. UpdateXML(xml_target, xpath_expr, new_xml) returns xml_target with the single portion matched by xpath_expr replaced by new_xml.
SELECT ExtractValue('<a><b>hello</b></a>', '/a/b');
SELECT UpdateXML('<a><b>hello</b></a>', '/a/b', '<b>bye</b>');

'hello'; '<a><b>bye</b></a>'


SELECT ExtractValue('<a><b>hello</b></a>', '/a/b');

SELECT UpdateXML('<a><b>hello</b></a>', '/a/b', '<b>bye</b>');

SELECT ExtractValue('hello', '/a/b'):
ExtractValue('<a><b>hello</b></a>', '/a/b')
hello
SELECT UpdateXML('hello', '/a/b', 'bye'):
UpdateXML(...)
<a><b>bye</b></a>

-- UUID() and UUID_SHORT() are non-deterministic, so neither has a
-- fixed result to show here.
SELECT UUID(), UUID_SHORT();