Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.4Kb
Man Pages (Zip) - 306.8Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Functions and Operators  /  Miscellaneous Functions

13.19 Miscellaneous Functions

Table 13.25 Miscellaneous Functions

NameDescription
ANY_VALUE() Suppress ONLY_FULL_GROUP_BY value rejection
DEFAULT() Return the default value for a table column
GET_LOCK() Get a named lock
INET_ATON() Return the numeric value of an IP address
INET_NTOA() Return the IP address from a numeric value
INET6_ATON() Return the numeric value of an IPv6 address
INET6_NTOA() Return the IPv6 address from a numeric value
IS_FREE_LOCK() Whether the named lock is free
IS_IPV4() Whether argument is an IPv4 address
IS_IPV4_COMPAT() Whether argument is an IPv4-compatible address
IS_IPV4_MAPPED() Whether argument is an IPv4-mapped address
IS_IPV6() Whether argument is an IPv6 address
IS_USED_LOCK() Whether the named lock is in use; return connection identifier if true
MASTER_POS_WAIT() Block until the slave has read and applied all updates up to the specified position
NAME_CONST() Causes the column to have the given name
RAND() Return a random floating-point value
RELEASE_ALL_LOCKS() Releases all current named locks
RELEASE_LOCK() Releases the named lock
SLEEP() Sleep for a number of seconds
UUID() Return a Universal Unique Identifier (UUID)
UUID_SHORT() Return an integer-valued universal identifier
VALUES() Defines the values to be used during an INSERT

  • ANY_VALUE(arg)

    This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

    For example, if name is a nonindexed column, the following query fails with ONLY_FULL_GROUP_BY enabled:

    
    mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
    ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
    BY clause and contains nonaggregated column 'mydb.t.address' which
    is not functionally dependent on columns in GROUP BY clause; this
    is incompatible with sql_mode=only_full_group_by
    

    The failure occurs because address is a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them. As a result, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query:

    • Alter the table to make name a primary key or a unique NOT NULL column. This enables MySQL to determine that address is functionally dependent on name; that is, address is uniquely determined by name. (This technique is inapplicable if NULL must be permitted as a valid name value.)

    • Use ANY_VALUE() to refer to address:

      SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
      

      In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group. ANY_VALUE() is not an aggregate function, unlike functions such as SUM() or COUNT(). It simply acts to suppress the test for nondeterminism.

    • Disable ONLY_FULL_GROUP_BY. This is equivalent to using ANY_VALUE() with ONLY_FULL_GROUP_BY enabled, as described in the previous item.

    ANY_VALUE() is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid because age is functionally dependent on the grouping column age-1, but MySQL cannot tell that and rejects the query with ONLY_FULL_GROUP_BY enabled:

    SELECT age FROM t GROUP BY age-1;
    

    To cause MySQL to accept the query, use ANY_VALUE():

    SELECT ANY_VALUE(age) FROM t GROUP BY age-1;
    

    ANY_VALUE() can be used for queries that refer to aggregate functions in the absence of a GROUP BY clause:

    
    mysql> SELECT name, MAX(age) FROM t;
    ERROR 1140 (42000): In aggregated query without GROUP BY, expression
    #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
    is incompatible with sql_mode=only_full_group_by
    

    Without GROUP BY, there is a single group and it is indeterminate which name value to choose for the group. ANY_VALUE() tells MySQL to accept the query:

    SELECT ANY_VALUE(name), MAX(age) FROM t;
    

    It may be that, due to some property of a given data set, you know that a selected nonaggregated column is effectively functionally dependent on a GROUP BY column. For example, an application may enforce uniqueness of one column with respect to another. In this case, using ANY_VALUE() for the effectively functionally dependent column may make sense.

    For additional discussion, see Section 13.20.3, “MySQL Handling of GROUP BY”.

  • 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 13.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. A negative timeout value means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name.

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

    A lock obtained with GET_LOCK() is released explicitly by executing RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally). Lock release may also occur with another call to GET_LOCK():

    • Before 5.7.5, only a single simultaneous lock can be acquired and GET_LOCK() releases any existing lock.

    • In MySQL 5.7.5, GET_LOCK() was reimplemented using the metadata locking (MDL) subsystem and its capabilities were extended. Multiple simultaneous locks can be acquired and GET_LOCK() does not release any existing locks. It is even possible for a given session to acquire multiple locks for the same name. Other sessions cannot acquire a lock with that name until the acquiring session releases all its locks for the name.

      As a result of the MDL reimplementation, locks acquired with GET_LOCK() appear in the Performance Schema metadata_locks table. The OBJECT_TYPE column says USER LEVEL LOCK and the OBJECT_NAME column indicates the lock name. Also, the capability of acquiring multiple locks introduces the possibility of deadlock among clients. When this happens, the server chooses a caller and terminates its lock-acquisition request with an ER_USER_LOCK_DEADLOCK error. This error does not cause transactions to roll back.

    The difference in lock acquisition behavior as of MySQL 5.7.5 can be seen by the following example. Suppose that you execute these statements:

    SELECT GET_LOCK('lock1',10);
    SELECT GET_LOCK('lock2',10);
    SELECT RELEASE_LOCK('lock2');
    SELECT RELEASE_LOCK('lock1');
    

    In MySQL 5.7.5 or later, the second GET_LOCK() acquires a second lock and both RELEASE_LOCK() calls return 1 (success). Before MySQL 5.7.5, the second GET_LOCK() releases the first lock ('lock1') and the second RELEASE_LOCK() returns NULL (failure) because there is no 'lock1' to release.

    MySQL 5.7.5 and later enforces a maximum length on lock names of 64 characters. Previously, no limit was enforced.

    Locks obtained with GET_LOCK() are not released when transactions commit or roll back.

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

    If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

    GET_LOCK() is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

    Caution

    With the capability of acquiring multiple named locks in MySQL 5.7.5, it is possible for a single statement to acquire a large number of locks. For example:

    INSERT INTO ... SELECT GET_LOCK(t1.col_name) FROM t1;
    

    These types of statements may have certain adverse effects. For example, if the statement fails part way through and rolls back, locks acquired up to the point of failure will still exist. If the intent is for there to be a correspondence between rows inserted and locks acquired, that intent will not be satisfied. Also, if it is important that locks are granted in a certain order, be aware that result set order may differ depending on which execution plan the optimizer chooses. For these reasons, it may be best to limit applications to a single lock-acquisition call per statement.

    A different locking interface is available as either a plugin service or a set of user-defined functions. This interface provides lock namespaces and distinct read and write locks, unlike the interface provided by GET_LOCK() and related functions. For details, see Section 26.3.1, “The Locking Service”.

  • 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×2563 + 0×2562 + 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.

    Note

    To store values generated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed. If you use a signed column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. See Section 12.2.6, “Out-of-Range and Overflow Handling”.

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

    • A trailing zone ID is not permitted, as in fe80::3%1 or fe80::3%eth0.

    • A trailing network mask is not permitted, as in 2001:45f:3:ba::/64 or 192.168.1.0/24.

    • For values representing IPv4 addresses, only classless addresses are supported. Classful addresses such as 192.168.1 are rejected. A trailing port number is not permitted, as in 192.168.1.2:8080. Hexadecimal numbers in address components are not permitted, as in 192.0xa0.1.2. Octal numbers are not supported: 192.168.010.1 is treated as 192.168.10.1, not 192.168.8.1. These IPv4 constraints also apply to IPv6 addresses that have IPv4 address parts, such as IPv4-compatible or IPv4-mapped addresses.

    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'
    
  • 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:

    • It does not use operating system functions to perform conversions, thus the output string is platform independent.

    • The return string has a maximum length of 39 (4 x 8 + 7). Given this statement:

      CREATE TABLE t AS SELECT INET6_NTOA(expr) AS c1;
      

      The resulting table would have this definition:

      CREATE TABLE t (c1 VARCHAR(39) CHARACTER SET utf8 DEFAULT NULL);
      
    • The return string uses lowercase letters for IPv6 addresses.

    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'
    
  • 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. A warning is logged if you use this function when binlog_format is set to STATEMENT.

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

  • 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'));
            -> 1
    mysql> 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
    
  • 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'));
            -> 0
    mysql> 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
    
  • 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.

  • 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 session that holds the lock. Otherwise, it 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.

  • MASTER_POS_WAIT(log_name,log_pos[,timeout][,channel_name])

    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.

    The optional channel added in MySQL 5.7.6 enables you to choose which replication channel the function applies to. See Section 18.2.3, “Replication Channels” for more information.

    This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

  • 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 21.7, “Binary Logging of Stored Programs”, You might see this function in the output from mysqlbinlog.

    For your applications, you can obtain exactly the same result as in the example just shown by using simple aliasing, like this:

    mysql> SELECT 14 AS myname;
    +--------+
    | myname |
    +--------+
    |     14 |
    +--------+
    1 row in set (0.00 sec)
    

    See Section 14.2.9, “SELECT Syntax”, for more information about column aliases.

  • RELEASE_ALL_LOCKS()

    Releases all named locks held by the current session and returns the number of locks released (0 if there were none)

    This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

    This function was added in MySQL 5.7.5.

  • 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 14.2.3, “DO Syntax”.

    This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

  • SLEEP(duration)

    Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. The duration may have a fractional part. If the argument is NULL or negative, SLEEP() produces a warning, or an error in strict SQL mode.

    When sleep returns normally (without interruption), it returns 0:

    mysql> SELECT SLEEP(1000);
    +-------------+
    | SLEEP(1000) |
    +-------------+
    |           0 |
    +-------------+
    

    When SLEEP() is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error. This is true whether the query is killed or times out:

    • This statement is interrupted using KILL QUERY from another session:

      mysql> SELECT SLEEP(1000);
      +-------------+
      | SLEEP(1000) |
      +-------------+
      |           1 |
      +-------------+
      
    • This statement is interrupted by timing out:

      mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(1000);
      +-------------+
      | SLEEP(1000) |
      +-------------+
      |           1 |
      +-------------+
      

    When SLEEP() is only part of a query that is interrupted, the query returns an error:

    • This statement is interrupted using KILL QUERY from another session:

      mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
      ERROR 1317 (70100): Query execution was interrupted
      
    • This statement is interrupted by timing out:

      mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 FROM t1 WHERE SLEEP(1000);
      ERROR 3024 (HY000): Query execution was interrupted, maximum statement
      execution time exceeded
      

    This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

  • UUID()

    Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, A Universally Unique IDentifier (UUID) URN Namespace (http://www.ietf.org/rfc/rfc4122.txt).

    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 devices not connected to each other.

    Warning

    Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

    UUID() returns a value that conforms to UUID version 1 as described in RFC 4122. The value is a 128-bit number represented as a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:

    • The first three numbers are generated from the low, middle, and high parts of a timestamp. The high part also includes the UUID version number.

    • 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 device has no Ethernet card, or it is unknown how to find the hardware address of an interface on the host operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.

      The MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number.

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

    UUID() does not work with statement-based replication.

  • UUID_SHORT()

    Returns a short universal identifier as a 64-bit unsigned integer. Values returned by UUID_SHORT() differ from the string-format 128-bit identifiers returned by the UUID() function and have different uniqueness properties. The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:

    • The server_id value of the current server is between 0 and 255 and is unique among your set of master and slave servers

    • You do not set back the system time for your server host between mysqld restarts

    • You invoke UUID_SHORT() on average fewer than 16 million times per second between mysqld restarts

    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

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

User Comments
  Posted by Kenn O'Connell on November 3, 2007
As a note to INET_ATON : if you are using (PHP 4, PHP 5) and are looking to get the integer value of an IP address, i have found that the following works flawlessly for converting to and from IPv4 and it's integer equivalent.

$ip = "127.0.0.0"; // as an example

$integer_ip = (substr($ip, 0, 3) > 127) ? ((ip2long($ip) & 0x7FFFFFFF) + 0x80000000) : ip2long($ip);

echo $integer_ip; // integer value
echo long2ip($integer_ip); // dotted format
-----------------------
Results are as follows:
-----------------------
2130706432
127.0.0.0
-----------------------
255.255.255.255 (converts to) 4294967295 (and back to) 255.255.255.255
209.65.0.0 (converts to) 3510697984 (and back to) 209.65.0.0
12.0.0.0 (converts to) 201326592 (and back to) 12.0.0.0
1.0.0.0 (converts to) 16777216 (and back to) 1.0.0.0

While i understand that this is a MySQL comment section, it seems that many have the same issue regarding MySQL / PHP IPv4 address handling in databases, and as such have posted this as a way to help those who, like myself, were frustrated with IP addresses that were not converting properly.
  Posted by Bipul Kuri on March 16, 2012
To get Binary representation of a given IP (dot notation).
where ip='192.168.0.1' is a string
select if(length(CONV(INET_ATON(ip),10,2))<32,LPAD(CONV(INET_ATON(ip),10,2),32,'0'),CONV(INET_ATON(ip),10,2));

so
mysql> SELECT IF(
-> LENGTH( CONV( INET_ATON('192.168.0.1'), 10, 2) ) < 32,
-> LPAD( CONV( INET_ATON('192.168.0.1'), 10, 2), 32, '0'),
-> CONV( INET_ATON('192.168.0.1'), 10, 2)
-> ) as BinaryRep;
+----------------------------------+
| BinaryRep |
+----------------------------------+
| 11000000101010000000000000000001 |
+----------------------------------+
1 row in set (0.00 sec)

  Posted by Nicholas Sherlock on July 21, 2009
You can convert the 36 character string returned by UUID() into a nice compact 16-byte integer to store in a BINARY(16) column. This will give you more compact indexes, and will probably be faster too. Just use:

UNHEX(REPLACE(UUID(),'-',''))

  Posted by Pil Ho Kim on October 1, 2011
In case we want to implement UUID_SHORT using PHP to avoid excessive assess to MySQL when generating UUIDs a lot, below codes will work like MySQL UUID_SHORT. It onces reads two MySQL system variables, then calls uuid_short() as many as you want. This example assume that you implemented your own MySQLGetOneResult that returns the first value of the query.

<php>
$query = "SELECT VARIABLE_VALUE FROM information_schema.SESSION_VARIABLES WHERE VARIABLE_NAME = 'SERVER_ID'";
$mysql_server_id = YourImplementedMySQLGetOneResult($query);

$query = "SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'UPTIME'";
$server_startup_time_in_seconds = time() - intval(YourImplementedMySQLGetOneResult($query));

$uuid_short_count = 0;

function uuid_short() {
return ((($mysql_server_id)&255) << 56) + ($server_startup_time_in_seconds << 24) + $uuid_short_count++;
}
</php>
  Posted by Ben Griffin on March 25, 2013
I'm not really sure that the documentation explains whether the binary column is indexed as a number or as a binarystring. This becomes relevant regarding where the LSB is in UUID fields. Typically with sequences (eg auto-increment) it's the least significant value which changes most frequently, whereas with UUID() I notice that it is the most significant value which changes most frequently. Which generates the most efficient indices?

In the end, I guess I am wondering if it would be more efficient to index the reverse of the UUID when using it in it's binary format, or leave it as it is?

So the options I'm interested in are as follows (assumption is that a primary field of BINARY(16) is populated with the function):

CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(REPLACE(UUID(),'-',''));
or
CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(reverse(REPLACE(UUID(),'-','')));

Measuring appears to indicate that the non-reversed function is much, much faster. But I am not sure why that should be.
  Posted by Shlomi Noach on December 12, 2014
You can make UUID() work with statement based replication through assignment to session variable.

master> set @safe_uuid := UUID();
master> insert into test.uuid_test values (2, @safe_uuid);

-- The above replicates correctly to slaves even with statement based replication.

See: http://code.openark.org/blog/mysql/making-uuid-and-rand-replication-safe

Sign Up Login You must be logged in to post a comment.