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.