The usual arithmetic operators are available. 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 argument is an unsigned integer, and the other argument is also an integer, the result will be an unsigned integer. See the section called “Cast Functions and Operators”.
mysql> SELECT 3+5;
-> 8
mysql> SELECT 3-5;
-> -2
Unary minus. Changes the sign of the argument.
mysql> SELECT - 2;
-> -2
Note that if this operator is used with a BIGINT, the return value is a BIGINT! This means that you should avoid using - on integers that may have the value of -2^63!
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.
mysql> SELECT 3/5;
-> 0.60
Division by zero produces a NULL result:
mysql> SELECT 102/(1-1);
-> NULL
A division will be calculated with BIGINT arithmetic only if performed in a context where its result is converted to an integer!
Integer division. Similar to FLOOR() but safe with BIGINT values.
mysql> SELECT 5 DIV 2;
-> 2
DIV is new in MySQL 4.1.0.
All mathematical functions return NULL in case of an error.
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.
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.000000
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
-> 1.570796
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.201358
mysql> SELECT ASIN('foo');
-> 0.000000
Returns the arc tangent of X, that is, the value whose tangent is X.
mysql> SELECT ATAN(2);
-> 1.107149
mysql> SELECT ATAN(-2);
-> -1.107149
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.785398
mysql> SELECT ATAN2(PI(),0);
-> 1.570796
Returns the smallest integer value not less than X.
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEIL(-1.23);
-> -1
Note that the return value is converted to a BIGINT!
The CEIL() alias was added in MySQL 4.0.6.
Returns the cosine of X, where X is given in radians.
mysql> SELECT COS(PI());
-> -1.000000
mysql> SELECT COT(12);
-> -1.57267341
mysql> SELECT COT(0);
-> NULL
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 be a string and will be treated as one if it is not.
mysql> SELECT CRC32('MySQL');
-> 3259397556
CRC32() is available as of MySQL 4.1.0.
Returns the argument X, converted from radians to degrees.
mysql> SELECT DEGREES(PI());
-> 180.000000
Returns the value of e (the base of natural logarithms) raised to the power of X.
mysql> SELECT EXP(2);
-> 7.389056
mysql> SELECT EXP(-2);
-> 0.135335
Returns the largest integer value not greater than X.
mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2
Note that the return value is converted to a BIGINT!
Returns the natural logarithm of X.
mysql> SELECT LN(2);
-> 0.693147
mysql> SELECT LN(-2);
-> NULL
This function was added in MySQL 4.0.3. It is synonymous with LOG(X) in MySQL.
If called with one parameter, this function returns the natural logarithm of X.
mysql> SELECT LOG(2);
-> 0.693147
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.000000
mysql> SELECT LOG(1,100);
-> NULL
The arbitrary base option was added in MySQL 4.0.3. LOG(B,X) is equivalent to LOG(X)/LOG(B).
Returns the base-2 logarithm of X.
mysql> SELECT LOG2(65536);
-> 16.000000
mysql> SELECT LOG2(-100);
-> NULL
LOG2() is useful for finding out how many bits a number would require for storage. This function was added in MySQL 4.0.3. In earlier versions, you can use LOG(X)/LOG(2) instead.
Returns the base-10 logarithm of X.
mysql> SELECT LOG10(2);
-> 0.301030
mysql> SELECT LOG10(100);
-> 2.000000
mysql> SELECT LOG10(-100);
-> NULL
Modulo (like the % operator in C). 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. The N MOD M syntax works only as of MySQL 4.1.
Returns the value of PI. The default number of decimals displayed is five, but MySQL internally uses the full double-precision value for PI.
mysql> SELECT PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116
Returns the value of X raised to the power of Y.
mysql> SELECT POW(2,2);
-> 4.000000
mysql> SELECT POW(2,-2);
-> 0.250000
Returns the argument X, converted from degrees to radians.
mysql> SELECT RADIANS(90);
-> 1.570796
Returns a random floating-point value in the range from 0 to 1.0. If an integer argument N is specified, it is used as the seed value (producing a repeatable sequence).
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
You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. As of MySQL 3.23, 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 of 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 a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.
Returns the argument X, rounded to the nearest integer. With two arguments, returns X rounded to D decimals. If D is negative, the integer part of the number is zeroed out.
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
Note that 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.
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
Returns the sine of X, where X is given in radians.
mysql> SELECT SIN(PI());
-> 0.000000
Returns the non-negative square root of X.
mysql> SELECT SQRT(4);
-> 2.000000
mysql> SELECT SQRT(20);
-> 4.472136
Returns the tangent of X, where X is given in radians.
mysql> SELECT TAN(PI()+1);
-> 1.557408
Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part. If D is negative, the integer part of the number is zeroed out.
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
Starting from MySQL 3.23.51, all numbers are rounded toward zero.
Note that decimal numbers are normally not stored as exact numbers in computers, but as double-precision values, so you may be surprised by the following result:
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1027
This happens because 10.28 is actually stored as something like 10.2799999999999999.