以下内容基于MySQL 5.6及更高，大部分函数5.5也基本适用，更低版本请参考对应版本手册，其内容整理自官方。

mysql常用函数被分为五类，分别为：

①Numeric：数字函数；

②String：字符串函数；
http://blog.itpub.net/29773961/viewspace-1813545/

③Date and time：日期和时间；
http://blog.itpub.net/29773961/viewspace-1808967/

④Control Flow：基于一个表达式的结果集选择不同的值（控制流函数）；
http://blog.itpub.net/29773961/viewspace-1813557/

⑤Aggregate：基于一列的多个值返回单一值（聚合函数）；
http://blog.itpub.net/29773961/viewspace-1813589/

【Numeric】：
ABS()：返回绝对值

mysql> SELECT ABS(-22), ABS(0), ABS(33);
+----------+--------+---------+
| ABS(-22) | ABS(0) | ABS(33) |
+----------+--------+---------+
| 22 | 0 | 33 |
+----------+--------+---------+
1 row in set (0.00 sec)

ACOS()：返回arccos

ASIN()：返回arcsin

ATAN()：返回arctan

CEIL()与CEILING()：向上取整

mysql> SELECT CEIL(-1.2), CEIL(1.8), CEILING(0.4), CEILING(0.8);
+------------+-----------+--------------+--------------+
| CEIL(-1.2) | CEIL(1.8) | CEILING(0.4) | CEILING(0.8) |
+------------+-----------+--------------+--------------+
| -1 | 2 | 1 | 1 |
+------------+-----------+--------------+--------------+
1 row in set (0.00 sec)

CONV()：不同进制的转换

mysql> SELECT CONV('a',16,2) 将16进制数a转换为2进制,
-> CONV('6E',18,8),
-> CONV(10+'10'+'10'+0xa,10,10);
+---------------------------------+-----------------+------------------------------+
| 将16进制数a转换为2进制 | CONV('6E',18,8) | CONV(10+'10'+'10'+0xa,10,10) |
+---------------------------------+-----------------+------------------------------+
| 1010 | 172 | 40 |
+---------------------------------+-----------------+------------------------------+
1 row in set (0.00 sec)

COS()：返回余弦值

COT()：返回余切值

CRC32()：返回循环冗余校验值

DEGREES()：将弧度变为角度

mysql> SELECT DEGREES(PI());
+---------------+
| DEGREES(PI()) |
+---------------+
| 180
+---------------+
1 row in set (0.01 sec)

EXP()：返回以e为底的某次方的值

FLOOR()：返回最大不超过表达式的整数

mysql> SELECT FLOOR(1.34), FLOOR(-0.1112), FLOOR(2.56);
+-------------+----------------+-------------+
| FLOOR(1.34) | FLOOR(-0.1112) | FLOOR(2.56) |
+-------------+----------------+-------------+
| 1 | -1 | 2 |
+-------------+----------------+-------------+
1 row in set (0.00 sec)

LN()：返回log以e为底，某指的对数

LOG10()：返回log以10为底，某值的对数

LOG2()：返回log以2为底，某值的对数

LOG(expr1,expr2)：返回以expr1为底数，expr2值的对数

MOD(expr1,expr2)：返回expr1模expr2的值。

PI()：返回π的值

POW(expr1,expr2)与POWER(expr1,expr2)：返回expr1的expr2次方的值

mysql> SELECT POW(1,99), POWER(1,99),
-> POW(2,10), POWER(-2,5);
+-----------+-------------+-----------+-------------+
| POW(1,99) | POWER(1,99) | POW(2,10) | POWER(-2,5) |
+-----------+-------------+-----------+-------------+
| 1 | 1 | 1024 | -32 |
+-----------+-------------+-----------+-------------+
1 row in set (0.02 sec)

RADIANS()：将角度变为弧度

mysql> SELECT RADIANS(180);
+-------------------+
| RADIANS(180) |
+-------------------+
| 3.141592653589793 |
+-------------------+
1 row in set (0.00 sec)

RAND()：返回一个浮点随机数（0~1开区间）

mysql> SELECT RAND();
+--------------------+
| rand() |
+--------------------+
| 0.7237686484342148 |
+--------------------+
1 row in set (0.00 sec)

ROUND()：将某值四舍五入

mysql> SELECT ROUND(PI()), ROUND(-2.333), ROUND(-2.7666), ROUND(3.611);
+-------------+---------------+----------------+--------------+
| ROUND(PI()) | ROUND(-2.333) | ROUND(-2.7666) | ROUND(3.611) |
+-------------+---------------+----------------+--------------+
| 3 | -2 | -3 | 4 |
+-------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

SIGN()：返回正负（-1=负值，0=零，1=正值)

mysql> SELECT SIGN(-3.14), SIGN(0), SIGN(22.1)
-> ;
+-------------+---------+------------+
| SIGN(-3.14) | SIGN(0) | SIGN(22.1) |
+-------------+---------+------------+
| -1 | 0 | 1 |
+-------------+---------+------------+
1 row in set (0.00 sec)

SIN()：返回正弦值

SQRT()：将某值开平方（当然用POW(expr1,1/2)也可以）

mysql> SELECT SQRT(1024),SQRT(9);
+------------+---------+
| SQRT(1024) | SQRT(9) |
+------------+---------+
| 32 | 3 |
+------------+---------+
1 row in set (0.00 sec)

TAN()：返回正切值

TRUNCATE(expr1,expr2)：将expr1返回以保留expr2个小数点

mysql> SELECT TRUNCATE(2.111111,3);
+----------------------+
| TRUNCATE(2.111111,3) |
+----------------------+
| 2.111 |
+----------------------+
1 row in set (0.00 sec)

作者公众号（持续更新）

ITPub博客

MySQL 数字函数大全

最新文章