Table 11.19. Miscellaneous Functions
Name | Description |
---|---|
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 |
IS_FREE_LOCK() |
Checks whether the named lock is free |
IS_USED_LOCK() (v4.1.0) |
Checks 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() (v5.0.12) |
Causes the column to have the given name |
RAND() |
Return a random floating-point value |
RELEASE_LOCK() |
Releases the named lock |
SLEEP() (v5.0.12) |
Sleep for a number of seconds |
UUID_SHORT() (v5.1.20) |
Return an integer-valued universal identifier |
UUID() (v4.1.2) |
Return a Universal Unique Identifier (UUID) |
VALUES() (v4.1.1) |
Defines the values to be used during an INSERT |
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;
Formats the number X
to a format
like '#,###,###.##'
, rounded to
D
decimal places, and returns the
result as a string. For details, see
Section 11.4, “String Functions”.
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 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
.
mysql>SELECT GET_LOCK('lock1',10);
-> 1 mysql>SELECT IS_FREE_LOCK('lock2');
-> 1 mysql>SELECT GET_LOCK('lock2',10);
-> 1 mysql>SELECT RELEASE_LOCK('lock2');
-> 1 mysql>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.
If a client attempts to acquire a lock that is already
held by another client, it blocks according to the
timeout
argument. If the
blocked client terminates, its thread does not die until
the lock request times out. This is a known bug (fixed in
MySQL 5.4).
Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.
mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480
The generated number is always in network byte order. For the example just shown, the number is calculated as 209×2563 + 207×2562 + 224×256 + 40.
INET_ATON()
also understands
short-form IP addresses:
mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433
When storing values generated by
INET_ATON()
, it is
recommended that you use an INT
UNSIGNED
column. If you use a (signed)
INT
column, values
corresponding to IP addresses for which the first octet is
greater than 127 cannot be stored correctly. See
Section 10.2, “Numeric Types”.
Given a numeric network address in network byte order (4 or 8 byte), returns the dotted-quad representation of the address as a string.
mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'
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).
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
.
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.
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 was added in MySQL 5.0.12. It 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 18.6, “Binary Logging of Stored Programs”, You might see this function in the output from mysqlbinlog.
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 12.2.3, “DO
Syntax”.
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.
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:
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, MySQL uses a randomly generated 48-bit number.
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
The UUID()
function returns
a string using the character set defined by the
character_set_server
parameter. If you are using UUID values in your tables and
these columns are indexed the character set of your column
or table should match the character set used when the
UUID()
was called. If you
do not use the same character set for the column and the
UUID value, then the indexes on those columns will not be
used, which may lead to a reduction in performance and
locked tables during operations as the table is searched
sequentially for the value.
You can convert between different character sets when
using UUID-based strings using the
CONVERT()
function.
UUID()
does not work with
statement-based replication.
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 server_id
of the
current host is unique among your set of master and
slave servers
server_id
is between 0
and 255
You don't set back your system time for your server between mysqld restarts
You do not invoke
UUID_SHORT()
on average
more 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 that UUID_SHORT()
does
not work with statement-based replication.
This function was added in MySQL 5.1.20.
In an
INSERT
... ON DUPLICATE KEY UPDATE
statement, you can use
the
VALUES(
function in the col_name
)UPDATE
clause
to refer to column values from the
INSERT
portion of the
statement. In other words,
VALUES(
in the col_name
)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.
Section 12.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
I think the application level locks are great, but I think they have a drawback (possibly bug)
If you have a key that has been locked, and then another connection (thread) wants to acquire the lock (and say wants to wait indefinitely until it acuires the lock), when this application (that wants to acquire the lock) closes the database connection gracefully or not, the connection on the server-side is not closed. This could lead into zombie connections, I am thinking of reporting this as a bug.
Tips:
1. Trying to lock the same lock from the same connection that already has the lock will always succeede.
2. Trying to execute any statements from a connection that is waiting for a lock will not return untill the connection gets the lock.
I'd just like to add to the good point that Álvaro G. Vicario brought up.
As an alternative, you can actually force PHP to return an unsigned integer using printed formatting:
<?php
$long = ip2long('255.255.255.0'); // signed: -256
$u_long = sprintf('%u', $long); // unsigned: 4294967040
// blah blah ... add to table
?>
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.
To get Binary representation of a given IP (dot notation).
1 row in set (0.00 sec)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));
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(),'-',''))
Add your own comment.