MySQL in a Nutshell (2008)

Part II. SQL Statements and Functions

Chapter 13. Mathematical Functions

MySQL has many built-in mathematical functions that you can use in SQL statements for performing calculations on values in databases. Each function accepts either numbers or numeric columns for parameter values. All mathematical functions return NULL on error.

The following functions are covered in this chapter:

ABS(), ACOS(), ASIN(), ATAN(), ATAN2(), BIT_COUNT(), CEIL(), CEILING(), CONV(), COS(), COT(), DEGREES(), EXP(), FLOOR(), FORMAT(), GREATEST(), INET_ATON(), INET_NTOA(), LEAST(), LN(), LOG(), LOG2(), LOG10(), MOD(), OCT(), PI(), POW(), POWER(),RADIANS(), RAND(), ROUND(), SIGN(), SIN(), SQRT(), TAN(), TRUNCATE().

Functions in Alphabetical Order

The following is a list of MySQL mathematical functions in alphabetical order, along with descriptions of each and examples of their use.

Name

ABS()

Synopsis

ABS(number)

This function returns the absolute value of a given number. Here is an example:

SELECT ABS(-10);

+----------+

| ABS(-10) |

+----------+

| 10 |

+----------+

Name

ACOS()

Synopsis

ACOS(number)

This function returns the arc cosine, in radians, of a given number. For input greater than 1 or less than –1, NULL is returned. Here is an example:

SELECT ACOS(.5), ACOS(1.5);

+----------+-----------+

| ACOS(.5) | ACOS(1.5) |

+----------+-----------+

| 1.047198 | NULL |

+----------+-----------+

Name

ASIN()

Synopsis

ASIN(number)

This function returns the arcsine, in radians, of a given number. For input greater than 1 or less than –1, NULL is returned. Here is an example:

SELECT ASIN(1);

+----------+

| ASIN(1) |

+----------+

| 1.570796 |

+----------+

Name

ATAN()

Synopsis

ATAN(number[, ...])

This function returns the arctangent, in radians, of a given number. To determine the arctangent of two numbers (Y and X), add the optional second argument to the function or use ATAN2(). The value of Y for a Cartesian plane is given as the first argument and X as the second. Here is an example:

SELECT ATAN(2);

+----------+

| ATAN(2) |

+----------+

| 1.107149 |

+----------+

Name

ATAN2()

Synopsis

ATAN2(number, number)

This function returns the arctangent, in radians, of X and Y for a point on a Cartesian plane. The value for Y is given as the first argument and X as the second. The reverse function is TAN(). Here is an example:

SELECT ATAN2(10, 5);

+--------------+

| ATAN2(10, 5) |

+--------------+

| 1.107149 |

+--------------+

Name

BIT_COUNT()

Synopsis

BIT_COUNT(number)

This function returns the number of bits set in the argument, which is an integer that the function treats as a binary number.

SELECT BIT_COUNT(10), BIT_COUNT(11);

+---------------+---------------+

| BIT_COUNT(10) | BIT_COUNT(11) |

+---------------+---------------+

| 2 | 3 |

+---------------+---------------+

Name

CEIL()

Synopsis

CEIL(number)

This function rounds a given floating-point number up to the next higher integer. It’s an alias to CEILING().

SELECT CEIL(1), CEIL(1.1);

+---------+-----------+

| CEIL(1) | CEIL(1.1) |

+---------+-----------+

| 1 | 2 |

+---------+-----------+

Name

CEILING()

Synopsis

CEILING(number)

This function rounds a given floating-point number up to the next higher integer. It’s an alias to CEIL(). This function can be particularly useful when you want a numeric value for a time function, but without the decimal places (the microseconds) in the results:

SELECT NOW(), NOW() + 0, CEILING(NOW() + 0);

+---------------------+-----------------------+--------------------+

| NOW() | NOW() + 0 | CEILING(NOW() + 0) |

+---------------------+-----------------------+--------------------+

| 2007-07-16 00:07:14 | 20070716000714.000000 | 20070716000714 |

+---------------------+-----------------------+--------------------+

Name

CONV()

Synopsis

CONV(number, from_base, to_base)

This function converts a number from one numeric base system to another. The number to convert is given in the first argument, the base from which to convert the number in the second, and the base to which to convert the number in the third. The minimum base allowed is 2 and the maximum is 36. Here is an example:

SELECT CONV(4, 10, 2) AS 'Base-10 4 Converted',

CONV(100, 2, 10) AS 'Binary 100 Converted';

+---------------------+----------------------+

| Base-10 4 Converted | Binary 100 Converted |

+---------------------+----------------------+

| 100 | 4 |

+---------------------+----------------------+

Here, the number 4 under the base 10 system is converted to the base 2 or binary equivalent and back again.

Name

COS()

Synopsis

COS(number)

This function returns the cosine of number, where number is expressed in radians. Here is an example:

SELECT COS(2 * PI( ));

+----------------+

| COS(2 * PI( )) |

+----------------+

| 1 |

+----------------+

Name

COT()

Synopsis

COT(number)

This function returns the cotangent of a number. Here is an example:

SELECT COT(1);

+------------+

| COT(1) |

+------------+

| 0.64209262 |

+------------+

Name

DEGREES()

Synopsis

DEGREES(number)

This function converts radians to degrees.

SELECT DEGREES(PI( ));

+------------------+

| DEGREES(PI( )) |

+------------------+

| 180.000000 |

+------------------+

Name

EXP()

Synopsis

EXP(number)

This function returns the value of the natural logarithm base number e to the power of the given number.

SELECT EXP(1);

+----------+

| EXP(1) |

+----------+

| 2.718282 |

+----------+

Name

FLOOR()

Synopsis

FLOOR(number)

This function rounds a given floating-point number down to the next lower integer. It’s a counterpart to CEILING().

SELECT CEILING(1.1), FLOOR(1.1);

+--------------+------------+

| CEILING(1.1) | FLOOR(1.1) |

+--------------+------------+

| 2 | 1 |

+--------------+------------+

Name

FORMAT()

Synopsis

FORMAT(number, decimal)

This function returns the given floating-point number with a comma inserted between every three digits and a period before the number of decimal places specified in the second argument.

SELECT FORMAT(1000.375, 2)

AS Amount;

+----------+

| Amount |

+----------+

| 1,000.38 |

+----------+

Notice that the function rounds the number given to two decimal places.

Name

GREATEST()

Synopsis

GREATEST(value, value, ...)

This function compares two or more values, returning the greatest value. In an INTEGER data type context, all values are treated as integers for comparison. In a REAL data type context, all values are treated as REAL values for comparison. If any parameter contains a case-sensitive string (i.e., with a BINARY keyword), all values are compared as case-sensitive strings. Here is an example:

SELECT GREATEST(col1, col2, col3);

Name

INET_ATON()

Synopsis

INET_ATON(IP_address)

This function converts an Internet Protocol (IP) address in dot-quad notation to its numeric equivalent. The function INET_NTOA() can be used to reverse the results. Here is an example:

SELECT INET_ATON('12.127.17.72')

AS 'AT&T';

+-----------+

| AT&T |

+-----------+

| 209654088 |

+-----------+

This function is useful in sorting IP addresses that lexically might not sort properly. For instance, an address of 10.0.11.1 would come after 10.0.1.1 and before 10.0.2.1 under normal sort conditions in an ORDER BY clause.

Name

INET_NTOA()

Synopsis

INET_NTOA(IP_address)

This function converts the numeric equivalent of an IP address to its dot-quad notation. The function INET_ATON() can be used to reverse the results.

SELECT INET_NTOA('209654088')

AS 'AT&T';

+--------------+

| AT&T |

+--------------+

| 12.127.17.72 |

+--------------+

Name

LEAST()

Synopsis

LEAST(value, value, ...)

Use this function to compare two or more values and return the smallest value. In an INTEGER datatype context, all values are treated as integers for comparison. In a REAL data type context, all values are treated as REAL values for comparison. If any parameter contains a case-sensitive string (i.e., with a BINARY keyword), all values are compared as case-sensitive strings. Here is an example:

SELECT LEAST(col1, col2, col3);

Name

LN()

Synopsis

LN(number)

This function returns the natural logarithm of its input. Here is an example:

SELECT LN(5);

+----------+

| LN(5) |

+----------+

| 1.609438 |

+----------+

Name

LOG()

Synopsis

LOG(number[, base])

This function returns the logarithm of the first argument to the base indicated by the second argument. This is the same as using LOG(number)/LOG(base). If the function is called with only the first argument, its natural logarithm is returned; the function is equivalent to LN in that case. Here is an example:

SELECT LOG(5,4);

+------------+

| LOG(5,4) |

+------------+

| 1.16096405 |

+------------+

Name

LOG2()

Synopsis

LOG2(number)

This function returns the base 2 logarithm of a given number.

Name

LOG10()

Synopsis

LOG10(number)

This function returns the base 10 logarithm of a given number.

Name

MOD()

Synopsis

MOD(number, number)

number MOD number

This function returns the remainder of a number given in the first argument when divided by the number given in the second argument, the modulo. The function works the same as using the % operator between two given numbers. The second syntax shown is available as of version 4.1 of MySQL. Starting with version 4.1.7, fractional values may be given. Here is an example:

SELECT MOD(10, 3);

+------------+

| MOD(10, 3) |

+------------+

| 1 |

+------------+

Here’s an example of the alternate syntax:

SELECT 10 MOD 3;

+----------+

| 10 MOD 3 |

+----------+

| 1 |

+----------+

Name

OCT()

Synopsis

OCT(number)

This function returns the octal, or base 8, numeric system value of the given number. It returns NULL if the argument is NULL. Here is an example:

SELECT OCT(1), OCT(9), OCT(16);

+--------+--------+---------+

| OCT(8) | OCT(9) | OCT(16) |

+--------+--------+---------+

| 10 | 11 | 20 |

+--------+--------+---------+

Name

PI()

Synopsis

PI()

This function returns by default the first five decimal places of the number pi. You can adjust it to include more decimal places by adding a mask to the end of the function. There is no argument within the parentheses of the function. Here is an example:

SELECT PI( ), PI( ) + 0.0000000000;

+----------+----------------------+

| PI( ) | PI( ) + 0.0000000000 |

+----------+----------------------+

| 3.141593 | 3.1415926536 |

+----------+----------------------+

Name

POW()

Synopsis

POW(number, exponent)

This function returns the result of raising the number given in the first argument to the exponent given in the second argument. It’s an alias of POWER(). Here is an example:

SELECT POW(2, 4);

+-----------+

| POW(2, 4) |

+-----------+

| 16.000000 |

+-----------+

Name

POWER()

Synopsis

POWER(number, exponent)

This function returns the result of raising the number given in the first argument to the power of the number given in the second argument. It’s an alias for POW().

Name

RADIANS()

Synopsis

RADIANS()

This function converts degrees to radians. Here is an example:

SELECT RADIANS(180);

+-----------------+

| RADIANS(180) |

+-----------------+

| 3.1415926535898 |

+-----------------+

Name

RAND()

Synopsis

RAND([seed])

This function returns a random floating-point number from 0 to 1. A seed number may be passed as an argument to start the sequence of random numbers at a different point. Here is an example:

SELECT RAND( ), RAND( );

+------------------+------------------+

| RAND( ) | RAND( ) |

+------------------+------------------+

| 0.29085519843814 | 0.45449978900561 |

+------------------+------------------+

Note that rerunning this statement with the same seed will produce the same results. This type of sequence is properly known as a pseudorandom number generator, and is generally not considered strong enough for security purposes, but it is adequate for making random choices among a set of alternatives.

Name

ROUND()

Synopsis

ROUND(number[, precision])

This function rounds a number given in the first argument to the nearest integer. The number may be rounded to the number of decimal places given in the second argument. Here is an example:

SELECT ROUND(2.875), ROUND(2.875, 2);

+--------------+-----------------+

| ROUND(2.875) | ROUND(2.875, 2) |

+--------------+-----------------+

| 3 | 2.88 |

+--------------+-----------------+

Name

SIGN()

Synopsis

SIGN(number)

This function returns –1 if the given number is a negative, 0 if it is zero, and 1 if it is positive. Here is an example:

SELECT SIGN(-5);

+----------+

| SIGN(-5) |

+----------+

| -1 |

+----------+

Name

SIN()

Synopsis

SIN(number)

This function returns the sine of the number given, where number is expressed in radians. Here is an example:

SELECT SIN(.5 * PI( ));

+-----------------+

| SIN(.5 * PI( )) |

+-----------------+

| 1 |

+-----------------+

Name

SQRT()

Synopsis

SQRT(number)

This function returns the square root of its input, which must be a positive number. Here is an example:

SELECT SQRT(25);

+----------+

| SQRT(25) |

+----------+

| 5.000000 |

+----------+

Name

TAN()

Synopsis

TAN(number)

This function returns the tangent of an angle, where number is expressed in radians. It’s the reverse of ATAN2(). Here is an example:

SELECT ATAN2(1), TAN(0.785398);

+----------+---------------+

| ATAN2(1) | TAN(0.785398) |

+----------+---------------+

| 0.785398 | 1.000000 |

+----------+---------------+

Name

TRUNCATE()

Synopsis

TRUNCATE(number, number)

This function returns a number equivalent to its first argument, removing any digits beyond the number of decimal places specified in the second argument. The function does not round the number; use the ROUND() function instead. If 0 is given for the second argument, the decimal point and the fractional value are dropped. If a negative number is given as the second argument, the decimal point and the fractional value are dropped, and the number of positions given is zeroed out in the remaining integer. Here is an example:

SELECT TRUNCATE(321.1234, 2) AS '+2',

TRUNCATE(321.1234, 0) AS '0',

TRUNCATE(321.1234, -2) AS '-2';

+--------+-----+-----+

| +2 | 0 | -2 |

+--------+-----+-----+

| 321.12 | 321 | 300 |

+--------+-----+-----+

Notice that for the first field in the results, the last two decimal places are dropped. For the second field, the decimal point and all of the fractional value are dropped. For the third field, the decimal point and the fractional value are dropped, and because the second parameter is –2, the two least significant digits (starting from the right) of the integer are changed to zeros.