12.16. Miscellaneous Functions

DEFAULT(col_name)

Returns the default value for a table column. An error results if the column has no default value.

mysql> UPDATE t SET i = DEFAULT(i)+1
                    WHERE id < 100;

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 12.5, "String Functions".

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). If you have a lock obtained with GET_LOCK(), it is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or your connection terminates (either normally or abnormally). 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.

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 enables clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also enables 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.

mysql> SELECT
                    GET_LOCK('lock1',10);        -> 1mysql> SELECT
                    IS_FREE_LOCK('lock2');        -> 1mysql> SELECT
                    GET_LOCK('lock2',10);        -> 1mysql> SELECT
                    RELEASE_LOCK('lock2');        -> 1mysql> SELECT
                    RELEASE_LOCK('lock1');        -> NULL

The second RELEASE_LOCK() call returns NULL because the lock 'lock1' was automatically released by the second GET_LOCK() call.

If multiple clients are waiting for a lock, the order in which they will acquire it is undefined and depends on factors such as the thread library in use. In particular, applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

INET_ATON(expr)

Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.

mysql> SELECT
                    INET_ATON('10.0.5.9');        -> 167773449

For this example, the return value is calculated as 10×256³ + 0×256² + 5×256 + 9.

INET_ATON() may or may not return a non-NULL result for short-form IP addresses (such as '127.1' as a representation of '127.0.0.1'). Because of this, INET_ATON()a should not be used for such addresses.

INET_NTOA(expr)

Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a nonbinary string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.

mysql> SELECT
                    INET_NTOA(167773449);        -> '10.0.5.9'

INET6_ATON(expr)

Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian). Because numeric-format IPv6 addresses require more bytes than the largest integer type, the representation returned by this function has the VARBINARY data type: VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. If the argument is not a valid address, INET6_ATON() returns NULL.

The following examples use HEX() to display the INET6_ATON() result in printable form:

mysql> SELECT
                    HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));        -> 'FDFE0000000000005A55CAFFFEFA9089'mysql> SELECT HEX(INET6_ATON('10.0.5.9'));        -> '0A000509'

INET6_ATON() observes several constraints on valid arguments. These are given in the following list along with examples.

To convert an IPv4 address expr represented in numeric form as an INT value to an IPv6 address represented in numeric form as a VARBINARY value, use this expression:

INET6_ATON(INET_NTOA(expr))

For example:

mysql> SELECT
                    HEX(INET6_ATON(INET_NTOA(167773449)));        -> '0A000509'

This function was added in MySQL 5.6.3.

INET6_NTOA(expr)

Given an IPv6 or IPv4 network address represented in numeric form as a binary string, returns the string representation of the address as a nonbinary string in the connection character set. If the argument is not a valid address, INET6_NTOA() returns NULL.

INET6_NTOA() has these properties:

mysql> SELECT
                    INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));        -> 'fdfe::5a55:caff:fefa:9089'mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));        -> '10.0.5.9'mysql> SELECT
                    INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));        -> 'fdfe::5a55:caff:fefa:9089'mysql> SELECT INET6_NTOA(UNHEX('0A000509'));        -> '10.0.5.9'

This function was added in MySQL 5.6.3.

IS_FREE_LOCK(str)

Checks whether the lock named str is free to use (that is, not locked). Returns 1 if the lock is free (no one is using the lock), 0 if the lock is in use, and NULL if an error occurs (such as an incorrect argument).

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

IS_IPV4(expr)

Returns 1 if the argument is a valid IPv4 address specified as a string, 0 otherwise.

mysql> SELECT IS_IPV4('10.0.5.9'),
                    IS_IPV4('10.0.5.256');        -> 1, 0

For a given argument, if IS_IPV4() returns 1, INET_ATON() (and INET6_ATON()) will return non-NULL. The converse statement is not true: In some cases, INET_ATON() returns non-NULL when IS_IPV4() returns 0.

As implied by the preceding remarks, IS_IPV4() is more strict than INET_ATON() about what constitutes a valid IPv4 address, so it may be useful for applications that need to perform strong checks against invalid values. Alternatively, use INET6_ATON() to convert IPv4 addresses to internal form and check for a NULL result (which indicates an invalid address). INET6_ATON() is equally strong as IS_IPV4() about checking IPv4 addresses.

This function was added in MySQL 5.6.3.

IS_IPV4_COMPAT(expr)

This function takes an IPv6 address represented in numeric form as a binary string, as returned by INET6_ATON(). It returns 1 if the argument is a valid IPv4-compatible IPv6 address, 0 otherwise. IPv4-compatible addresses have the form ::ipv4_address.

mysql> SELECT
                    IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9'));        -> 1mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9'));        -> 0

The IPv4 part of an IPv4-compatible address can also be represented using hexadecimal notation. For example, 192.168.0.1 has this raw hexadecimal value:

mysql> SELECT
                    HEX(INET6_ATON('192.168.0.1'));        -> 'C0A80001'

Expressed in IPv4-compatible form, ::192.168.0.1 is equivalent to ::c0a8:0001 or (without leading zeros) ::c0a8:1

mysql> SELECT    -> 
                    IS_IPV4_COMPAT(INET6_ATON('::192.168.0.1')),    -> 
                    IS_IPV4_COMPAT(INET6_ATON('::c0a8:0001')),    -> 
                    IS_IPV4_COMPAT(INET6_ATON('::c0a8:1'));        -> 1, 1, 1

This function was added in MySQL 5.6.3.

IS_IPV4_MAPPED(expr)

This function takes an IPv6 address represented in numeric form as a binary string, as returned by INET6_ATON(). It returns 1 if the argument is a valid IPv4-mapped IPv6 address, 0 otherwise. IPv4-mapped addresses have the form ::ffff:ipv4_address.

mysql> SELECT
                    IS_IPV4_MAPPED(INET6_ATON('::10.0.5.9'));        -> 0mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9'));        -> 1

As with IS_IPV4_COMPAT() the IPv4 part of an IPv4-mapped address can also be represented using hexadecimal notation:

mysql> SELECT    -> 
                    IS_IPV4_MAPPED(INET6_ATON('::ffff:192.168.0.1')),    ->  IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:0001')),    ->  IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:1'));        -> 1, 1, 1

This function was added in MySQL 5.6.3.

IS_IPV6(expr)

Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise. This function does not consider IPv4 addresses to be valid IPv6 addresses.

mysql> SELECT IS_IPV6('10.0.5.9'),
                    IS_IPV6('::1');        -> 0, 1

For a given argument, if IS_IPV6() returns 1, INET6_ATON() will return non-NULL.

This function was added in MySQL 5.6.3.

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.

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

MASTER_POS_WAIT(log_name,log_pos[,timeout])

This function is useful for control of master/slave synchronization. It blocks until the slave has read and applied all updates up to the specified position in the master log. The return value is the number of log events the slave had to wait for to advance to the specified position. The function returns NULL if the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if the timeout has been exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is waiting, the function returns NULL. If the slave is past the specified position, the function returns immediately.

If 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.

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

NAME_CONST(name,value)

Returns the given value. When used to produce a result set column, NAME_CONST() causes the column to have the given name. The arguments should be constants.

mysql> SELECT NAME_CONST('myname',
                    14);+--------+| myname |+--------+|     14 |+--------+

This function is for internal use only. The server uses it when writing statements from stored programs that contain references to local program variables, as described in Section 19.7, "Binary Logging of Stored Programs", You might see this function in the output from mysqlbinlog.

RELEASE_LOCK(str)

Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock was not established by this thread (in which case the lock is not released), and 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.

The DO statement is convenient to use with RELEASE_LOCK(). See Section 13.2.3, "DO Syntax".

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

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.

This function is unsafe for statement-based replication. In MySQL 5.6, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

UUID()

Returns a Universal Unique Identifier (UUID) generated according to "DCE 1.1: Remote Procedure Call" (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706, http://www.opengroup.org/public/pubs/catalog/c706.htm).

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:

mysql> SELECT UUID();        -> '6ccd780c-baba-1026-9564-0040f4311e29'

UUID_SHORT()

Returns a "short" universal 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 UUID_SHORT() return value is constructed this way:

  (server_id & 255) << 56+ (server_startup_time_in_seconds << 24)+ incremented_variable++;

mysql> SELECT
                    UUID_SHORT();        -> 92395783831158784

Note that UUID_SHORT() does not work with statement-based replication.

VALUES(col_name)

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT 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 the ON DUPLICATE KEY UPDATE clause of INSERT statements and returns NULL otherwise. See Section 13.2.5.3, "INSERT ... ON DUPLICATE KEY UPDATE Syntax".

mysql> INSERT INTO table (a,b,c) VALUES
                    (1,2,3),(4,5,6)    -> ON DUPLICATE KEY UPDATE
                    c=VALUES(a)+VALUES(b);