A function is a MySQL command used in an SQL statement that takes one or more arguments and returns a value based on the values supplied. Just as with expressions, you can use a function anywhere a fixed value could otherwise be used, or to manipulate the value returned in a database column.
Random NumbersTo generate a random number, use the RAND() function with no arguments. The result is a random decimal number between 0 and 1. mysql> SELECT RAND(), RAND(); +------------------+------------------+ | RAND() | RAND() | +------------------+------------------+ | 0.96684006784748 | 0.27750918760664 | +------------------+------------------+ 1 row in set (0.00 sec) In the previous example, two different calls to RAND() caused two different random numbers to be generated; a different value is returned each time RAND() is called, even within the same query.
An optional argument to RAND() specifies the random number seed. This resets the random number generator to a particular point in its sequence. Whenever the sequence is reset to a known point, the same sequence of random numbers will be generated.
Rounding FunctionsThis set of functions enables you to round decimal numbers in a particular way. Each of these functions takes a single decimal number argument and returns an integer result.
The ROUND() function rounds a value up or down to the nearest integer. mysql> SELECT ROUND(29.21), ROUND(32.76); +--------------+--------------+ | ROUND(29.21) | ROUND(32.76) | +--------------+--------------+ | 29 | 33 | +--------------+--------------+ 1 row in set (0.00 sec) If you supply a second argument, the number will be rounded to that amount of decimal places instead of an integer. mysql> SELECT ROUND(29.21, 1), ROUND(32.76, 1); +-----------------+-----------------+ | ROUND(29.21, 1) | ROUND(32.76, 1) | +-----------------+-----------------+ | 29.2 | 32.8 | +-----------------+-----------------+ 1 row in set (0.00 sec) The CEILING and FLOOR functions round a decimal number up and down to the nearest integer, respectively. mysql> SELECT CEILING(29.21), FLOOR(29.21); +----------------+--------------+ | CEILING(29.21) | FLOOR(29.21) | +----------------+--------------+ | 30 | 29 | +----------------+--------------+ 1 row in set (0.00 sec)
The TRUNCATE function takes two arguments, a number and a precision. It does not round the number, but simply removes from it decimal places in excess of the precision specified. mysql> SELECT TRUNCATE(12.34567, 3); +-----------------------+ | TRUNCATE(12.34567, 3) | +-----------------------+ | 12.345 | +-----------------------+ 1 row in set (0.00 sec) Mathematical FunctionsThis set of functions enables you to perform mathematical operations in a query. To raise a number to a power, use the POW() function. It takes two arguments: POW(a,b) raises a to the power b. mysql> SELECT POW(2,2), POW(2,3), POW(2,4); +----------+----------+----------+ | POW(2,2) | POW(2,3) | POW(2,4) | +----------+----------+----------+ | 4 | 8 | 16 | +----------+----------+----------+ 1 row in set (0.00 sec) To find the square root of a number, use SQRT() with a single argument. The following example also shows how functions can be nested by putting one function call as the argument to another. It calculates the square root of a number after it has just been squared. mysql> SELECT SQRT(POW(8,2)); +-----------------+ | SQRT(POW(8, 2)) | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec) To find the logarithm of a number, use the LOG() function. With one argument, a natural logarithm (base e) is returned. The opposite of LOG() is EXP()natural log base e is raised to the power given in the argument. In the following example, the first column shows the value of e. The second column performs a natural logarithm on this value. mysql> SELECT EXP(1), LOG(EXP(1)); +----------------+-------------+ | EXP(1) | LOG(EXP(1)) | +----------------+-------------+ | 2.718281828459 | 1 | +----------------+-------------+ 1 row in set (0.00 sec) When called with two arguments, the LOG() function uses the first argument as its base. For instance, LOG(10,num) returns the logarithm of num to base 10. Trigonometry FunctionsMySQL supports trigonometry functions to calculate the sine, cosine, and tangent of a value using SIN(), COS(), and TAN(), respectively. The argument to these functions should be a value in radians. The following examples use PI() to supply arguments to these functions that are based on [pi]. mysql> SELECT SIN(PI()/2), COS(2*PI()), TAN(PI()/4); +-------------+-------------+-------------+ | SIN(PI()/2) | COS(2*PI()) | TAN(PI()/4) | +-------------+-------------+-------------+ | 1 | 1 | 1 | +-------------+-------------+-------------+ 1 row in set (0.00 sec)
To convert a value in degrees to radians, use DEGREES(). Likewise, to convert radians to degrees, use RADIANS(). mysql> SELECT DEGREES(PI()/2), RADIANS(180); +-----------------+-----------------+ | DEGREES(PI()/2) | RADIANS(180) | +-----------------+-----------------+ | 90 | 3.1415926535898 | +-----------------+-----------------+ 1 row in set (0.00 sec) |