How to use ceil and floor in mysql

MysqlHow-ToBeginner · 3 min read

How to Use CEIL and FLOOR Functions in MySQL

In MySQL, use the CEIL() function to round a number up to the nearest integer, and the FLOOR() function to round a number down to the nearest integer. Both functions take a numeric value as input and return an integer.

📐

Syntax

The CEIL() and FLOOR() functions each take one numeric argument and return an integer.

CEIL(number): Rounds the number up to the smallest integer greater than or equal to the number.
FLOOR(number): Rounds the number down to the largest integer less than or equal to the number.

sql

SELECT CEIL(4.2) AS ceil_value, FLOOR(4.8) AS floor_value;

Output

+------------+-------------+ | ceil_value | floor_value | +------------+-------------+ | 5 | 4 | +------------+-------------+

💻

Example

This example shows how CEIL() rounds numbers up and FLOOR() rounds numbers down.

sql

SELECT
  CEIL(3.1) AS ceil_3_1,
  FLOOR(3.1) AS floor_3_1,
  CEIL(-3.7) AS ceil_neg_3_7,
  FLOOR(-3.7) AS floor_neg_3_7;

Output

+----------+------------+--------------+----------------+ | ceil_3_1 | floor_3_1 | ceil_neg_3_7 | floor_neg_3_7 | +----------+------------+--------------+----------------+ | 4 | 3 | -3 | -4 | +----------+------------+--------------+----------------+

⚠️

Common Pitfalls

One common mistake is confusing CEIL() and FLOOR() with simple rounding. CEIL() always rounds up, and FLOOR() always rounds down, regardless of the decimal part.

Also, negative numbers behave differently: CEIL(-3.7) returns -3 (rounds up towards zero), while FLOOR(-3.7) returns -4 (rounds down away from zero).

sql

/* Wrong: expecting CEIL to round normally */
SELECT CEIL(4.2) AS wrong_ceil; -- returns 5, not 4

/* Correct usage */
SELECT CEIL(4.2) AS correct_ceil; -- returns 5

Output

+------------+ | wrong_ceil | +------------+ | 5 | +------------+

📊

Quick Reference

Function	Description	Example	Result
CEIL(number)	Rounds number up to nearest integer	CEIL(4.3)	5
FLOOR(number)	Rounds number down to nearest integer	FLOOR(4.7)	4
CEIL(-3.7)	Rounds up (towards zero) for negatives	CEIL(-3.7)	-3
FLOOR(-3.7)	Rounds down (away from zero) for negatives	FLOOR(-3.7)	-4

✅

Key Takeaways

Use CEIL() to always round numbers up to the nearest integer in MySQL.

Use FLOOR() to always round numbers down to the nearest integer in MySQL.

CEIL() and FLOOR() behave differently with negative numbers; CEIL rounds towards zero, FLOOR rounds away from zero.

Neither function performs standard rounding; they strictly round up or down regardless of decimal value.

Both functions accept any numeric expression and return an integer.