Note that in the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both arguments are integers.

If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer.

If any of the operands of a +, -, /, *, % is a real or string value, then the precision of the result is the precision of the argument with the maximum precision.

In multiplication and division, the precision of the result when using two exact values is the precision of the first argument + the value of the div_precision_increment global variable. For example, the expression 5.05 / 0.0014 would have a precision of six decimal places (3607.142857).

+

Addition:

mysql> SELECT 3+5;
        -> 8

-

Subtraction:

mysql> SELECT 3-5;
        -> -2

-

Unary minus. This operator changes the sign of the argument.

mysql> SELECT - 2;
        -> -2

Note: If this operator is used with a BIGINT, the return value is also a BIGINT. This means that you should avoid using – on integers that may have the value of –2⁶³.

*

Multiplication:

mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> 0

The result of the last expression is incorrect because the result of the integer multiplication exceeds the 64-bit range of BIGINT calculations. (See Section 11.2, “Numeric Types”.)

/

Division:

mysql> SELECT 3/5;
        -> 0.60

Division by zero produces a NULL result:

mysql> SELECT 102/(1-1);
        -> NULL

A division is calculated with BIGINT arithmetic only if performed in a context where its result is converted to an integer.

DIV

Integer division. Similar to FLOOR(), but is safe with BIGINT values.

mysql> SELECT 5 DIV 2;
        -> 2

N % M

Modulo operation. Returns the remainder of N divided by M. For more information, see the description for the MOD() function in Section 12.5.2, “Mathematical Functions”.

ABS(X)

Returns the absolute value of X.

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

This function is safe to use with BIGINT values.

ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');

+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X.

mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941

ATAN(Y,X), ATAN2(Y,X)

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.

mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949

CEILING(X), CEIL(X)

Returns the smallest integer value not less than X. CEILING() and CEIL() are synonymous.

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEIL(-1.23);
        -> -1

For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

COS(X)

Returns the cosine of X, where X is given in radians.

mysql> SELECT COS(PI());
        -> -1

COT(X)

Returns the cotangent of X.

mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> NULL

CRC32(expr)

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.

mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538

DEGREES(X)

Returns the argument X, converted from radians to degrees.

mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X.

mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);
        -> 0.13533528323661
mysql> SELECT EXP(0);
        -> 1

FLOOR(X)

Returns the largest integer value not greater than X.

mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2

For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 12.4, “String Functions”.

LN(X)

Returns the natural logarithm of X; that is, the base-e logarithm of X.

mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL

This function is synonymous with LOG(X).

LOG(X), LOG(B,X)

If called with one parameter, this function returns the natural logarithm of X.

mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL

If called with two parameters, this function returns the logarithm of X for an arbitrary base B.

mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2

LOG(B,X) is equivalent to LOG(X) / LOG(B).

LOG2(X)

Returns the base-2 logarithm of X.

mysql> SELECT LOG2(65536);
        -> 16
mysql> SELECT LOG2(-100);
        -> NULL

LOG2() is useful for finding out how many bits a number requires for storage. This function is equivalent to the expression LOG(X) / LOG(2).

LOG10(X)

Returns the base-10 logarithm of X.

mysql> SELECT LOG10(2);
        -> 0.30102999566398
mysql> SELECT LOG10(100);
        -> 2
mysql> SELECT LOG10(-100);
        -> NULL

LOG10(X) is equivalent to LOG(10,X).

MOD(N,M), N % M, N MOD M

Modulo operation. Returns the remainder of N divided by M.

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

This function is safe to use with BIGINT values.

MOD() also works on values that have a fractional part and returns the exact remainder after division:

mysql> SELECT MOD(34.5,3);
        -> 1.5

MOD(N,0) returns NULL.

PI()

Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116

POW(X,Y), POWER(X,Y)

Returns the value of X raised to the power of Y.

mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25

RADIANS(X)

Returns the argument X, converted from degrees to radians. (Note that π radians equals 180 degrees.)

mysql> SELECT RADIANS(90);
        -> 1.5707963267949

RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

mysql> SELECT RAND();
        -> 0.9233482386203
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND();
        -> 0.63553050033332
mysql> SELECT RAND();
        -> 0.70100469486881
mysql> SELECT RAND(20);
        -> 0.15888261251047

The effect of using a non-constant argument is undefined. As of MySQL 5.0.13, non-constant arguments are disallowed.

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.

ROUND(X), ROUND(X,D)

Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become zero.

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20

The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places):

mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+

Before MySQL 5.0.3, the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead.

As of MySQL 5.0.3, ROUND() uses the precision math library for exact-value arguments when the first argument is a decimal value:

The following example shows how rounding differs for exact and approximate values:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

For more information, see Chapter 21, Precision Math.

SIGN(X)

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.

mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1

SIN(X)

Returns the sine of X, where X is given in radians.

mysql> SELECT SIN(PI());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0

SQRT(X)

Returns the square root of a non-negative number X.

mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL

TAN(X)

Returns the tangent of X, where X is given in radians.

mysql> SELECT TAN(PI());
        -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
        -> 1.5574077246549

TRUNCATE(X,D)

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028

All numbers are rounded toward zero.