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

MySQL FLOOR() Function

The `FLOOR()` function in MySQL is used to return the largest integer value that is less than or equal to a given number. It effectively rounds down a floating-point number to the nearest integer, operating on both positive and negative numbers.

Usage

The `FLOOR()` function is commonly used when you need to truncate decimal values to whole numbers without any rounding up. This can be useful in scenarios like calculating discounts or handling integer-based logic.

sql
FLOOR(number);

In this syntax, `number` is the numeric expression you want to round down. This could be a constant, a column value, or a more complex expression.

Examples

1. Basic Usage

sql
SELECT FLOOR(3.75);

This query returns `3`, as `FLOOR()` rounds 3.75 down to the nearest whole number.

2. Using FLOOR() with Negative Numbers

sql
SELECT FLOOR(-4.2);

In this example, the function returns `-5`, since `-5` is the largest integer less than or equal to `-4.2`, illustrating the concept of rounding down with negative numbers.

3. Applying FLOOR() in a Table Query

sql
SELECT product_name, FLOOR(price) AS discounted_price
FROM products;

This query selects each product's name and applies the `FLOOR()` function to the `price` column, effectively displaying the integer component of each price as `discounted_price`.

4. Using FLOOR() with Zero and Large Numbers

sql
SELECT FLOOR(0), FLOOR(123456789.987);

This query demonstrates that `FLOOR()` returns `0` for zero and `123456789` for a large number, showing consistent behavior across different input ranges.

5. Combining FLOOR() with Other Functions

sql
SELECT FLOOR(ABS(-5.7) * 2);

Here, `FLOOR()` is used in conjunction with `ABS()` to round down the absolute value of `-5.7` multiplied by `2`, illustrating its use in more complex expressions.

Tips and Best Practices

  • Use for integer calculations. `FLOOR()` is ideal when working with integers in calculations, avoiding unexpected rounding.
  • Consider performance. While `FLOOR()` is generally efficient, its use in computed columns or views may require indexing strategies for optimal performance.
  • Be aware of negative values. Keep in mind that `FLOOR()` can produce unexpected results with negative numbers due to its rounding down behavior.
  • Combine with other functions. Use `FLOOR()` with other functions like `ABS()` or `MOD()` for more complex mathematical operations.