How do the CEILING() and FLOOR() functions differ from the ROUND() function in MySQL?

CEILING() The function returns the smallest integer value that is not less than X. Consider the following example –

mysql> Select CEILING(3.46);
+---------------+
| CEILING(3.46) |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)  

mysql> Select CEILING(-6.43);
+----------------+
| CEILING(-6.43) |
+----------------+
|             -6 |
+----------------+
1 row in set (0.02 sec)

FLOOR() The function returns the largest integer value not greater than X. Consider the following example –

mysql> Select FLOOR(-6.43);
+--------------+
| FLOOR(-6.43) |
+--------------+
|           -7 |
+--------------+
1 row in set (0.00 sec)
 
mysql> Select FLOOR(3.46);
+-------------+
| FLOOR(3.46) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

ROUND() The function returns X rounded to the nearest integer. If the second argument D is supplied, the function returns X rounded to D decimal places. D must be positive, otherwise all digits to the right of the decimal point will be removed. Consider the following example-

mysql>SELECT ROUND(5.693893);
+---------------------------------------------------------+
|                    ROUND(5.693893)                      |
+---------------------------------------------------------+
|                           6                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)  

mysql>SELECT ROUND(5.693893,2);
+---------------------------------------------------------+
|                   ROUND(5.693893,2)                     |
+---------------------------------------------------------+
|                          5.69                           |
+---------------------------------------------------------+
1 row in set (0.00 sec)

From the above definition and example, we can observe the following differences between these three functions-

• ROUND() The function rounds the number up or down depending on the second parameter D and the number itself (D the number after the decimal place >= 5 or not).
• FLOOR() The function rounds a number toward zero and always down.
• CEILING()The function rounds a number away from zero and always upward.

mysql> Select ROUND(1.415,2),FLOOR(1.415),CEILING(1.415);
+----------------+--------------+----------------+
| ROUND(1.415,2) | FLOOR(1.415) | CEILING(1.415) |
+----------------+--------------+----------------+
|           1.42 |            1 |              2 |
+----------------+--------------+----------------+
1 row in set (0.00 sec)

The above is the detailed content of How do the CEILING() and FLOOR() functions differ from the ROUND() function in MySQL?. For more information, please follow other related articles on the PHP Chinese website!