12.9. Other Functions

MySQL 5.0

12.9. Other Functions

12.9.1. Bit Functions

MySQL uses (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

  • Bitwise OR:

    mysql> 
            -> 31
    

    The result is an unsigned 64-bit integer.

  • Bitwise AND:

    mysql> 
            -> 13
    

    The result is an unsigned 64-bit integer.

  • Bitwise XOR:

    mysql> 
            -> 0
    mysql> 
            -> 1
    mysql> 
            -> 8
    

    The result is an unsigned 64-bit integer.

  • Shifts a longlong () number to the left.

    mysql> 
            -> 4
    

    The result is an unsigned 64-bit integer.

  • Shifts a longlong () number to the right.

    mysql> 
            -> 1
    

    The result is an unsigned 64-bit integer.

  • Invert all bits.

    mysql> 
            -> 4
    

    The result is an unsigned 64-bit integer.

  • )

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

    mysql> 
            -> 4, 3
    

12.9.2. Encryption and Compression Functions

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 column rather than a or (before MySQL 5.0.3) 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.

  • ,), ,)

    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.

    encrypts a string and returns a binary string. decrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument is , the result of this function is also .

    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( / 16) + 1)
    

    If detects invalid data or incorrect padding, it returns . However, it is possible for to return a non- 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'));
    

    and 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 . Otherwise, the return value is always . The compressed string can be uncompressed with .

    mysql> 
            -> 21
    mysql> 
            -> 0
    mysql> 
            -> 13
    mysql> 
            -> 15
    

    The 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 a or column. (Use of or to store compressed strings is not recommended. It is better to use a column instead.)

  • ,)

    Decrypts the encrypted string using as the password. should be a string returned from .

  • ,)

    Encrypt using as the password. To decrypt the result, use .

    The result is a binary string of the same length as .

    The strength of the encryption is based on how good the random generator is. It should suffice for short strings.

  • [,])

    Decrypts a string encrypted with . If an error occurs, this function returns .

    Note that this function works only if MySQL has been configured with SSL support. See Section 5.9.7, “Using Secure Connections”.

    If no argument is given, 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 privilege. The key file can be specified with the server option.

    If you pass this function a argument, that string is used as the key for decrypting the message.

    If the argument does not appear to be an encrypted string, MySQL returns the given .

  • [,{|}])

    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 , if one was given:

    Argument Description
    No argument The first key from the DES key file is used.
    The given key number (0-9) from the DES key file is used.
    The given key string is used to encrypt .

    The key file can be specified with the server option.

    The return string is a binary string where the first character is ). If an error occurs, returns .

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

    The string length for the result is given by this formula:

     =  + (8 - ( % 8)) + 1
    

    Each line in the DES key file has the following format:

     
    

    Each value must be a number in the range from to . Lines in the file may be in any order. 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 to .

    You can tell MySQL to read new key values from the key file with the statement. This requires the 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> 
         > 
    
  • [,])

    Encrypts using the Unix system call and returns a binary string. The argument should be a string with at least two characters. If no argument is given, a random value is used.

    mysql> 
            -> 'VxuFAJXVARROc'
    

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

    If is not available on your system (as is the case with Windows), always returns .

  • )

    Calculates an MD5 128-bit checksum for the string. The value is returned as a binary string of 32 hex digits, or if the argument was . The return value can, for example, be used as a hash key.

    mysql> 
            -> '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 operator in Section 12.8, “Cast Functions and Operators”.

    See the note regarding the MD5 algorithm at the beginning this section.

  • )

    was added to MySQL when the implementation of was changed to improve security. returns the value of the old (pre-4.1) implementation of 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 and returns a binary string, or if the argument was . This is the function that is used for encrypting MySQL passwords for storage in the column of the grant table.

    mysql> 
            -> '*AAB3E285149C0135D51A520E1940DD3263DC008C'
    

    encryption is one-way (not reversible).

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

    Note: The function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider or 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 if the argument was . 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. is synonymous with .

    mysql> 
            -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
    

    can be considered a cryptographically more secure equivalent of . However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.

  • )

    Uncompresses a string compressed by the function. If the argument is not a compressed value, the result is . This function requires MySQL to have been compiled with a compression library such as . Otherwise, the return value is always .

    mysql> 
            -> 'any string'
    mysql> 
            -> NULL
    
  • )

    Returns the length that the compressed string had before being compressed.

    mysql> 
            -> 30
    

12.9.3. Information Functions

  • ,)

    The function executes the expression repeatedly times. It may be used to time how quickly MySQL processes the expression. The result value is always . The intended use is from within the mysql client, which reports query execution times:

    mysql> 
    +----------------------------------------------+
    | 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 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> 
            -> 'latin1'
    mysql> 
            -> 'utf8'
    mysql> 
            -> 'utf8'
    
  • )

    Returns the collation coercibility value of the string argument.

    mysql> 
            -> 0
    mysql> 
            -> 3
    mysql> 
            -> 4
    

    The return values have the meanings shown in the following table. Lower values have higher precedence.

    Coercibility Meaning Example
    Explicit collation Value with clause
    No collation Concatenation of strings with different collations
    Implicit collation Column value
    System constant return value
    Coercible Literal string
    Ignorable or an expression derived from

    Before MySQL 5.0.3, the return values are shown as follows, and functions such as have a coercibility of 2:

    Coercibility Meaning Example
    Explicit collation Value with clause
    No collation Concatenation of strings with different collations
    Implicit collation Column value, stored routine parameter or local variable
    Coercible Literal string
  • )

    Returns the collation of the string argument.

    mysql> 
            -> 'latin1_swedish_ci'
    mysql> 
            -> '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> 
            -> 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 characteristic, returns the creator of the routine. The return value is a string in the character set.

    The value of can differ from the value of .

    mysql> 
            -> 'davida@localhost'
    mysql> 
    ERROR 1044: Access denied for user ''@'localhost' to
    database 'mysql'
    mysql> 
            -> '@localhost'
    

    The example illustrates that although the client specified a username of (as indicated by the value of the function), the server authenticated the client using an anonymous user account (as seen by the empty username part of the value). One way this might occur is that there is no account listed in the grant tables for .

  • Returns the default (current) database name as a string in the character set. If there is no default database, returns . 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> 
            -> 'test'
    
  • A statement may include a 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 , but without running the statement again. To obtain this row count, include a option in the statement, and then invoke afterward:

    mysql> 
        -> 
    mysql> 
    

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

    The row count available through is transient and not intended to be available past the statement following the statement. If you need to refer to the value later, save it:

    mysql> 
    mysql> 
    

    If you are using , MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without , because the result set need not be sent to the client.

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

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

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

    • The keyword must appear in the first of the .

    • The value of is exact only if is used. If without is used, duplicate removal occurs and the value of is only approximate.

    • If no is present in the , is ignored and returns the number of rows in the temporary table that is created to process the .

  • , )

    Returns the first automatically generated value that was set for an column by the most recent or statement to affect such a column.

    mysql> 
            -> 195
    

    The 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 value generated for most recent statement affecting an column by that client. This value cannot be affected by other clients, even if they generate 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 is not changed if you set the column of a row to a non-“magic” value (that is, a value that is not and not ).

    Important: If you insert multiple rows using a single statement, returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same statement against some other server.

    For example:

    mysql> 
    Database changed
    mysql> 
        ->   
        ->   
        -> 
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> 
    Query OK, 1 row affected (0.01 sec)
    
    mysql> 
    +----+------+
    | id | name |
    +----+------+
    |  1 | Bob  |
    +----+------+
    1 row in set (0.01 sec)
    
    mysql> 
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                1 |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> 
        -> 
    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> 
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                2 |
    +------------------+
    1 row in set (0.00 sec)
    

    Although the second statement inserted three new rows into , the ID generated for the first of these rows was , and it is this value that is returned by for the following statement.

    If you use and the row is ignored, the counter is not incremented and returns , which reflects that no row was inserted.

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

    1. Create a table to hold the sequence counter and initialize it:

      mysql> 
      mysql> 
      
    2. Use the table to generate sequence numbers like this:

      mysql> 
      mysql> 
      

      The statement increments the sequence counter and causes the next call to to return the updated value. The statement retrieves that value. The C API function can also be used to get the value. See Section 22.2.3.36, “.

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

    Note that is only updated after and statements, so you cannot use the C API function to retrieve the value for ) after executing other SQL statements like or .

  • 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 C API function.

    mysql> 
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> 
    +-------------+
    | ROW_COUNT() |
    +-------------+
    |           3 |
    +-------------+
    1 row in set (0.00 sec)
    
    mysql> 
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> 
    +-------------+
    | ROW_COUNT() |
    +-------------+
    |           2 |
    +-------------+
    1 row in set (0.00 sec)
    

    was added in MySQL 5.0.1.

  • This function is a synonym for . It was added in MySQL 5.0.2.

  • is a synonym for .

  • is a synonym for .

  • Returns the current MySQL username and hostname as a string in the character set.

    mysql> 
            -> '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 .

    You can extract only the username part like this:

    mysql> 
            -> 'davida'
    
  • Returns a string that indicates the MySQL server version. The string uses the character set.

    mysql> 
            -> '5.0.25-standard'
    

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

12.9.4. Miscellaneous Functions

  • )

    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> 
    
  • ,)

    Formats the number to a format like , rounded to 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 , using a timeout of seconds. Returns if the lock was obtained successfully, if the attempt timed out (for example, because another client has previously locked the name), or 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 , it is released when you execute , execute a new , or your connection terminates (either normally or abnormally). Locks obtained with 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, 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 or .

    mysql> 
            -> 1
    mysql> 
            -> 1
    mysql> 
            -> 1
    mysql> 
            -> 1
    mysql> 
            -> NULL
    

    The second call returns because the lock was automatically released by the second call.

    Note: If a client attempts to acquire a lock that is already held by another client, it blocks according to the 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> 
            -> 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.

    also understands short-form IP addresses:

    mysql> 
            -> 2130706433, 2130706433
    

    Note: When storing values generated by , it is recommended that you use an column. If you use a (signed) 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> 
            -> '209.207.224.40'
    
  • )

    Checks whether the lock named is free to use (that is, not locked). Returns if the lock is free (no one is using the lock), if the lock is in use, and if an error occurs (such as an incorrect argument).

  • )

    Checks whether the lock named is in use (that is, locked). If so, it returns the connection identifier of the client that holds the lock. Otherwise, it returns .

  • ,[,])

    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 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 if the timeout has been exceeded. If the slave SQL thread stops while is waiting, the function returns . If the slave is past the specified position, the function returns immediately.

    If a value is specified, stops waiting when seconds have elapsed. must be greater than 0; a zero or negative means no timeout.

  • ,)

    Returns the given value. When used to produce a result set column, causes the column to have the given name.

    mysql> 
    +--------+
    | 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 that was obtained with . Returns if the lock was released, if the lock was not established by this thread (in which case the lock is not released), and if the named lock did not exist. The lock does not exist if it was never obtained by a call to or if it has previously been released.

    The statement is convenient to use with . See Section 13.2.2, “ Syntax”.

  • )

    Sleeps (pauses) for the number of seconds given by the argument, then returns 0. If 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 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 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> 
            -> '6ccd780c-baba-1026-9564-0040f4311e29'
    

    Note that does not yet work with replication.

  • )

    In an statement, you can use the ) function in the clause to refer to column values from the portion of the statement. In other words, ) in the clause refers to the value of that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The function is meaningful only in statements and returns otherwise. Section 13.2.4.3, “ Syntax”.

    mysql> 
        ->