Bitwise OR:

mysql> SELECT 29 | 15;
        -> 31

The result is an unsigned 64-bit integer.

Bitwise AND:

mysql> SELECT 29 & 15;
        -> 13

The result is an unsigned 64-bit integer.

Bitwise XOR:

mysql> SELECT 1 ^ 1;
        -> 0
mysql> SELECT 1 ^ 0;
        -> 1
mysql> SELECT 11 ^ 3;
        -> 8

The result is an unsigned 64-bit integer.

Bitwise XOR was added in MySQL 4.0.2.

Shifts a longlong (BIGINT) number to the left.

mysql> SELECT 1 << 2;
        -> 4

The result is an unsigned 64-bit integer.

Shifts a longlong (BIGINT) number to the right.

mysql> SELECT 4 >> 2;
        -> 1

The result is an unsigned 64-bit integer.

Invert all bits.

mysql> SELECT 5 & ~1;
        -> 4

The result is an unsigned 64-bit integer.

Returns the number of bits that are set in the argument N.

mysql> SELECT BIT_COUNT(29);
        -> 4

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 usually secure enough.

The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.

Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1).

If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_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'));

You can get even more security by not transferring the key over the connection for each query, which can be accomplished by storing it in a server-side variable at connection time. For example:

SELECT @password:='my password';
INSERT INTO t VALUES (1,AES_ENCRYPT('text',@password));

AES_ENCRYPT() and AES_DECRYPT() were added in MySQL 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL.

Decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE().

Encrypt str using pass_str as the password. To decrypt the result, use DECODE().

The result is a binary string of the same length as str. If you want to save it in a column, use a BLOB column type.

Decrypts a string encrypted with DES_ENCRYPT(). On error, this function returns NULL.

Note that this function works only if MySQL has been configured with SSL support. See the section called “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 the SUPER 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 doesn't look like an encrypted string, MySQL will return the given crypt_str. DES_DECRYPT() was added in MySQL 4.0.1.

Encrypts the string with the given key using the Triple-DES algorithm. On error, this function returns NULL.

Note that this function works only if MySQL has been configured with SSL support. See the section called “Using Secure Connections”.

The encryption key to use is chosen based on the second argument to DES_ENCRYPT(), if one was given:

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

The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_num will be 127.

The string length for the result will be new_len = orig_len + (8-(orig_len % 8))+1. Each line in the DES key file has the following format:

key_num des_key_str

Each key_num must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_str is the string that will be used to encrypt the message. Between the number and the key there should be at least one space. The first key is the default key that is used if you don't specify any key argument to DES_ENCRYPT()

You can tell MySQL to read new key values from the key file with the FLUSH DES_KEY_FILE statement. This requires the RELOAD 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');

DES_ENCRYPT() was added in MySQL 4.0.1.

Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL 3.22.16, salt may be longer than two characters.) If no salt argument is given, a random value is used.

mysql> SELECT ENCRYPT('hello');
        -> 'VxuFAJXVARROc'

ENCRYPT() ignores all but the first eight characters of str, at least on some systems. This behavior is determined by the implementation of the underlying crypt() system call.

If crypt() is not available on your system, ENCRYPT() always returns NULL. Because of this, we recommend that you use MD5() or SHA1() instead, because those two functions exist on all platforms.

Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. 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."

MD5() was added in MySQL 3.23.2.

OLD_PASSWORD() is available as of MySQL 4.1, when the implementation of PASSWORD() was changed to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD(). the section called “Password Hashing in MySQL 4.1”.

Calculates and returns a password string from the plaintext password str, or NULL if the argument was NULL. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table.

mysql> SELECT PASSWORD('badpwd');
        -> '7f84554057dd964b'

PASSWORD() encryption is one-way (not reversible).

PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. See ENCRYPT().

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, use MD5() or SHA1() instead. Also see RFC 2195 for more information about handling passwords and authentication securely in your application.

Calculates an SHA1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. One of the possible uses for this function is as a hash key. You can also use it as a cryptographically safe function for storing passwords.

mysql> SELECT SHA1('abc');
        -> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1() was added in MySQL 4.0.2, and can be considered a cryptographically more secure equivalent of MD5(). SHA() is synonym for SHA1().

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. 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'

CHARSET() was added in MySQL 4.1.0.

Returns the collation coercibility value of the string argument.

mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
        -> 0
mysql> SELECT COERCIBILITY('abc');
        -> 3
mysql> SELECT COERCIBILITY(USER());
        -> 2

The return values have the following meanings:

Lower values have higher precedence.

COERCIBILITY() was added in MySQL 4.1.1.

Returns the collation for the character set of the string argument.

mysql> SELECT COLLATION('abc');
        -> 'latin1_swedish_ci'
mysql> SELECT COLLATION(_utf8'abc');
        -> 'utf8_general_ci'

COLLATION() was added in MySQL 4.1.0.

Returns the connection ID (thread ID) for the connection. Every connection has its own unique ID.

mysql> SELECT CONNECTION_ID();
        -> 23786

CONNECTION_ID() was added in MySQL 3.23.14.

Returns the username and hostname combination that the current session was authenticated as. This value corresponds to the MySQL account that determines your access privileges. It can be different from the value of USER().

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 the USER() function), the server authenticated the client using an anonymous user account (as seen by the empty username part of the CURRENT_USER() value). One way this might occur is that there is no account listed in the grant tables for davida.

CURRENT_USER() was added in MySQL 4.0.6.

Returns the default (current) database name.

mysql> SELECT DATABASE();
        -> 'test'

If there is no default database, DATABASE() returns NULL as of MySQL 4.1.1, and the empty string before that.

A SELECT statement may include a LIMIT 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 the LIMIT, but without running the statement again. To get this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. (If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not.)

Note that 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 without LIMIT, because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_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. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL 4.0.0.

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

mysql> SELECT LAST_INSERT_ID();
        -> 195

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your 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 update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0).

If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server.

If you use INSERT IGNORE and the record is ignored, the AUTO_INCREMENT counter still is incremented and LAST_INSERT_ID() returns the new value.

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

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 the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.

Note that mysql_insert_id() is only updated after INSERT and UPDATE statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

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

SESSION_USER() is a synonym for USER().

SYSTEM_USER() is a synonym for USER().

Returns the current MySQL username and hostname.

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 than that of CURRENT_USER().

Prior to MySQL 3.22.11, the function value does not include the client hostname. You can extract just the username part, regardless of whether the value includes a hostname part, like this:

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
        -> 'davida'

As of MySQL 4.1, USER() returns a value in the utf8 character set, so you should also make sure that the '@' string literal is interpreted in that character set:

mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
        -> 'davida'

Returns a string that indicates the MySQL server version.

mysql> SELECT VERSION();
        -> '4.1.3-beta-log'

Note that if your version string ends with -log this means that logging is enabled.

Returns the default value for a table column. Starting from MySQL 5.0.2, you will get an error if the column doesn't have a default value.

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

DEFAULT() was added in MySQL 4.1.0.

Formats the number X to a format like '#,###,###.##', rounded to D decimals, and returns the result as a string. If D is 0, the result will have no decimal point or fractional part.

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

Tries to obtain a lock with a name given by the string str, with 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 already 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).

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.

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

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

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*256^3 + 207*256^2 + 224*256 + 40.

As of MySQL 4.1.2, INET_ATON() also understands short-form IP addresses:

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
        -> 2130706433, 2130706433

INET_ATON() was added in MySQL 3.23.15.

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'

INET_NTOA() was added in MySQL 3.23.15.

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 on errors (such as incorrect arguments). IS_FREE_LOCK() was added in MySQL 4.0.2.

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. IS_USED_LOCK() was added in MySQL 4.1.0.

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 it had to wait for to get 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 already 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.

MASTER_POS_WAIT() was added in MySQL 3.23.32. The timeout argument was added in 4.0.10.

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 wasn't locked by this thread (in which case the lock is not released), and NULL if the named lock didn't exist. The lock will not exist if it was never obtained by a call to GET_LOCK() or if it already has been released.

The DO statement is convenient to use with RELEASE_LOCK(). See DO.

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

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:

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

Note that UUID() does not yet work with replication.

UUID() was added in MySQL 4.1.2.