Other Functions

The following functions include encryption functions, comparison functions, control flow functions, and other miscellaneous functions.

AES_DECRYPT

AES_DECRYPT(encrypted_string,key_string)

Decrypts the result of an AES_ENCRYPT() function.

AES_ENCRYPT

AES_ENCRYPT(string,key_string)

Uses the Advanced Encryption Standard algorithm (Rijndael) to encrypt the string based upon the key_string. This uses 128-bit key length by default. AES_DECRYPT() decrypts the result.

BENCHMARK

BENCHMARK(count,expression)

Runs the expression count times. Used mainly for testing to see how fast MySQL runs an expression. Always returns 0; the time (on the client) displayed below the function is the useful part of the output.

For example:

mysql> SELECT BENCHMARK(10000,SHA('how long')); +----------------------------------+ | BENCHMARK(10000,SHA('how long')) | +----------------------------------+ |                                0 | +----------------------------------+ 1 row in set (0.95 sec)

CASE

CASE value WHEN [compare_value1] THEN result1 [WHEN [compare_value2]  THEN result2 ...] [ELSE result3] END CASE WHEN [condition1] THEN result1 [WHEN [condition2]  THEN result2 ...] [ELSE result3] END

There are two incantations of the CASE statement. The first returns a result depending on the value. It compares the value to the various compare_values and returns the result associated with that value (after the THEN), returns the result after the ELSE if none are found, or returns NULL if there is no result to return.

The second compares the various conditions and returns the associated result when it finds a true condition, returns the result after the ELSE if none are found, or returns NULL if there is no result to return.

For example:

mysql> SELECT CASE 'a' WHEN 'a' THEN 'a it is' END; +--------------------------------------+ | CASE 'a' WHEN 'a' THEN 'a it is' END | +--------------------------------------+ | a it is                              | +--------------------------------------+ mysql> SELECT CASE 'b' WHEN 'a' THEN 'a it is' WHEN 'b' THEN 'b it is' END; +--------------------------------------------------------------+ | CASE 'b' WHEN 'a' THEN 'a it is' WHEN 'b' THEN 'b it is' END | +--------------------------------------------------------------+ | b it is                                                      | +--------------------------------------------------------------+ mysql> SELECT CASE 9 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2' ELSE 'not found' END; +-------------------------------------------------------------------+ | CASE 9 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2' ELSE 'not found' END | +-------------------------------------------------------------------+ | not found                                                         | +-------------------------------------------------------------------+ mysql> SELECT CASE 9 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2' END; +---------------------------------------------------+ | CASE 9 WHEN 1 THEN 'is 1' WHEN 2 THEN 'is 2'  END | +---------------------------------------------------+ | NULL                                              | +---------------------------------------------------+ mysql> SELECT CASE WHEN 1>2 THEN '1>2' WHEN 2=2 THEN 'is 2' END; +----------------------------------------------------+ | CASE WHEN 1>2 THEN '1>2' WHEN 2=2 THEN 'is 2'  END | +----------------------------------------------------+ | is 2                                               | +----------------------------------------------------+ mysql> SELECT CASE WHEN 1>2 THEN '1>2' WHEN 2<2 THEN '2<2' ELSE 'none' END; +--------------------------------------------------------------+ | CASE WHEN 1>2 THEN '1>2' WHEN 2<2 THEN '2<2' ELSE 'none' END | +--------------------------------------------------------------+ | none                                                         | +--------------------------------------------------------------+ mysql> SELECT CASE WHEN BINARY 'a' = 'A' THEN 'bin' WHEN 'a'='A' THEN 'text' END; +--------------------------------------------------------------------+ | CASE WHEN BINARY 'a' = 'A' THEN 'bin' WHEN 'a'='A' THEN 'text' END | +--------------------------------------------------------------------+ | text                                                               | +--------------------------------------------------------------------+ mysql> SELECT CASE WHEN BINARY 1=1 THEN '1' WHEN 2=2 THEN '2' END; +-----------------------------------------------------+ | CASE WHEN BINARY 1=1 THEN '1' WHEN 2=2 THEN '2' END | +-----------------------------------------------------+ | 1                                                   | +-----------------------------------------------------+

The type of the return value (INTEGER, DOUBLE, or STRING) is the same as the type of the first returned value (the expression after the first THEN).

CAST

CAST(expression AS type)

Converts the expression to the specified type and returns the result. The types can be one of the following: BINARY, DATE, DATETIME, SIGNED, SIGNED INTEGER, TIME, UNSIGNED, and UNSIGNED INTEGER.

MySQL usually automatically converts types. For example, if you add two number strings, the result will be a numeric. Or if any part of a calculation is unsigned, the entire result will be unsigned. You can use CAST() to change this behavior.

For example:

mysql> SELECT "4" + "3"; +-----------+ | "4" + "3" | +-----------+ |         7 | +-----------+ mysql> SELECT CAST(("4"+"3") AS TIME); +-------------------------+ | CAST(("4"+"3") AS TIME) | +-------------------------+ | 7                       | +-------------------------+ mysql> SELECT CAST(50-60 AS UNSIGNED INTEGER); +---------------------------------+ | CAST(50-60 AS UNSIGNED INTEGER) | +---------------------------------+ |            18446744073709551606 | +---------------------------------+ mysql> SELECT CAST(50-60 AS SIGNED INTEGER); +-------------------------------+ | CAST(50-60 AS SIGNED INTEGER) | +-------------------------------+ |                           -10 | +-------------------------------+

Use CONVERT() for a synonym that uses ODBC syntax.

CONNECTION_ID

CONNECTION_ID()

Returns the unique thread_id of the connection.

For example:

mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ |               7 | +-----------------+

CONVERT

CONVERT(expression,type)

This is a synonym for CAST(expression AS type), which is the ANSI SQL99 syntax.

DATABASE

DATABASE()

Returns the name of the current database or returns an empty string if there is none.

For example:

mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test       | +------------+

DECODE

DECODE(encoded_string,password_string)

Decodes the encoded string using the password string and returns the result. The decoded string is usually generated by the ENCODE() function first.

For example:

mysql> SELECT DECODE('g','1'); +-----------------+ | DECODE('g','1') | +-----------------+ | a               | +-----------------+ mysql> SELECT DECODE('wer','1sz'); +---------------------+ | DECODE('wer','1sz') | +---------------------+ | ì8                  | +---------------------+

DES_DECRYPT

DES_DECRYPT(encrypted_string [, key_string])

Decrypts a string encrypted with DES_ENCRYPT().

DES_ENCRYPT

DES_ENCRYPT(string [, (key_number | key_string) ] )

Uses the Data Encryption Standard (DES) algorithm to encrypt the string and returns a binary string. If the optional key argument is omitted, the first key from the des-key file is used. If the key argument is a number (from 0–9), the corresponding key from the des-key file is used. If the key argument is a string, that will be the key.

If the key values change in the des-key file, MySQL can read the new values when you run a FLUSH_DES_KEY_FILE statement, which requires the reload permission.

This function only works if MySQL has Secure Sockets Layer (SSL) support.

ENCODE

ENCODE(string,password_string)

Returns an encoded binary string. You can use DECODE(), with the same password_string, to return the original string. The encoded and decoded strings will be the same length.

For example:

mysql> SELECT ENCODE('a','1'); +-----------------+ | ENCODE('a','1') | +-----------------+ | g               | +-----------------+ mysql> SELECT ENCODE('ah','2'); +------------------+ | ENCODE('ah','2') | +------------------+ | Uÿ               | +------------------+

ENCRYPT

ENCRYPT(string [, salt])

Encrypts a string using the Unix crypt() system call and returns the result. The optional salt argument is a string used in the encryption. Its specific behavior depends on the underlying system call.

For example:

mysql> SELECT ENCRYPT('keepmeout'); +----------------------+ | ENCRYPT('keepmeout') | +----------------------+ | V9tOly.dRY55k        | +----------------------+ mysql> SELECT ENCRYPT('keepmeout','ab'); +---------------------------+ | ENCRYPT('keepmeout','ab') | +---------------------------+ | abpr3o3DrHzJo             | +---------------------------+

FOUND_ROWS

FOUND_ROWS() 

Returns the number of rows that satisfy the previous SELECT SQL_CALC_FOUND_ROWS query (or that would have been returned if it wasn't limited with a LIMIT clause).

For example:

mysql> SELECT SQL_CALC_FOUND_ROWS user FROM user LIMIT 1; +------+ | user | +------+ |      | +------+ 1 row in set (0.00 sec) mysql> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ |            4 | +--------------+

GET_LOCK

GET_LOCK(string,timeout)

Attempts to obtain a lock called string, for up to timeout seconds. It will return 1 if successful, 0 if it times out, or NULL if there was some other error. The lock is released with RELEASE_LOCK(), a new GET_LOCK(), or if the thread ends. You can use IS_FREE_LOCK() to check if a lock is free.

This is used mainly as an extra locking mechanism for applications.

For example:

mysql> SELECT GET_LOCK('one',1); +-------------------+ | GET_LOCK('one',1) | +-------------------+ |                 1 | +-------------------+

IF

IF(expression1,expression2,expression3)

Returns expression2 if expression1 is true; otherwise returns expression3. This can return a numeric or string depending on the context. expression1 is evaluated as an integer, so real comparisons may not produce the results you expect.

For example:

mysql> SELECT IF('a'='a',1,2); +-----------------+ | IF('a'='a',1,2) | +-----------------+ |               1 | +-----------------+ mysql> SELECT IF(9<4,1,2); +-------------+ | IF(9<4,1,2) | +-------------+ |           2 | +-------------+ mysql> SELECT IF(NULL,'a','b'); +------------------+ | IF(NULL,'a','b') | +------------------+ | b                | +------------------+ mysql> SELECT IF(16-6-10,'a',NULL); +----------------------+ | IF(16-6-10,'a',NULL) | +----------------------+ | NULL                 | +----------------------+

The next example returns false because the real number 0.49 is evaluated as the integer 0:

mysql> SELECT IF(0.49,'true','false'); +-------------------------+ | IF(0.49,'true','false') | +-------------------------+ | false                   | +-------------------------+

IFNULL

IFNULL(expression1,expression2)

Returns expression1 if it's not null; otherwise it returns expression2. The result can be numeric or string depending on the context.

For example:

mysql> SELECT IFNULL(1,2); +-------------+ | IFNULL(1,2) | +-------------+ |           1 | +-------------+ mysql> SELECT IFNULL(NULL,'nothing here'); +-----------------------------+ | IFNULL(NULL,'nothing here') | +-----------------------------+ | nothing here                | +-----------------------------+ mysql> SELECT IFNULL(RELEASE_LOCK('nonexistant'),'The lock never existed'); +--------------------------------------------------------------+ | IFNULL(RELEASE_LOCK('nonexistant'),'The lock never existed') | +--------------------------------------------------------------+ |                                       The lock never existed | +--------------------------------------------------------------+ 

INET_ATON

INET_ATON(dotted_quad_string)

Returns an integer 4- or 8-byte network address from the dotted quad string.

For example:

mysql> SELECT INET_ATON('196.26.90.168'); +----------------------------+ | INET_ATON('196.26.90.168') | +----------------------------+ |                 3290061480 | +----------------------------+

INET_NTOA

INET_NTOA(network_address)

Returns a dotted quad address from a 4- or 8-byte network address and returns a dotted quad string representing the dotted quad address.

For example:

mysql> SELECT INET_NTOA(3290061480); +-----------------------+ | INET_NTOA(3290061480) | +-----------------------+ | 196.26.90.168         | +-----------------------+

IS_FREE_LOCK

IS_FREE_LOCK(string)

Used to check whether a lock named string, created with GET_LOCK(), is free.

Returns 1 if the lock is free, 0 if the lock is held, or NULL on other errors.

For example:

mysql> SELECT GET_LOCK('one',1); +-------------------+ | GET_LOCK('one',1) | +-------------------+ |                 1 | +-------------------+ mysql> SELECT IS_FREE_LOCK('one'); +---------------------+ | IS_FREE_LOCK('one') | +---------------------+ |                   0 | +---------------------+ mysql> SELECT GET_LOCK('two',1); +-------------------+ | GET_LOCK('two',1) | +-------------------+ |                 1 | +-------------------+ mysql> SELECT IS_FREE_LOCK('one'); +---------------------+ | IS_FREE_LOCK('one') | +---------------------+ |                   1 | +---------------------+

LAST_INSERT_ID

LAST_INSERT_ID([expression])

Returns the last value inserted into an AUTO_INCREMENT field from this connection, or 0 if there have been none.

For example:

mysql> SELECT LAST_INSERT_ID(); +------------------+ | last_insert_id() | +------------------+ |                0 | +------------------+

MASTER_POS_WAIT

MASTER_POS_WAIT(log_name, log_position)

Used for replication synching. Run on the slave, this will wait until the slave has done all updates until the specified position in the master log file before continuing.

For example:

mysql> SELECT MASTER_POS_WAIT('g-bin.001',273); +-------------------------------------------+ | MASTER_POS_WAIT('g-bin.001',273)          | +-------------------------------------------+ |                                      NULL | +-------------------------------------------+

MD5

MD5(string)

Uses the Message Digest algorithm to calculate a 128-bit checksum from the string and returns the resulting 32-digit hexadecimal number.

For example:

mysql> SELECT MD5('how many more'); +----------------------------------+ | MD5('how many more')             | +----------------------------------+ | 75dea0eddd9ffb8db451448a9931e764 | +----------------------------------+

Use the SHA() function for a more cryptographically secure alternative.

NULLIF

NULLIF(expression1,expression2)

Returns expression1 unless expression1 is equal to expression2, in which case it returns NULL. This will evaluate expression1 twice if it's equal to expression2.

For example:

mysql> SELECT NULLIF('a','b'); +-----------------+ | NULLIF('a','b') | +-----------------+ | a               | +-----------------+ mysql> SELECT NULLIF(1,'1'); +---------------+ | NULLIF(1,'1') | +---------------+ |          NULL | +---------------+

PASSWORD

PASSWORD(string) 

Converts the string into an encrypted password and returns the result. This function is used for encrypting passwords in the user table of the mysql database. It it not reversible, and it is encrypted differently than the normal Unix password.

For example:

mysql> SELECT PASSWORD('a'); +------------------+ | PASSWORD('a')    | +------------------+ | 60671c896665c3fa | +------------------+ mysql> SELECT PASSWORD(PASSWORD('a')); +-------------------------+ | PASSWORD(PASSWORD('a')) | +-------------------------+ | 772a81723a030f10        | +-------------------------+

ENCRYPT() converts a string to a password the Unix way.

RELEASE_LOCK

RELEASE_LOCK(string)

Releases the lock string earlier obtained with GET_LOCK(). Returns 1 if the lock is released, 0 if it can't release the lock because this connection did not create it, or NULL if the lock does not exist (never created or already released).

For example:

mysql> SELECT GET_LOCK('one',1); +-------------------+ | GET_LOCK('one',1) | +-------------------+ |                 1 | +-------------------+ mysql> SELECT RELEASE_LOCK('one'); +---------------------+ | RELEASE_LOCK('one') | +---------------------+ |                   1 | +---------------------+ mysql> SELECT RELEASE_LOCK('one'); +---------------------+ | RELEASE_LOCK('one') | +---------------------+ |                NULL | +---------------------+ 

SESSION_USER

SESSION_USER()

Returns the MySQL user and host that are connected with the current thread.

For example:

mysql> SELECT SESSION_USER(); +----------------+ | SESSION_USER() | +----------------+ | root@localhost | +----------------+

SYSTEM_USER() and USER() are synonyms.

SHA

SHA(string)

Uses the Secure Hash algorithm to calculate a 160-bit checksum from the string and returns the resulting 40-digit hexadecimal number. It's a more secure encryption than that achieved with the MD5() function.

For example:

mysql> SELECT SHA('how many more'); +------------------------------------------+ | SHA('how many more')                     | +------------------------------------------+ | 38ccbb8146b0673fa91abba3239829af6f3e5a6b | +------------------------------------------+

SHA1

SHA1(string)

A synonym for SHA().

SYSTEM_USER

SYSTEM_USER()

A synonym for SESSION_USER().

USER

USER()

A synonym for SESSION_USER().

VERSION

VERSION()

Returns the MySQL server version as a string, with -log appended if logging is enabled.

For example:

mysql> SELECT VERSION(); +----------------+ | VERSION()      | +----------------+ | 4.0.3-beta-log | +----------------+