mysql power函数数值类型_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)