MySQL Miscellaneous Functions

Last update on August 10 2023 12:52:49 (UTC/GMT +8 hours)

Miscellaneous Functions

Here is a list of MySQL miscellaneous functions with a description, syntax, and examples.

Contents:

MySQL DEFAULT() Function
MySQL GET_LOCK() Function
MySQL INET_ATON() Function
MySQL INET_NTOA() Function
MySQL INET6_ATON() Function
MySQL INET6_NTOA() Function
MySQL IS_FREE_LOCK() Function
MySQL IS_IPV4_COMPAT() Function
MySQL IS_IPV4_MAPPED() Function
MySQL IS_IPV4() Function
MySQL IS_IPV6() Function
MySQL IS_USED_LOCK() Function
MySQL NAME_CONST() Function

MySQL DEFAULT(col_name) function

The function is used to get the default value from a table column.
Returns error if the column has no default value.

Example:

mysql> select * from table1;
  +------------+-----------------------+
  | col1       | col2                  |
  +------------+-----------------------+
  | w3resource | w3resource            |
  +------------+-----------------------+
  1 row in set (0.00 sec)

mysql> SELECT default(col1) from table1;
  +---------------+
  | default(col1) |
  +---------------+
  | NULL          |
  +---------------+
  1 row in set (0.00 sec)

MySQL GET_LOCK(str,timeout) function

The function 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.

GET_LOCK() 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 within one session, GET_LOCK() blocks any request by another session 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.

Example:

mysql> SELECT GET_LOCK('lock1',10);
        -> 1

MySQL INET_ATON(expr) function

The function is used to get the numeric value of an IP address.

Example:

mysql> SELECT INET_ATON('10.0.4.8');
+-----------------------+
| INET_ATON('10.0.4.8') |
+-----------------------+
|             167773192 |
+-----------------------+
1 row in set (0.07 sec)

Note: In the above example, the return value is calculated as 10×256³+ 0×256²+ 4×256 + 8.

MySQL INET_NTOA(expr) function

The function is used to get the dotted-quad string representation of the address as a nonbinary string in the connection character set. from a given numeric IPv4 network address in network byte order.

Example:

mysql> SELECT INET_NTOA(167773449);
+----------------------+
| INET_NTOA(167773449) |
+----------------------+
| 10.0.5.9             |
+----------------------+
1 row in set (0.00 sec)

MySQL INET6_ATON(expr) function

The function is used to a get the binary string that represents the numeric value of the address in network byte order (big endian) from a given IPv6 or IPv4 network address as a string.
As the 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.

Example:

mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
  +----------------------------------------------+
  | HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')) |
  +----------------------------------------------+
  | FDFE0000000000005A55CAFFFEFA9089             |
  +----------------------------------------------+
  1 row in set (0.06 sec)

mysql> SELECT HEX(INET6_ATON('10.0.5.9'));
  +-----------------------------+
  | HEX(INET6_ATON('10.0.5.9')) |
  +-----------------------------+
  | 0A000509                    |
  +-----------------------------+
  1 row in set (0.00 sec)

Note: In the above examples we have used HEX() to display the INET6_ATON() result in printable form.

MySQL INET6_NTOA(expr) function

The function is used to get the string representation of the address as a nonbinary string in the connection character set from a given IPv6 or IPv4 network address represented in numeric form as a binary string.

Example:

mysql> SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
  +-----------------------------------------------------+
  | INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089')) |
  +-----------------------------------------------------+
  | fdfe::5a55:caff:fefa:9089                           |
  +-----------------------------------------------------+
  1 row in set (0.00 sec)
mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
  +------------------------------------+
  | INET6_NTOA(INET6_ATON('10.0.5.9')) |
  +------------------------------------+
  | 10.0.5.9                           |
  +------------------------------------+
  1 row in set (0.00 sec)
mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
  +-------------------------------------------------------+
  | INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089')) |
  +-------------------------------------------------------+
  | fdfe::5a55:caff:fefa:9089                             |
  +-------------------------------------------------------+
  1 row in set (0.05 sec)
mysql> SELECT INET6_NTOA(UNHEX('0A000509'));
  +-------------------------------+
  | INET6_NTOA(UNHEX('0A000509')) |
  +-------------------------------+
  | 10.0.5.9                      |
  +-------------------------------+
  1 row in set (0.00 sec)

MySQL IS_FREE_LOCK(str) function

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

MySQL IS_IPV4_COMPAT(expr) function

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.

Example:

mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.49'));
  +-------------------------------------------+
  | IS_IPV4_COMPAT(INET6_ATON('::10.0.5.49')) |
  +-------------------------------------------+
  |                                         1 |
  +-------------------------------------------+
  1 row in set (0.00 sec)
mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9'));
  +-----------------------------------------------+
  | IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9')) |
  +-----------------------------------------------+
  |                                             0 |
  +-----------------------------------------------+
  1 row in set (0.00 sec

MySQL IS_IPV4_MAPPED(expr) function

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.

Example:

mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.4.9'));
  +------------------------------------------+
  | IS_IPV4_MAPPED(INET6_ATON('::10.0.4.9')) |
  +------------------------------------------+
  |                                        0 |
  +------------------------------------------+
  1 row in set (0.00 sec)
mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9'));
  +-----------------------------------------------+
  | IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9')) |
  +-----------------------------------------------+
  |                                             1 |
  +-----------------------------------------------+
  1 row in set (0.00 sec)

MySQL IS_IPV4(expr) function

The function is used to test whether an argument (specified as a string) is a valid IPv4 address.
It 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_IPV4('10.0.5.256'), IS_IPV4('10.0.5.4');
+-----------------------+---------------------+
| IS_IPV4('10.0.5.256') | IS_IPV4('10.0.5.4') |
+-----------------------+---------------------+
|                     0 |                   1 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

MySQL IS_IPV6(expr) function

The function is used to test whether an argument (specified as a string) is a valid IPv6 address.
It 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.

Example:

mysql> SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1');
+---------------------+----------------+
| IS_IPV6('10.0.5.9') | IS_IPV6('::1') |
+---------------------+----------------+
|                   0 |              1 |
+---------------------+----------------+
1 row in set (0.00 sec)

MySQL IS_USED_LOCK(str) function

The function is used to check whether the lock named str is in use (that is, locked).
if locked, it returns the connection identifier of the client that holds the lock, otherwise, returns NULL.
This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

MySQL NAME_CONST(name,value) function

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

Example:

mysql> SELECT NAME_CONST('employee_id', 20);
+-------------+
| employee_id |
+-------------+
|          20 |
+-------------+
1 row in set (0.02 sec)

This function is for internal use only. For your applications, you can obtain exactly the same result as in the example just shown by using simple aliasing, like this:

mysql> SELECT 20 AS employee_id;
+-------------+
| employee_id |
+-------------+
|          20 |
+-------------+
1 row in set (0.01 sec)

Previous: MySQL XML Functions
Next: MySQL Advance Query Find duplicate data in MySQL