In MariaDB, FLOOR() is a built-in numeric function that returns the largest integer value not greater than its argument.

Syntax

The syntax goes like this:

FLOOR(X)

Where X is the value to apply the operation to.

Example

Here’s an example:

SELECT FLOOR(3.6789);

Result:

+---------------+
| FLOOR(3.6789) |
+---------------+
|             3 |
+---------------+

Passing a negative value produces the following result:

SELECT FLOOR(-3.6789);

Result:

+----------------+
| FLOOR(-3.6789) |
+----------------+
|             -4 |
+----------------+

Compared to ROUND()

The FLOOR() function is different to the ROUND() function. The ROUND() function would round the number up in some instances and down in others. The FLOOR() function, on the other hand, always returns the largest integer value not greater than its argument.

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789);

Result:

+---------------+---------------+
| FLOOR(3.6789) | ROUND(3.6789) |
+---------------+---------------+
|             3 |             4 |
+---------------+---------------+

Also, ROUND() allows us to specify the number of decimal places to round to:

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789, 2);

Result:

+---------------+------------------+
| FLOOR(3.6789) | ROUND(3.6789, 2) |
+---------------+------------------+
|             3 |             3.68 |
+---------------+------------------+

FLOOR() is also different to the TRUNCATE() function, which simply truncates the number to a given decimal place.

Non-Numeric Arguments

Here’s what happens when we provide a non-numeric argument:

SELECT FLOOR('Ten');

Result:

+--------------+
| FLOOR('Ten') |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.000 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Ten' |
+---------+------+-----------------------------------------+

Missing Arguments

Calling FLOOR() without an argument results in an error:

SELECT FLOOR();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FLOOR'