MySQL中的常用数值函数

原创巩飞 2020-02-22

832

MySQL的数值函数也非常丰富，下表就是5.7版本中支持的数值函数：

Name	Description
ABS()	Return the absolute value
ACOS()	Return the arc cosine
ASIN()	Return the arc sine
ATAN()	Return the arc tangent
ATAN2(), ATAN()	Return the arc tangent of the two arguments
CEIL()	Return the smallest integer value not less than the argument
CEILING()	Return the smallest integer value not less than the argument
CONV()	Convert numbers between different number bases
COS()	Return the cosine
COT()	Return the cotangent
CRC32()	Compute a cyclic redundancy check value
DEGREES()	Convert radians to degrees
EXP()	Raise to the power of
FLOOR()	Return the largest integer value not greater than the argument
LN()	Return the natural logarithm of the argument
LOG()	Return the natural logarithm of the first argument
LOG10()	Return the base-10 logarithm of the argument
LOG2()	Return the base-2 logarithm of the argument
MOD()	Return the remainder
PI()	Return the value of pi
POW()	Return the argument raised to the specified power
POWER()	Return the argument raised to the specified power
RADIANS()	Return argument converted to radians
RAND()	Return a random floating-point value
ROUND()	Round the argument
SIGN()	Return the sign of the argument
SIN()	Return the sine of the argument
SQRT()	Return the square root of the argument
TAN()	Return the tangent of the argument
TRUNCATE()	Truncate to specified number of decimal places

我们通过实例来研究下常用函数的用法。

ABS(x)，返回x的绝对值

root@database-one 22:40:  [gftest]> select abs(2),abs(-32),abs(null),abs('a');
+--------+----------+-----------+----------+
| abs(2) | abs(-32) | abs(null) | abs('a') |
+--------+----------+-----------+----------+
|      2 |       32 |      NULL |        0 |
+--------+----------+-----------+----------+
1 row in set, 1 warning (0.06 sec)

上面可以看到，null的结果继续为null，字符串的为0。

CEILING(x)，返回大于x的最小整数。CEIL()是CEILING()的同义词。

root@database-one 22:46:  [gftest]> select ceil(1.23),ceiling(-1.23),ceil(null),ceiling('a');
+------------+----------------+------------+--------------+
| ceil(1.23) | ceiling(-1.23) | ceil(null) | ceiling('a') |
+------------+----------------+------------+--------------+
|          2 |             -1 |       NULL |            0 |
+------------+----------------+------------+--------------+
1 row in set, 1 warning (0.01 sec)

上面可以看到，null的结果继续为null，字符串的为0。

FLOOR(x)，返回小于x的最大整数，和CEIL整好相反。

root@database-one 22:49:  [gftest]> select floor(1.23),floor(-1.23),floor(null),floor('a');
+-------------+--------------+-------------+------------+
| floor(1.23) | floor(-1.23) | floor(null) | floor('a') |
+-------------+--------------+-------------+------------+
|           1 |           -2 |        NULL |          0 |
+-------------+--------------+-------------+------------+
1 row in set, 1 warning (0.01 sec)

上面可以看到，null的结果继续为null，字符串的为0。

RAND([n])，返回0~1之间的随机值，0 <=返回值< 1.0

root@database-one 23:38:  [gftest]> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.6461971706209678 | 0.7088797136292604 | 0.6058070870170436 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

root@database-one 23:38:  [gftest]> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.9023963249310817 | 0.6945603943379227 | 0.7656130276300135 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

root@database-one 23:38:  [gftest]> select rand(),rand(),rand();
+-------------------+--------------------+--------------------+
| rand()            | rand()             | rand()             |
+-------------------+--------------------+--------------------+
| 0.744383985869944 | 0.4250808771933251 | 0.8922524590904382 |
+-------------------+--------------------+--------------------+
1 row in set (0.00 sec)

利用此函数配合计算可以生成任意指定范围内的随机数，比如需要0~100内的随机整数，可以如下操作：

root@database-one 23:40:  [gftest]> select ceil(100*rand()),ceil(100*rand()),ceil(100*rand());
+------------------+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+------------------+
|               19 |               26 |               71 |
+------------------+------------------+------------------+
1 row in set (0.07 sec)

root@database-one 23:41:  [gftest]> select ceil(100*rand()),ceil(100*rand()),ceil(100*rand());
+------------------+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+------------------+
|               79 |               80 |               61 |
+------------------+------------------+------------------+
1 row in set (0.12 sec)

root@database-one 23:41:  [gftest]> select ceil(100*rand()),ceil(100*rand()),ceil(100*rand());
+------------------+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+------------------+
|               64 |               37 |               94 |
+------------------+------------------+------------------+
1 row in set (0.03 sec)

当指定参数n时，RAND（n）返回相同的值：

root@database-one 23:44:  [gftest]> select rand(5),rand(5),rand(8),rand(32);
+---------------------+---------------------+---------------------+---------------------+
| rand(5)             | rand(5)             | rand(8)             | rand(32)            |
+---------------------+---------------------+---------------------+---------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 | 0.16107992190968237 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

root@database-one 23:45:  [gftest]> select rand(5),rand(5),rand(8),rand(32);
+---------------------+---------------------+---------------------+---------------------+
| rand(5)             | rand(5)             | rand(8)             | rand(32)            |
+---------------------+---------------------+---------------------+---------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 | 0.16107992190968237 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

root@database-one 23:45:  [gftest]> select rand(5),rand(5),rand(8),rand(32);
+---------------------+---------------------+---------------------+---------------------+
| rand(5)             | rand(5)             | rand(8)             | rand(32)            |
+---------------------+---------------------+---------------------+---------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 | 0.16107992190968237 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

ROUND(x,d)，返回参数x四舍五入有d位小数的值
如果不写d，则默认d为0，将x四舍五入后取整。

root@database-one 23:50:  [gftest]> SELECT ROUND(-1.23),ROUND(-1.58),ROUND(1.58),ROUND(1.298, 1),ROUND(1.298, 0),ROUND(23.298, -1);
+--------------+--------------+-------------+-----------------+-----------------+-------------------+
| ROUND(-1.23) | ROUND(-1.58) | ROUND(1.58) | ROUND(1.298, 1) | ROUND(1.298, 0) | ROUND(23.298, -1) |
+--------------+--------------+-------------+-----------------+-----------------+-------------------+
|           -1 |           -2 |           2 |             1.3 |               1 |                20 |
+--------------+--------------+-------------+-----------------+-----------------+-------------------+
1 row in set (0.03 sec)

函数的返回值跟x参数的数据类型一致：

root@database-one 23:51:  [gftest]> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+
1 row in set (0.03 sec)

TRUNCATE(x,d)，返回数字x截断为d位小数的值

root@database-one 00:03:  [gftest]> select truncate(-1.23,0),truncate(-1.58,0),truncate(1.58,0),truncate(1.298,1),truncate(1.298,0),round(23.298,-1);
+-------------------+-------------------+------------------+-------------------+-------------------+------------------+
| truncate(-1.23,0) | truncate(-1.58,0) | truncate(1.58,0) | truncate(1.298,1) | truncate(1.298,0) | round(23.298,-1) |
+-------------------+-------------------+------------------+-------------------+-------------------+------------------+
|                -1 |                -1 |                1 |               1.2 |                 1 |               20 |
+-------------------+-------------------+------------------+-------------------+-------------------+------------------+
1 row in set (0.02 sec)

root@database-one 00:04:  [gftest]> select truncate(150.000,2), truncate(150,2);
+---------------------+-----------------+
| truncate(150.000,2) | truncate(150,2) |
+---------------------+-----------------+
|              150.00 |             150 |
+---------------------+-----------------+
1 row in set (0.02 sec)

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySQL循序渐进（共42篇）

从搬砖到开车。

MySQL中的常用数值函数

文章被以下合辑收录

评论