6.3.6.2 Miscellaneous Functions

DATABASE()

Returns the current database name:

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

If there is no current database, DATABASE() returns the empty string.

USER()

SYSTEM_USER()

SESSION_USER()

Returns the current MySQL user name:

mysql> SELECT USER();
        -> 'davida@localhost'

In MySQL Version 3.22.11 or later, this includes the client hostname as well as the user name. You can extract just the user name part like this (which works whether the value includes a hostname part):

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

CURRENT_USER()

Returns the user name that the current session was authenticated as:

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'

PASSWORD(str)

OLD_PASSWORD(str)

Calculates a password string from the plaintext password str. 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 non-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.

ENCRYPT(str[,salt])

Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL Version 3.22.16, salt may be longer than two characters.):

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

ENCRYPT() ignores all but the first 8 characters of str, at least on some systems. This will be determined by the behaviour 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; these two functions exist on all platforms.

ENCODE(str,pass_str)

Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The results is a binary string of the same length as string. If you want to save it in a column, use a BLOB column type.

DECODE(crypt_str,pass_str)

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

MD5(string)

Calculates an MD5 128 bit checksum for the string. The value is returned as a 32 digit hex number that may, for example, be used as a hash key:

mysql> SELECT MD5("testing");
        -> 'ae2b1fca515949e5d54fb22b8ed95575'

This is the "RSA Data Security, Inc. MD5 Message-Digest Algorithm".

SHA1(string)

SHA(string)

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

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

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

AES_ENCRYPT(string,key_string)

AES_DECRYPT(string,key_string)

These functions allow encryption/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. As 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 are 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:

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

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

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

Encrypts the string with the given key using the Triple-DES algorithm. Note that this function only works if you have configured MySQL with SSL support. See section 4.3.9 Using Secure Connections. The encryption key to use is chosen the following way:

Argument	Description
Only one argument	The first key from des-key-file is used.
key number	The given key (0-9) from the des-key-file is used.
string	The given key_string will be used to crypt string_to_encrypt.

The return string will be a binary string where the first character will be CHAR(128 | key_number). The 128 is added to make it easier to recognise an encrypted key. If you use a string key, key_number will be 127. On error, this function returns NULL. The string length for the result will be new_length= org_length + (8-(org_length % 8))+1. The des-key-file has the following format:

key_number des_key_string
key_number des_key_string

Each key_number must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_string 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 will be 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 command. This requires the Reload_priv 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_DECRYPT(string_to_decrypt [, key_string])

Decrypts a string encrypted with DES_ENCRYPT(). Note that this function only works if you have configured MySQL with SSL support. See section 4.3.9 Using Secure Connections. If no key_string 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, then reads the key from the des-key-file to decrypt the message. For this to work the user must have the SUPER privilege. If you pass this function a key_string argument, that string is used as the key for decrypting the message. If the string_to_decrypt doesn't look like an encrypted string, MySQL will return the given string_to_decrypt. On error, this function returns NULL.

COMPRESS(string_to_compress)

Compresses a string.

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)));
        -> 15

COMPRESS() was added in MySQL version 4.1.1.

UNCOMPRESS(string_to_uncompress)

Uncompresses a string compressed by the COMPRESS() function.

mysql> SELECT UNCOMPRESS(COMPRESS("any string"));
        -> 'any string'

UNCOMPRESS() was added in MySQL version 4.1.1.

UNCOMPRESSED_LENGTH(compressed_string)

Returns the length of a compressed string before compressing.

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT("a",30)));
        -> 30

UNCOMPRESSED_LENGTH() was added in MySQL version 4.1.1.

LAST_INSERT_ID([expr])

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. See section 9.1.3.31 mysql_insert_id().

mysql> SELECT LAST_INSERT_ID();
        -> 195

The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column 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 expr is given as an argument to LAST_INSERT_ID(), then the value of the argument is returned by the function, and is set as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences: First create the table:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

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 (multi-user safe). You can retrieve the new ID as you would read any normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID() (without an argument) will return the new ID. The C API function mysql_insert_id() can also be used to get the value. Note that as mysql_insert_id() is only updated after INSERT and UPDATE statements, so you can't use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimals. 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'

VERSION()

Returns a string indicating the MySQL server version:

mysql> SELECT VERSION();
        -> '3.23.13-log'

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

CONNECTION_ID()

Returns the connection id (thread_id) for the connection. Every connection has its own unique id:

mysql> SELECT CONNECTION_ID();
        -> 1

GET_LOCK(str,timeout)

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, or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). A lock is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or the thread terminates. This function can be used to implement application locks or to simulate record locks. It blocks requests by other clients for locks with the same name; clients that agree on a given lock string name can use the string 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.

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 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 convinient to use with RELEASE_LOCK(). See section 6.4.10 DO Syntax.

IS_FREE_LOCK(str)

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

BENCHMARK(count,expr)

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 in 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 may be advisable to execute BENCHMARK() several times, and interpret the result with regard to how heavily loaded the server machine is.

INET_NTOA(expr)

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_ATON(expr)

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 example the above number is calculated as 209*256^3 + 207*256^2 + 224*256 +40.

MASTER_POS_WAIT(log_name, log_pos [, timeout])

Blocks until the slave reaches (that is, has read and applied all updates up to) the specified position in the master log. If master information is not initialised, or if the arguments are incorrect, returns NULL. If the slave is not running, will block and wait until it is started and goes to or past the specified position. If the slave is already past the specified position, returns immediately. If timeout (new in 4.0.10) is specified, will give up waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout. The return value is the number of log events it had to wait to get to the specified position, or NULL in case of error, or -1 if the timeout has been exceeded. This command is useful for control of master-slave synchronisation.

FOUND_ROWS()

Returns the number of rows that the preceding SELECT statement would have returned, if it had not been restricted with LIMIT. For FOUND_ROWS() to work correctly following a SELECT statement that includes a LIMIT clause, the statement must include the SQL_CALC_FOUND_ROWS option:

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. Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate all rows in the result set. However, this is faster than if you would not use LIMIT, as the result set need not be sent to the client. 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. SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL version 4.0.0.

User Comments

Experimentation has shown that if the client that
institutes a lock via GET_LOCK goes away (crashes,
exits, etc), the lock goes away as well. It's
pretty obvious, and definitely what you would
expect to happen, but this behavior probably
should be documented.

FORMAT(X,D) returns a formatted number
of type VARCHAR, NOT whatever
number type X was previously.

GET_LOCK also seems to be global across databases
in one instance of MySQL.

In the documentation for ENCODE:

ENCODE(str,pass_str)
Encrypt str using pass_str as the password. To
decrypt the result, use DECODE(). The results is a
binary string of the same length as string. If you
want to save it in a column, use a BLOB column type.

its says that you shoudl use a BLOB column type.
But it doesn't say why. I think the reason for
this is because other string data types don't
preserve spaces. And the encrypted string may have
trailing spaces that are significant, correct?

Otherwise somebody might try it with a VARCHAR()
for instance and it works and they wouldn't know
that its actually going to fail every once in a while.

As far as I can tell, you cannot use another function inside of the DECODE function (which happens to be something I wish it would do since the encoding process I chose to use in one case was based on variable information that is only retrievable via a function)

In response to Mike Wexler's comment:

The field needs a BLOB column because ENCODE() returns BINARY data, and a BLOB column is required to store binary data.

In response to Chris K's commment:

SELECT DECODE(ENCODE('My wonderful hat', 'abc'), 'abc')

works fine on 3.23.49-nt

GET_LOCK Problems:
If you turn of a client (eg. by pulling the power chord), the lock stays for a rather long time, at least if you use Microsoft NT as server.

There might be a timeout value you can set somehwere, but I have not found it yet.

Looking through the functions that were new in the 4.0.x series, I noticed the FOUND_ROWS() function.

What I don't get is why:

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

is any better than:
mysql> SELECT * FROM tbl_name
WHERE id > 100 LIMIT 10;
mysql> SELECT COUNT(*) FROM tbl_name
WHERE id > 100 LIMIT 10;

Has anyone tested this to see if there is any performance gain with the first method?

in response to Ross Bemrose:

The FOUND_ROWS() returns the number of results, regardless of a LIMIT statement.

SELECT COUNT(*) FROM sometable LIMIT 10

will always give a number 10 or less, regardless how many rows are in the table.

Where is FOUND_ROWS useful?

Let's say you have a guestbook. You want to list all entries where the country is Canada, but only display the first 10. You'd like to know how many entries there are, however, so you can display appropriate page indicators.

SELECT SQL_CALC_FOUND_ROWS * FROM guestbook WHERE Country='Canada' ORDER BY PostDate DESC LIMIT 0,10

Now you've got the first 10 records. You still don't know how many records there are in total. You COULD do:

SELECT COUNT(*) FROM guestbook WHERE Country='Canada'

but that's duplicating the same thing again.

So, it's much easier to use the new FOUND_ROWS feature:

SELECT FOUND_ROWS()

The md5() function is very useful for Password encryption. Keep in mind that we can not Decrypt it.
The most simplest method to use md5() function of MySQL with PHP is as follows (PHP Code):
Insert the record into the MySQL Database using a query like:

$query = "INSERT INTO user VALUES ('DummyUser',md5('DummyPassword'))";

And then for matching the password use:

$password = md5($password);
$query = "SELECT * FROM user WHERE username='DummyUser' AND password='DummyPassword'";

In the above code you can use your Variables instead of DummyUser & DummyPassword. The length of the Password field in my DB is 60 char.

Hope this helps!! :)

Add your own comment.