FAQs Categories Client Server Commands Database Structure Table Types or Storage Engines Indexes SQL Statements Table Joins Funtions and Operators Tricky Select Queries Speed Up Queries Data Back Up General Questions Errors
 What do you want to ask today?

Home / Funtions and Operators / Question No: 233

# What are mathematical functions in MySQL?

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 |
+--------+--------+--------+

+----------+---------------+-----------------+
| 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 |
+------------------+------------------+------------------+
```