摘要：MySQL中的常用数值函数介绍及实测。
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)
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。