MySQL 截取整数位、小数位以及四舍五入的方法

本文介绍了如何在MySQL中截取整数部分和小数部分，并展示了如何进行一位小数的四舍五入操作，适用于数据处理和精度调整场景。

 mysql> select locate('.',7.2);
+-----------------+
| locate('.',7.2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)
 
mysql> select left(7.2,locate('.',7.2));
+---------------------------+
| left(7.2,locate('.',7.2)) |
+---------------------------+
| 7.                        |
+---------------------------+
1 row in set (0.00 sec)
 
mysql> select left(7.2,locate('.',7.2)-1);
+-----------------------------+
| left(7.2,locate('.',7.2)-1) |
+-----------------------------+
| 7                           |
+-----------------------------+
1 row in set (0.00 sec)
 
 
mysql> select left(7.3,3) ;
+-------------+
| left(7.3,3) |
+-------------+
| 7.3         |
+-------------+
1 row in set (0.00 sec)
 
mysql> select left(7.3,2) ;
+-------------+
| left(7.3,2) |
+-------------+
| 7.          |
+-------------+
1 row in set (0.00 sec)
 
mysql> select left(7.3,1) ;
+-------------+
| left(7.3,1) |
+-------------+
| 7           |
+-------------+
1 row in set (0.00 sec)
 
 
 
mysql> select right(7.3,1) ;
+--------------+
| right(7.3,1) |
+--------------+
| 3            |
+--------------+
1 row in set (0.00 sec)
 
mysql> select right(7.3,2) ;
+--------------+
| right(7.3,2) |
+--------------+
| .3           |
+--------------+
1 row in set (0.00 sec)
 
mysql> select right(7.3,3) ;
+--------------+
| right(7.3,3) |
+--------------+
| 7.3          |
+--------------+
1 row in set (0.00 sec)
 

保留一位小数，四舍五入：

mysql> select round(7.16,1);
+---------------+
| round(7.16,1) |
+---------------+
|           7.2 |
+---------------+
1 row in set (0.00 sec)
 
mysql> select round(7.12,1);
+---------------+
| round(7.12,1) |
+---------------+
|           7.1 |
+---------------+
1 row in set (0.00 sec)

end