Home / Funtions and Operators / Question No: 233

Numeric functions perform several types of operations, such as rounding, truncation, trigonometric calculations, or generating random numbers.

The ROUND() function performs rounding of its argument. The rounding method applied to the fractional part of a number depends on whether the number is an exact or approximate value:

• For positive exact values, ROUND() rounds up to the next integer if the fractional part is .5 or greater, and down to the next integer otherwise. For negative exact values, ROUND() rounds down to the next integer if the fractional part is .5 or greater, and up to the next integer otherwise. Another way to state this is that a fraction of .5 or greater rounds away from zero and a fraction less than .5 rounds toward zero:

  mysql> SELECT ROUND(28.5), ROUND(-28.5);
  +-------------+--------------+
  | ROUND(28.5) | ROUND(-28.5) |
  +-------------+--------------+
  | 29          | -29          |
  +-------------+--------------+
  

• For approximate values, ROUND() uses the rounding method provided in the C library used by the MySQL server. This can vary from system to system, but typically rounds to the nearest even integer:

  mysql> SELECT ROUND(2.85E1), ROUND(-2.85E1);
  +---------------+----------------+
  | ROUND(2.85E1) | ROUND(-2.85E1) |
  +---------------+----------------+
  |            28 |            -28 |
  +---------------+----------------+
  

FLOOR() and CEILING() Functions

FLOOR() returns the largest integer not greater than its argument, and CEILING() returns the smallest integer not less than its argument:

mysql> SELECT FLOOR(-14.7), FLOOR(14.7);
+--------------+-------------+
| FLOOR(-14.7) | FLOOR(14.7) |
+--------------+-------------+
|          -15 |          14 |
+--------------+-------------+

mysql> SELECT CEILING(-14.7), CEILING(14.7);
+----------------+---------------+
| CEILING(-14.7) | CEILING(14.7) |
+----------------+---------------+
|            -14 |            15 |
+----------------+---------------+

ABS() and SIGN() Functions

ABS() and SIGN() extract the absolute value and sign of numeric values:

mysql> SELECT ABS(-14.7), ABS(14.7);
+------------+-----------+
| ABS(-14.7) | ABS(14.7) |
+------------+-----------+
| 14.7       | 14.7      |
+------------+-----------+

mysql> SELECT SIGN(-14.7), SIGN(14.7), SIGN(0);
+-------------+------------+---------+
| SIGN(-14.7) | SIGN(14.7) | SIGN(0) |
+-------------+------------+---------+
|          -1 |          1 |       0 |
+-------------+------------+---------+

SIN, COS, TAN, PI, DEGREES and RADIANS Functions

A family of functions performs trigonometric calculations, including conversions between degrees and radians:

mysql> SELECT SIN(0), COS(0), TAN(0);
+--------+--------+--------+
| SIN(0) | COS(0) | TAN(0) |
+--------+--------+--------+
|      0 |      1 |      0 |
+--------+--------+--------+

mysql> SELECT PI(), DEGREES(PI()), RADIANS(180);
+----------+---------------+-----------------+
| PI()     | DEGREES(PI()) | RADIANS(180)    |
+----------+---------------+-----------------+
| 3.141593 |           180 | 3.1415926535898 |
+----------+---------------+-----------------+

RAND Function

To generate random numbers, invoke the RAND() function:

mysql> SELECT RAND(), RAND(), RAND();
+------------------+------------------+------------------+
| RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+
| 0.55239934711941 | 0.16831658330589 | 0.18438490590489 |
+------------------+------------------+------------------+