Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL CEIL() Function

The `CEIL()` function in MySQL returns the smallest integer value that is greater than or equal to a given number. It is commonly used to round up decimal values to the nearest integer.

Usage

The `CEIL()` function is used when you need to round up numeric values, typically in calculations involving fractions. It is especially useful in financial or statistical applications where rounding up is required.

sql
CEIL(number)

In this syntax, `number` is the value to be rounded up to the next whole integer.

Examples

1. Basic Usage

sql
SELECT CEIL(4.2);

This example returns `5`, as `CEIL()` rounds 4.2 up to the nearest integer.

2. Applying to Negative Numbers

sql
SELECT CEIL(-3.7);

In this case, the function returns `-3`, since `CEIL()` rounds up to the nearest integer closer to zero for negative numbers.

3. Using with a Column

sql
SELECT product_id, CEIL(price) AS rounded_price
FROM products;

Here, the `CEIL()` function is used to round up the `price` column values to the nearest integer for each product in the `products` table.

Additional Notes

  • Synonym: `CEIL()` is synonymous with `CEILING()` in MySQL, allowing flexibility for users familiar with different SQL dialects.
  • Zero and Integer Inputs: When `CEIL()` is applied to zero or an integer, it returns the same value, as there is no fractional part to round up.
  • Handling NULL Values: If the `CEIL()` function encounters a `NULL` value, it returns `NULL`.

Tips and Best Practices

  • Use for positive and negative numbers. The function works with both positive and negative numbers, rounding towards zero.
  • Combine with other functions. Pair `CEIL()` with `FLOOR()` or `ROUND()` for comprehensive rounding strategies.
  • Consider performance. When used in large datasets, ensure that the rounding is necessary, as it may impact performance. Utilize indices or logic to minimize performance impact.
  • Understand implications. Be aware of the impact of rounding up in financial calculations, as it can affect totals and averages.