笔者总结了一些常用的 MySQL 内置函数供读者复习查阅。
数值相关
ABS(x)
取绝对值
1
2
3
4
5
6
7
mysql> select abs(-10) as result;
+--------+
| result |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
CEIL(x)
取顶整数
1
2
3
4
5
6
7
mysql> select ceil(1.2) as result;
+--------+
| result |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
FLOOR(x)
取底整数
1
2
3
4
5
6
7
mysql> select floor(1.2) as result;
+--------+
| result |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
MOD(m,n)
取余
1
2
3
4
5
6
7
mysql> select mod(12,7) as result;
+--------+
| result |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)
RAND()
(0,1) 区间内的浮点随机数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select rand() as result;
+---------------------+
| result |
+---------------------+
| 0.23622484434044438 |
+---------------------+
1 row in set (0.01 sec)
mysql> select rand() as result;
+---------------------+
| result |
+---------------------+
| 0.31477422389646453 |
+---------------------+
1 row in set (0.00 sec)
ROUND(x,n)
四舍五入，取 n 个小数，若 n 不填或为0 则只保留整数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select round(5.757) as result;
+--------+
| result |
+--------+
| 6 |
+--------+
1 row in set (0.00 sec)
mysql> select round(5.757,2) as result;
+--------+
| result |
+--------+
| 5.76 |
+--------+
1 row in set (0.00 sec)
mysql> select round(5.757,5) as result;
+---------+
| result |
+---------+
| 5.75700 |
+---------+
1 row in set (0.00 sec)
TRUNCATE(x,n)
小数位截断 n 位。
1
2
3
4
5
6
7
mysql> select truncate(5.757,2) as result;
+--------+
| result |
+--------+
| 5.75 |
+--------+
1 row in set (0.00 sec)
AVG(column)
某列平均值
1
2
3
4
5
6
7
mysql> select avg(price) as avg_price from product;
+-----------+
| avg_price |
+-----------+
| 6.1250 |
+-----------+
1 row in set (0.00 sec)
COUNT(column)
某列行数
1
2
3
4
5
6
7
mysql> select count(*) as total_count from product;
+-------------+
| total_count |
+-------------+
| 96 |
+-------------+
1 row in set (0.00 sec)
MAX(column)
某列最大值
1
2
3
4
5
6
7
mysql> select max(price) as max_price from product;
+-----------+
| max_price |
+-----------+
| 24 |
+-----------+
1 row in set (0.00 sec)
MIN(column)
某列最小值
1
2
3
4
5
6
7
mysql> select min(price) as min_price from product;
+-----------+
| min_price |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
SUM(column)
求和
1
2
3
4
5
6
7
mysql> select sum(quantity) as total_quantity from product;
+----------------+
| total_quantity |
+----------------+
| 588 |
+----------------+
1 row in set (0.00 sec)
字符串相关
CONCAT(str1,str2,…)
将多个字符串拼接在一起
1
2
3
4
5
6
7
mysql> select concat('我','爱','python') as result;
+--------------+
| result |
+--------------+
| 我爱python |
+--------------+
1 row in set (0.01 sec)
LOWER(str)
将 str 转为全小写
1
2
3
4
5
6
7
mysql> select lower('I love mysql') as result;
+--------------+
| result |
+--------------+
| i love mysql |
+--------------+
1 row in set (0.00 sec)
UPPER(str)
将 str 转为全大写
1
2
3
4
5
6
7
mysql> select upper('I love mysql') as result;
+--------------+
| result |
+--------------+
| I LOVE MYSQL |
+--------------+
1 row in set (0.00 sec)
TRIM(str)
去除 str 首尾空格
1
2
3
4
5
6
7
mysql> select trim(' I love mysql ') as result;
+--------------+
| result |
+--------------+
| I love mysql |
+--------------+
1 row in set (0.00 sec)
LTRIM(str)
去除 str 左侧空格
1
2
3
4
5
6
7
mysql> select ltrim(' I love mysql ') as result;
+---------------+
| result |
+---------------+
| I love mysql |
+---------------+
1 row in set (0.00 sec)
RTRIM(str)
去除 str 右侧空格
1
2
3
4
5
6
7
mysql> select rtrim(' I love mysql ') as result;
+----------------+
| result |
+----------------+
| I love mysql |
+----------------+
1 row in set (0.00 sec)
LEFT(str,len)
截取 str 左侧 len 个字符
1
2
3
4
5
6
7
mysql> select left('亲亲我的宝贝',3) as result;
+-----------+
| result |
+-----------+
| 亲亲我 |
+-----------+
1 row in set (0.00 sec)
RIGHT(str,len)
截取 str 右侧 len 个字符
1
2
3
4
5
6
7
mysql> select right('我没说你很笨',3) as result;
+-----------+
| result |
+-----------+
| 你很笨 |
+-----------+
1 row in set (0.00 sec)
LPAD(str,len,pad_str)
在 str 左侧填充 pad_str，直到总字符数量到 len
1
2
3
4
5
6
7
mysql> select lpad('123',10,'0') as result;
+------------+
| result |
+------------+
| 0000000123 |
+------------+
1 row in set (0.00 sec)
RPAD(str,len,pad_str,len)
在 str 右侧填充 pad_str ，直到总字符数量到 len
1
2
3
4
5
6
7
mysql> select rpad('123',10,'0') as result;
+------------+
| result |
+------------+
| 1230000000 |
+------------+
1 row in set (0.00 sec)
REPEAT(str,count)
重复 str 多次
1
2
3
4
5
6
7
mysql> select repeat('哇',5) as result;
+-----------------+
| result |
+-----------------+
| 哇哇哇哇哇 |
+-----------------+
1 row in set (0.00 sec)
REPLACE(str,old_str,new_str)
将 str 中所有的 old_str 替换为 new_str
1
2
3
4
5
6
7
mysql> select replace('你很美','你','我') as result;
+-----------+
| result |
+-----------+
| 我很美 |
+-----------+
1 row in set (0.00 sec)
SUBSTRING(str,pos,len)
截取字符串，从 pos 处截取 len 个字符（从1开始计，截取结果包括 pos 处字符）
1
2
3
4
5
6
7
mysql> select substring('你看你，臭美死了',5,4) as result;
+--------------+
| result |
+--------------+
| 臭美死了 |
+--------------+
1 row in set (0.00 sec)
CHAR_LENGTH(str)
字符长度
1
2
3
4
5
6
7
mysql> select char_length('我喜欢mysql') as char_length;
+-------------+
| char_length |
+-------------+
| 8 |
+-------------+
1 row in set (0.00 sec)
LENGTH(str)
字节长度
1
2
3
4
5
6
7
mysql> select length('我喜欢mysql') as byte_length;
+-------------+
| byte_length |
+-------------+
| 14 |
+-------------+
1 row in set (0.00 sec)
一个中文字符占 3 个字节
PASSWORD(str)
加密字符串
1
2
3
4
5
6
7
mysql> select password('mysql') as result;
+-------------------------------------------+
| result |
+-------------------------------------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
MD5(str)
MD5 加密，32位小写
1
2
3
4
5
6
7
mysql> select md5('mysql') as result;
+----------------------------------+
| result |
+----------------------------------+
| 81c3b080dad537de7e10e0987a4bf52e |
+----------------------------------+
1 row in set (0.00 sec)
每个中文字符占用3个字节
时间相关
CURDATE()
当前日期
1
2
3
4
5
6
7
mysql> select curdate() as today;
+------------+
| today |
+------------+
| 2017-11-13 |
+------------+
1 row in set (0.00 sec)
CURTIME()
当前时间，不包含日期部分
1
2
3
4
5
6
7
mysql> select curtime() as current;
+----------+
| current |
+----------+
| 00:03:29 |
+----------+
1 row in set (0.00 sec)
NOW()
当前日期和时间
1
2
3
4
5
6
7
mysql> select now() as now;
+---------------------+
| now |
+---------------------+
| 2017-07-13 00:04:01 |
+---------------------+
1 row in set (0.00 sec)
YEAR(time)
获取指定日期的年份
1
2
3
4
5
6
7
mysql> select year('2017-07-10 18:00:00') as month;
+-------+
| month |
+-------+
| 2017 |
+-------+
1 row in set (0.00 sec)
MONTH(time)
获取指定日期的月份
1
2
3
4
5
6
7
mysql> select month('2017-07-10 18:00:00') as month;
+-------+
| month |
+-------+
| 7 |
+-------+
1 row in set (0.00 sec)
DAY(time)
获取指定时间的日
1
2
3
4
5
6
7
mysql> select day('2017-07-10 18:00:00') as day;
+------+
| day |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
HOUR(time)
获取指定时间的小时，24小时制
1
2
3
4
5
6
7
mysql> select hour('2017-07-10 18:00:00') as hour;
+------+
| hour |
+------+
| 18 |
+------+
1 row in set (0.00 sec)
MINUTE(time)
获取指定时间的分钟
1
2
3
4
5
6
7
mysql> select minute('2017-07-10 18:03:00') as minute;
+--------+
| minute |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
SECOND(time)
获取指定时间的秒
1
2
3
4
5
6
7
mysql> select second('2017-07-10 18:03:00') as second;
+--------+
| second |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP([date])
将时间转换为 unix 时间戳，若不指定时间，则获取当前时间的 unix 时间戳
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select unix_timestamp() as timestamp;
+------------+
| timestamp |
+------------+
| 1410502729 |
+------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2017-07-01') as timestamp;
+------------+
| timestamp |
+------------+
| 1498838400 |
+------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2017-07-01 13:00:06') as timestamp;
+------------+
| timestamp |
+------------+
| 1498885206 |
+------------+
1 row in set (0.00 sec)
FROM_UNIXTIME(timestamp)
将 unix 时间戳转换为日期时间
1
2
3
4
5
6
7
mysql> select from_unixtime(1498885206) as time;
+---------------------+
| time |
+---------------------+
| 2017-07-01 13:00:06 |
+---------------------+
1 row in set (0.00 sec)
DATE_ADD(start_date, INTERVAL expr unit)
日期增加
expr 是数值（若值为小数，会四舍五入到整数），unit 可为year 、month、day、 hour 、minute 、senond
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select date_add('2017-07-10 18:00:00',interval 1 day) as result;
+---------------------+
| result |
+---------------------+
| 2017-07-11 18:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_add('2017-07-10 18:00:00',interval 1 hour) as result;
+---------------------+
| result |
+---------------------+
| 2017-07-10 19:00:00 |
+---------------------+
1 row in set (0.00 sec)
DATE_SUB(start_date, INTERVAL expr unit)
日期减少
expr 是数值（若值为小数，会四舍五入到整数），unit 可为year 、month、day、 hour 、minute 、senond
1
2
3
4
5
6
7
mysql> select date_sub('2017-07-10 18:00:00',interval 1 hour) as result;
+---------------------+
| result |
+---------------------+
| 2017-07-10 17:00:00 |
+---------------------+
1 row in set (0.00 sec)
TIMEDIFF(time1,time2)
比较两个时间（格式需一致）的差值，即 time1-time2，值的范围为 -838:59:59 ~ 838:59:59
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select timediff('2017-07-10 15:30:10','2017-06-04 10:00:00') as diff;
+-----------+
| diff |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql> select timediff('2017-07-10 15:30:10','2017-07-04 10:00:00') as diff;
+-----------+
| diff |
+-----------+
| 149:30:10 |
+-----------+
1 row in set (0.00 sec)
流程控制相关
IF(condition,true_expr,false_expr)
如果 condition 的结果为真，则返回 true_expr 的值，否则返回 false_expr的值
1
2
3
4
5
6
7
mysql> select if(10=10,'equal','not equal') as result;
+--------+
| result |
+--------+
| equal |
+--------+
1 row in set (0.00 sec)
IFNULL(expr1,expr2)
如果表达式 expr1 不为 null，则返回 expr1 的值，否则返回 expr2 的值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select ifnull(null,1) as result;
+--------+
| result |
+--------+
| 1 |
+--------+
1 row in set (0.01 sec)
mysql> select ifnull(2,1) as result;
+--------+
| result |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
NULLIF(expr1,expr2)
如果表达式 expr1 等于 表达式 expr2，则返回 NULL，否则返回 expr1 的值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select nullif(1,1) as result;
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> select nullif(1+2,2) as result;
+--------+
| result |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
比较相关
ISNULL(expr)
如果 expr 为 NULL，则返回1，否则返回0
1
2
3
4
5
6
7
mysql> select isnull(1/0) as result;
+--------+
| result |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)
COALESCE(value1,value2,…)
系统相关
VERSION()
数据库版本
1
2
3
4
5
6
7
mysql> select version() as version;
+---------+
| version |
+---------+
| 5.7.16 |
+---------+
1 row in set (0.00 sec)
USER()
当前登录用户
1
2
3
4
5
6
7
mysql> select user() as user;
+----------------+
| user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
DATABASE()
当前数据库
1
2
3
4
5
6
7
mysql> select database() as db;
+--------+
| db |
+--------+
| testdb |
+--------+
1 row in set (0.00 sec)