MySQL uses BIGINT
(64-bit) arithmetic for bit
operations, so these operators have a maximum range of 64 bits.
-
Bitwise OR:
mysql>
SELECT 29 | 15;
-> 31The result is an unsigned 64-bit integer.
-
Bitwise AND:
mysql>
SELECT 29 & 15;
-> 13The result is an unsigned 64-bit integer.
-
Bitwise XOR:
mysql>
SELECT 1 ^ 1;
-> 0 mysql>SELECT 1 ^ 0;
-> 1 mysql>SELECT 11 ^ 3;
-> 8The result is an unsigned 64-bit integer.
-
Shifts a longlong (
BIGINT
) number to the left.mysql>
SELECT 1 << 2;
-> 4The result is an unsigned 64-bit integer.
-
Shifts a longlong (
BIGINT
) number to the right.mysql>
SELECT 4 >> 2;
-> 1The result is an unsigned 64-bit integer.
-
Invert all bits.
mysql>
SELECT 5 & ~1;
-> 4The result is an unsigned 64-bit integer.
-
Returns the number of bits that are set in the argument
N
.mysql>
SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
-> 4, 3
The functions in this section perform encryption and decryption, and compression and uncompression:
Compression or encryption | Uncompression or decryption |
AES_ENCRYT() | AES_DECRYPT() |
COMPRESS() | UNCOMPRESS() |
ENCODE() | DECODE() |
DES_ENCRYPT() | DES_DECRYPT() |
ENCRYPT() | Not available |
MD5() | Not available |
OLD_PASSWORD() | Not available |
PASSWORD() | Not available |
SHA() or SHA1() | Not available |
Not available | UNCOMPRESSED_LENGTH() |
Note: The encryption and
compression functions return binary strings. For many of these
functions, the result might contain arbitrary byte values. If
you want to store these results, use a BLOB
column rather than a CHAR
or (before MySQL
5.0.3) VARCHAR
column to avoid potential
problems with trailing space removal that would change data
values.
Note: Exploits for the MD5 and SHA-1 algorithms have become known. You may wish to consider using one of the other encryption functions described in this section instead.
-
AES_ENCRYPT(
str
,key_str
),AES_DECRYPT(
crypt_str
,key_str
)These functions allow encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes.
AES_ENCRYPT()
encrypts a string and returns a binary string.AES_DECRYPT()
decrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument isNULL
, the result of this function is alsoNULL
.Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:
16 × (trunc(
string_length
/ 16) + 1)If
AES_DECRYPT()
detects invalid data or incorrect padding, it returnsNULL
. However, it is possible forAES_DECRYPT()
to return a non-NULL
value (possibly garbage) if the input data or the key is invalid.You can use the AES functions to store data in an encrypted form by modifying your queries:
INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
AES_ENCRYPT()
andAES_DECRYPT()
can be considered the most cryptographically secure encryption functions currently available in MySQL. -
Compresses a string and returns the result as a binary string. This function requires MySQL to have been compiled with a compression library such as
zlib
. Otherwise, the return value is alwaysNULL
. The compressed string can be uncompressed withUNCOMPRESS()
.mysql>
SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
-> 21 mysql>SELECT LENGTH(COMPRESS(''));
-> 0 mysql>SELECT LENGTH(COMPRESS('a'));
-> 13 mysql>SELECT LENGTH(COMPRESS(REPEAT('a',16)));
-> 15The compressed string contents are stored the following way:
-
Empty strings are stored as empty strings.
-
Non-empty strings are stored as a four-byte length of the uncompressed string (low byte first), followed by the compressed string. If the string ends with space, an extra ‘
.
’ character is added to avoid problems with endspace trimming should the result be stored in aCHAR
orVARCHAR
column. (Use ofCHAR
orVARCHAR
to store compressed strings is not recommended. It is better to use aBLOB
column instead.)
-
-
Decrypts the encrypted string
crypt_str
usingpass_str
as the password.crypt_str
should be a string returned fromENCODE()
. -
Encrypt
str
usingpass_str
as the password. To decrypt the result, useDECODE()
.The result is a binary string of the same length as
str
.The strength of the encryption is based on how good the random generator is. It should suffice for short strings.
-
DES_DECRYPT(
crypt_str
[,key_str
])Decrypts a string encrypted with
DES_ENCRYPT()
. If an error occurs, this function returnsNULL
.Note that this function works only if MySQL has been configured with SSL support. See Section 5.9.7, “Using Secure Connections”.
If no
key_str
argument is given,DES_DECRYPT()
examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have theSUPER
privilege. The key file can be specified with the--des-key-file
server option.If you pass this function a
key_str
argument, that string is used as the key for decrypting the message.If the
crypt_str
argument does not appear to be an encrypted string, MySQL returns the givencrypt_str
. -
DES_ENCRYPT(
str
[,{key_num
|key_str
}])Encrypts the string with the given key using the Triple-DES algorithm.
Note that this function works only if MySQL has been configured with SSL support. See Section 5.9.7, “Using Secure Connections”.
The encryption key to use is chosen based on the second argument to
DES_ENCRYPT()
, if one was given:Argument Description No argument The first key from the DES key file is used. key_num
The given key number (0-9) from the DES key file is used. key_str
The given key string is used to encrypt str
.The key file can be specified with the
--des-key-file
server option.The return string is a binary string where the first character is
CHAR(128 |
key_num
). If an error occurs,DES_ENCRYPT()
returnsNULL
.The 128 is added to make it easier to recognize an encrypted key. If you use a string key,
key_num
is 127.The string length for the result is given by this formula:
new_len
=orig_len
+ (8 - (orig_len
% 8)) + 1Each line in the DES key file has the following format:
key_num
des_key_str
Each
key_num
value must be a number in the range from0
to9
. Lines in the file may be in any order.des_key_str
is the string that is used to encrypt the message. There should be at least one space between the number and the key. The first key is the default key that is used if you do not specify any key argument toDES_ENCRYPT()
.You can tell MySQL to read new key values from the key file with the
FLUSH DES_KEY_FILE
statement. This requires theRELOAD
privilege.One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values.
mysql>
SELECT customer_address FROM customer_table
>WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
-
Encrypts
str
using the Unixcrypt()
system call and returns a binary string. Thesalt
argument should be a string with at least two characters. If nosalt
argument is given, a random value is used.mysql>
SELECT ENCRYPT('hello');
-> 'VxuFAJXVARROc'ENCRYPT()
ignores all but the first eight characters ofstr
, at least on some systems. This behavior is determined by the implementation of the underlyingcrypt()
system call.If
crypt()
is not available on your system (as is the case with Windows),ENCRYPT()
always returnsNULL
. -
Calculates an MD5 128-bit checksum for the string. The value is returned as a binary string of 32 hex digits, or
NULL
if the argument wasNULL
. The return value can, for example, be used as a hash key.mysql>
SELECT MD5('testing');
-> 'ae2b1fca515949e5d54fb22b8ed95575'This is the “RSA Data Security, Inc. MD5 Message-Digest Algorithm.”
If you want to convert the value to uppercase, see the description of binary string conversion given in the entry for the
BINARY
operator in Section 12.8, “Cast Functions and Operators”.See the note regarding the MD5 algorithm at the beginning this section.
-
OLD_PASSWORD()
was added to MySQL when the implementation ofPASSWORD()
was changed to improve security.OLD_PASSWORD()
returns the value of the old (pre-4.1) implementation ofPASSWORD()
as a binary string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to your version 5.0 MySQL server without locking them out. See Section 5.8.9, “Password Hashing as of MySQL 4.1”. -
Calculates and returns a password string from the plaintext password
str
and returns a binary string, orNULL
if the argument wasNULL
. This is the function that is used for encrypting MySQL passwords for storage in thePassword
column of theuser
grant table.mysql>
SELECT PASSWORD('badpwd');
-> '*AAB3E285149C0135D51A520E1940DD3263DC008C'PASSWORD()
encryption is one-way (not reversible).PASSWORD()
does not perform password encryption in the same way that Unix passwords are encrypted. SeeENCRYPT()
.Note: The
PASSWORD()
function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, considerMD5()
orSHA1()
instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications. -
Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a binary string of 40 hex digits, or
NULL
if the argument wasNULL
. One of the possible uses for this function is as a hash key. You can also use it as a cryptographic function for storing passwords.SHA()
is synonymous withSHA1()
.mysql>
SELECT SHA1('abc');
-> 'a9993e364706816aba3e25717850c26c9cd0d89d'SHA1()
can be considered a cryptographically more secure equivalent ofMD5()
. However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section. -
UNCOMPRESS(
string_to_uncompress
)Uncompresses a string compressed by the
COMPRESS()
function. If the argument is not a compressed value, the result isNULL
. This function requires MySQL to have been compiled with a compression library such aszlib
. Otherwise, the return value is alwaysNULL
.mysql>
SELECT UNCOMPRESS(COMPRESS('any string'));
-> 'any string' mysql>SELECT UNCOMPRESS('any string');
-> NULL -
UNCOMPRESSED_LENGTH(
compressed_string
)Returns the length that the compressed string had before being compressed.
mysql>
SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
-> 30
-
The
BENCHMARK()
function executes the expressionexpr
repeatedlycount
times. It may be used to time how quickly MySQL processes the expression. The result value is always0
. The intended use is from within the mysql client, which reports query execution times:mysql>
SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (4.74 sec)The time reported is elapsed time on the client end, not CPU time on the server end. It is advisable to execute
BENCHMARK()
several times, and to interpret the result with regard to how heavily loaded the server machine is. -
Returns the character set of the string argument.
mysql>
SELECT CHARSET('abc');
-> 'latin1' mysql>SELECT CHARSET(CONVERT('abc' USING utf8));
-> 'utf8' mysql>SELECT CHARSET(USER());
-> 'utf8' -
Returns the collation coercibility value of the string argument.
mysql>
SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
-> 0 mysql>SELECT COERCIBILITY(USER());
-> 3 mysql>SELECT COERCIBILITY('abc');
-> 4The return values have the meanings shown in the following table. Lower values have higher precedence.
Coercibility Meaning Example 0
Explicit collation Value with COLLATE
clause1
No collation Concatenation of strings with different collations 2
Implicit collation Column value 3
System constant USER()
return value4
Coercible Literal string 5
Ignorable NULL
or an expression derived fromNULL
Before MySQL 5.0.3, the return values are shown as follows, and functions such as
USER()
have a coercibility of 2:Coercibility Meaning Example 0
Explicit collation Value with COLLATE
clause1
No collation Concatenation of strings with different collations 2
Implicit collation Column value, stored routine parameter or local variable 3
Coercible Literal string -
Returns the collation of the string argument.
mysql>
SELECT COLLATION('abc');
-> 'latin1_swedish_ci' mysql>SELECT COLLATION(_utf8'abc');
-> 'utf8_general_ci' -
Returns the connection ID (thread ID) for the connection. Every connection has an ID that is unique among the set of currently connected clients.
mysql>
SELECT CONNECTION_ID();
-> 23786 -
Returns the username and hostname combination for the MySQL account that the server used to authenticate the current client. This account determines your access privileges. As of MySQL 5.0.10, within a stored routine that is defined with the
SQL SECURITY DEFINER
characteristic,CURRENT_USER()
returns the creator of the routine. The return value is a string in theutf8
character set.The value of
CURRENT_USER()
can differ from the value ofUSER()
.mysql>
SELECT USER();
-> 'davida@localhost' mysql>SELECT * FROM mysql.user;
ERROR 1044: Access denied for user ''@'localhost' to database 'mysql' mysql>SELECT CURRENT_USER();
-> '@localhost'The example illustrates that although the client specified a username of
davida
(as indicated by the value of theUSER()
function), the server authenticated the client using an anonymous user account (as seen by the empty username part of theCURRENT_USER()
value). One way this might occur is that there is no account listed in the grant tables fordavida
. -
Returns the default (current) database name as a string in the
utf8
character set. If there is no default database,DATABASE()
returnsNULL
. Within a stored routine, the default database is the database that the routine is associated with, which is not necessarily the same as the database that is the default in the calling context.mysql>
SELECT DATABASE();
-> 'test' -
A
SELECT
statement may include aLIMIT
clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without theLIMIT
, but without running the statement again. To obtain this row count, include aSQL_CALC_FOUND_ROWS
option in theSELECT
statement, and then invokeFOUND_ROWS()
afterward:mysql>
SELECT SQL_CALC_FOUND_ROWS * FROM
tbl_name
->WHERE id > 100 LIMIT 10;
mysql>SELECT FOUND_ROWS();
The second
SELECT
returns a number indicating how many rows the firstSELECT
would have returned had it been written without theLIMIT
clause. (If the precedingSELECT
statement does not include theSQL_CALC_FOUND_ROWS
option, thenFOUND_ROWS()
may return a different result whenLIMIT
is used than when it is not.)The row count available through
FOUND_ROWS()
is transient and not intended to be available past the statement following theSELECT SQL_CALC_FOUND_ROWS
statement. If you need to refer to the value later, save it:mysql>
SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
mysql>SET @rows = FOUND_ROWS();
If you are using
SELECT SQL_CALC_FOUND_ROWS
, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again withoutLIMIT
, because the result set need not be sent to the client.SQL_CALC_FOUND_ROWS
andFOUND_ROWS()
can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. UsingFOUND_ROWS()
allows you to determine how many other pages are needed for the rest of the result.The use of
SQL_CALC_FOUND_ROWS
andFOUND_ROWS()
is more complex forUNION
statements than for simpleSELECT
statements, becauseLIMIT
may occur at multiple places in aUNION
. It may be applied to individualSELECT
statements in theUNION
, or global to theUNION
result as a whole.The intent of
SQL_CALC_FOUND_ROWS
forUNION
is that it should return the row count that would be returned without a globalLIMIT
. The conditions for use ofSQL_CALC_FOUND_ROWS
withUNION
are:-
The
SQL_CALC_FOUND_ROWS
keyword must appear in the firstSELECT
of theUNION
. -
The value of
FOUND_ROWS()
is exact only ifUNION ALL
is used. IfUNION
withoutALL
is used, duplicate removal occurs and the value ofFOUND_ROWS()
is only approximate. -
If no
LIMIT
is present in theUNION
,SQL_CALC_FOUND_ROWS
is ignored and returns the number of rows in the temporary table that is created to process theUNION
.
-
-
LAST_INSERT_ID()
,LAST_INSERT_ID(
expr
)Returns the first automatically generated value that was set for an
AUTO_INCREMENT
column by the most recentINSERT
orUPDATE
statement to affect such a column.mysql>
SELECT LAST_INSERT_ID();
-> 195The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first
AUTO_INCREMENT
value generated for most recent statement affecting anAUTO_INCREMENT
column by that client. This value cannot be affected by other clients, even if they generateAUTO_INCREMENT
values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.The value of
LAST_INSERT_ID()
is not changed if you set theAUTO_INCREMENT
column of a row to a non-“magic” value (that is, a value that is notNULL
and not0
).Important: If you insert multiple rows using a single
INSERT
statement,LAST_INSERT_ID()
returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the sameINSERT
statement against some other server.For example:
mysql>
USE test;
Database changed mysql>CREATE TABLE t (
->id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
->name VARCHAR(10) NOT NULL
->);
Query OK, 0 rows affected (0.09 sec) mysql>INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec) mysql>SELECT * FROM t;
+----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ 1 row in set (0.01 sec) mysql>SELECT LAST_INSERT_ID();
+------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql>INSERT INTO t VALUES
->(NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | +----+------+ 4 rows in set (0.01 sec) mysql>SELECT LAST_INSERT_ID();
+------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)Although the second
INSERT
statement inserted three new rows intot
, the ID generated for the first of these rows was2
, and it is this value that is returned byLAST_INSERT_ID()
for the followingSELECT
statement.If you use
INSERT IGNORE
and the row is ignored, theAUTO_INCREMENT
counter is not incremented andLAST_INSERT_ID()
returns0
, which reflects that no row was inserted.If
expr
is given as an argument toLAST_INSERT_ID()
, the value of the argument is returned by the function and is remembered as the next value to be returned byLAST_INSERT_ID()
. This can be used to simulate sequences:-
Create a table to hold the sequence counter and initialize it:
mysql>
CREATE TABLE sequence (id INT NOT NULL);
mysql>INSERT INTO sequence VALUES (0);
-
Use the table to generate sequence numbers like this:
mysql>
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql>SELECT LAST_INSERT_ID();
The
UPDATE
statement increments the sequence counter and causes the next call toLAST_INSERT_ID()
to return the updated value. TheSELECT
statement retrieves that value. Themysql_insert_id()
C API function can also be used to get the value. See Section 22.2.3.36, “mysql_insert_id()
”.
You can generate sequences without calling
LAST_INSERT_ID()
, but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue theUPDATE
statement and get their own sequence value with theSELECT
statement (ormysql_insert_id()
), without affecting or being affected by other clients that generate their own sequence values.Note that
mysql_insert_id()
is only updated afterINSERT
andUPDATE
statements, so you cannot use the C API function to retrieve the value forLAST_INSERT_ID(
expr
) after executing other SQL statements likeSELECT
orSET
. -
-
ROW_COUNT()
returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from themysql_affected_rows()
C API function.mysql>
INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT ROW_COUNT();
+-------------+ | ROW_COUNT() | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql>DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec) mysql>SELECT ROW_COUNT();
+-------------+ | ROW_COUNT() | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)ROW_COUNT()
was added in MySQL 5.0.1. -
This function is a synonym for
DATABASE()
. It was added in MySQL 5.0.2. -
SESSION_USER()
is a synonym forUSER()
. -
SYSTEM_USER()
is a synonym forUSER()
. -
Returns the current MySQL username and hostname as a string in the
utf8
character set.mysql>
SELECT USER();
-> 'davida@localhost'The value indicates the username you specified when connecting to the server, and the client host from which you connected. The value can be different from that of
CURRENT_USER()
.You can extract only the username part like this:
mysql>
SELECT SUBSTRING_INDEX(USER(),'@',1);
-> 'davida' -
Returns a string that indicates the MySQL server version. The string uses the
utf8
character set.mysql>
SELECT VERSION();
-> '5.0.25-standard'Note that if your version string ends with
-log
this means that logging is enabled.
-
Returns the default value for a table column. Starting with MySQL 5.0.2, 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 toD
decimal places, and returns the result as a string. For details, see Section 12.3, “String Functions”. -
Tries to obtain a lock with a name given by the string
str
, using a timeout oftimeout
seconds. Returns1
if the lock was obtained successfully,0
if the attempt timed out (for example, because another client has previously locked the name), orNULL
if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). If you have a lock obtained withGET_LOCK()
, it is released when you executeRELEASE_LOCK()
, execute a newGET_LOCK()
, or your connection terminates (either normally or abnormally). Locks obtained withGET_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 formdb_name.str
orapp_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');
-> NULLThe second
RELEASE_LOCK()
call returnsNULL
because the lock'lock1'
was automatically released by the secondGET_LOCK()
call.Note: 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. -
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');
-> 3520061480The 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, 2130706433Note: When storing values generated by
INET_ATON()
, it is recommended that you use anINT 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 11.2, “Numeric Types”. -
Given a numeric network address (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). Returns1
if the lock is free (no one is using the lock),0
if the lock is in use, andNULL
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 returnsNULL
. -
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 whileMASTER_POS_WAIT()
is waiting, the function returnsNULL
. If the slave is past the specified position, the function returns immediately.If a
timeout
value is specified,MASTER_POS_WAIT()
stops waiting whentimeout
seconds have elapsed.timeout
must be greater than 0; a zero or negativetimeout
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.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 routines that contain references to local routine variables, as described in Section 17.4, “Binary Logging of Stored Routines and Triggers”, You might see this function in the output from mysqlbinlog.
-
Releases the lock named by the string
str
that was obtained withGET_LOCK()
. Returns1
if the lock was released,0
if the lock was not established by this thread (in which case the lock is not released), andNULL
if the named lock did not exist. The lock does not exist if it was never obtained by a call toGET_LOCK()
or if it has previously been released.The
DO
statement is convenient to use withRELEASE_LOCK()
. See Section 13.2.2, “DO
Syntax”. -
Sleeps (pauses) for the number of seconds given by the
duration
argument, then returns 0. IfSLEEP()
is interrupted, it returns 1. The duration may have a fractional part given in microseconds. This function was added in MySQL 5.0.12. -
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 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'Note that
UUID()
does not yet work with replication. -
-
In an
INSERT ... ON DUPLICATE KEY UPDATE
statement, you can use theVALUES(
col_name
) function in theUPDATE
clause to refer to column values from theINSERT
portion of the statement. In other words,VALUES(
col_name
) in theUPDATE
clause refers to the value ofcol_name
that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. TheVALUES()
function is meaningful only inINSERT ... ON DUPLICATE KEY UPDATE
statements and returnsNULL
otherwise. Section 13.2.4.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);