MySQL numeric operators and functions

For ordinary addition and subtraction operations, we will not explain them in detail here, as shown in the following example:

mysql> SELECT 3+4;
+-----+
| 3+4 |
+-----+
|   7 |
+-----+
1 row in set (0.03 sec)

CEIL and FLOOR

CEIL is rounded up, as long as there are decimal places , no matter how many, enter one directly. For example:

mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
|          4 |
+------------+
1 row in set (0.02 sec)

FLOOR is the opposite of

mysql> SELECT FLOOR(3.99);
+-------------+
| FLOOR(3.99) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec）

p and MOD

p is rounded, MOD is remainder

mysql> SELECT 3/4;
+--------+
| 3/4    |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 3 p 4;
+---------+
| 3 p 4 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT 3 MOD 4;
+---------+
| 3 MOD 4 |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

Note: MOD can be replaced by %

POWER exponentiation

For example, 3 to the 2nd power

mysql> SELECT POWER(3,2);
+------------+
| POWER(3,2) |
+------------+
|          9 |
+------------+
1 row in set (0.14 sec)

ROUNDRounding

For example, 3.652 is rounded to two decimal places.

mysql> SELECT ROUND(3.652,2);
+----------------+
| ROUND(3.652,2) |
+----------------+
|           3.65 |
+----------------+
1 row in set (0.00 sec)

Retain one decimal place:

mysql> SELECT ROUND(3.652,1);
+----------------+
| ROUND(3.652,1) |
+----------------+
|            3.7 |
+----------------+
1 row in set (0.00 sec)

##TRUNCATE

Remove certain digits directly

mysql> SELECT TRUNCATE(125.89,2);
+--------------------+
| TRUNCATE(125.89,2) |
+--------------------+
|             125.89 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89,1);
+--------------------+
| TRUNCATE(125.89,1) |
+--------------------+
|              125.8 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89,0);
+--------------------+
| TRUNCATE(125.89,0) |
+--------------------+
|                125 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89,-1);
+---------------------+
| TRUNCATE(125.89,-1) |
+---------------------+
|                 120 |
+---------------------+
1 row in set (0.00 sec)

The above is the content of MySQL numerical operators and functions. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!