前言
- 在 MySQL 数据库中，函数可以用在 select 语句及其子句（例如 where、order by、having 等）中，也可以用在 update、delete 语句及其子句中。
1、字符串函数
-
字符串函数是最常用的一种函数，在 MySQL 中，字符串函数同样是最丰富的一类函数。
-
在程序上，字符串的编号从 0 开始，但在 MySQL 中，从 1 开始。
函数 功能 concat(s1, s2, ..., sn) 连接 s1, s2, ..., sn 为一个字符串 insert(str, x, y, instr) 将字符串 str 从 x 位置开始，y 个字符长的字串替换为字符串 instr lower(str) 将字符串 str 中所有字符变为小写 upper(str) 将字符串 str 中所有字符变为大写 lpad(str, n, pad) 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度 rpad(str, n, pad) 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度 repeat(str, x) 返回 str 重复 x 次的结果 replace(str, a, b) 用字符串 b 替换字符串 str 中所有出现的字符串 a strcmp(s1, s2) 比较字符串 s1 和 s2，s1 比 s2 小，返回 -1；s1 与 s2 相等，返回 0；s1 比 s2 大，返回 1 ltrim(str) 去掉字符串左侧的空格 rtrim(str) 去掉字符串右侧的空格 trim(str) 去掉字符串 str 行尾和行头的空格 left(str, x) 返回字符串 str 最左边的 x 个字符 right(str, x) 返回字符串 str 最右边的 x 个字符 substring(str, x, y) 返回从字符串 str x 位置起 y 个字符长度的子串 -
示例
> select concat('aaa', 'bbb', 'ccc'), concat('aaa', null); +-----------------------------+---------------------+ | concat('aaa', 'bbb', 'ccc') | concat('aaa', null) | +-----------------------------+---------------------+ | aaabbbccc | NULL | +-----------------------------+---------------------+ 1 row in set (0.00 sec) > select insert('beijing2008you', 12, 3, 'me'); +---------------------------------------+ | insert('beijing2008you', 12, 3, 'me') | +---------------------------------------+ | beijing2008me | +---------------------------------------+ 1 row in set (0.00 sec) > select lower('BEIJING2008'), upper('beijing2008'); +----------------------+----------------------+ | lower('BEIJING2008') | upper('beijing2008') | +----------------------+----------------------+ | beijing2008 | BEIJING2008 | +----------------------+----------------------+ 1 row in set (0.00 sec) > select lpad('2008', 20, 'beijing'), rpad('beijing', 20, '2008'); +-----------------------------+-----------------------------+ | lpad('2008', 20, 'beijing') | rpad('beijing', 20, '2008') | +-----------------------------+-----------------------------+ | beijingbeijingbe2008 | beijing2008200820082 | +-----------------------------+-----------------------------+ 1 row in set (0.00 sec) > select repeat('mysql ', 3); +---------------------+ | repeat('mysql ', 3) | +---------------------+ | mysql mysql mysql | +---------------------+ 1 row in set (0.00 sec) > select replace('beijing_2010', '_2010', '2008'); +------------------------------------------+ | replace('beijing_2010', '_2010', '2008') | +------------------------------------------+ | beijing2008 | +------------------------------------------+ 1 row in set (0.00 sec) > select strcmp('a', 'b'), strcmp('b', 'b'), strcmp('c', 'b'); +------------------+------------------+------------------+ | strcmp('a', 'b') | strcmp('b', 'b') | strcmp('c', 'b') | +------------------+------------------+------------------+ | -1 | 0 | 1 | +------------------+------------------+------------------+ 1 row in set (0.00 sec) > select ltrim(' |beijing'), rtrim('beijing| '); +--------------------+--------------------+ | ltrim(' |beijing') | rtrim('beijing| ') | +--------------------+--------------------+ | |beijing | beijing| | +--------------------+--------------------+ 1 row in set (0.00 sec) > select trim(' $ beijing2008 $ '); +---------------------------+ | trim(' $ beijing2008 $ ') | +---------------------------+ | $ beijing2008 $ | +---------------------------+ 1 row in set (0.00 sec) > select left('beijing2008', 7), left('beijing', null), right('beijing2008', 4); +------------------------+-----------------------+-------------------------+ | left('beijing2008', 7) | left('beijing', null) | right('beijing2008', 4) | +------------------------+-----------------------+-------------------------+ | beijing | NULL | 2008 | +------------------------+-----------------------+-------------------------+ 1 row in set (0.00 sec) > select substring('beijing2008', 8, 4), substring('beijing2008', 1, 7); +--------------------------------+--------------------------------+ | substring('beijing2008', 8, 4) | substring('beijing2008', 1, 7) | +--------------------------------+--------------------------------+ | 2008 | beijing | +--------------------------------+--------------------------------+ 1 row in set (0.00 sec)
2、数值函数
-
MySQL 中另外一类很重要的函数就是数值函数，这些函数能处理很多数值方面的运算。
函数 功能 abs(x) 返回 x 的绝对值 ceil(x) 返回大于 x 的最小整数值 floor(x) 返回小于 x 的最大整数值 mod(x, y) 返回 x/y 的模，模数和被模数任何一个为 NULL 结果都为 NULL rand() 返回 0～1 内的随机值 round(x, y) 返回参数 x 四舍五入的有 y 位小数的值，如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整 truncate(x, y) 返回数字 x 截断为 y 位小数的结果 -
示例
> select abs(-0.8), abs(0.8); +-----------+----------+ | abs(-0.8) | abs(0.8) | +-----------+----------+ | 0.8 | 0.8 | +-----------+----------+ 1 row in set (0.00 sec) > select ceil(-0.8), ceil(0.8); +------------+-----------+ | ceil(-0.8) | ceil(0.8) | +------------+-----------+ | 0 | 1 | +------------+-----------+ 1 row in set (0.01 sec) > select floor(-0.8), floor(0.8); +-------------+------------+ | floor(-0.8) | floor(0.8) | +-------------+------------+ | -1 | 0 | +-------------+------------+ 1 row in set (0.00 sec) > select mod(15, 10), mod(1, 11), mod(null, 10); +-------------+------------+---------------+ | mod(15, 10) | mod(1, 11) | mod(null, 10) | +-------------+------------+---------------+ | 5 | 1 | NULL | +-------------+------------+---------------+ 1 row in set (0.00 sec) > select rand(), rand() * 100, ceil(rand() * 100); +--------------------+--------------------+--------------------+ | rand() | rand() * 100 | ceil(rand() * 100) | +--------------------+--------------------+--------------------+ | 0.6922199192384444 | 37.314064183564916 | 79 | +--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) > select round(1.1), round(1.1, 2), round(1, 2); +------------+---------------+-------------+ | round(1.1) | round(1.1, 2) | round(1, 2) | +------------+---------------+-------------+ | 1 | 1.10 | 1 | +------------+---------------+-------------+ 1 row in set (0.00 sec) > select round(1.235, 2), truncate(1.235, 2); +-----------------+--------------------+ | round(1.235, 2) | truncate(1.235, 2) | +-----------------+--------------------+ | 1.24 | 1.23 | +-----------------+--------------------+ 1 row in set (0.00 sec)
3、日期和时间函数
-
常用日期和时间函数
函数 功能 curdate() 返回当前日期，只包含年月日 curtime() 返回当前时间，只包含时分秒 now() 返回当前日期和时间，年月日时分秒全都包含 unix_timestamp(date) 返回日期 date 的 unix 时间戳 from_unixtime(timestamp) 返回 unix 时间戳的日期和时间值，和 unix_timestamp(date) 互逆 week(date) 返回日期 date 为一年中的第几周 year(date) 返回日期 date 的年份 hour(time) 返回 time 的小时值 minute(time) 返回 time 的分钟值 monthname(date) 返回 date 的月份名 date_format(date, fmt) 返回按字符串 fmt 格式化日期 date 值 date_add(date, interval expr type) 返回与 date 相差 interval 时间段的日期，interval 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type 是日期间隔类型 datediff(expr, expr2) 返回起始时间 expr 和结束时间 expr2 之间的天数 -
日期和时间格式
格式符 格式说明 %S、%s 两位数字形式的秒（00, 01, ..., 59） %i 两位数字形式的分（00, 01, ..., 59） %H 两位数字形式的小时，24 小时（00, 01, ..., 23） %h、%I 两位数字形式的小时，12 小时（00, 01, ..., 12） %k 数字形式的小时，24 小时（0, 1, ..., 23） %l 数字形式的小时，12 小时（0, 1, ..., 12） %T 24 小时的时间形式（hh:mm:ss） %r 12 小时的时间形式（hh:mm:ss AM %p AM 或 PM %W 一周中每一天的名称（Sunday, Monday, ..., Saturday） %a 一周中每一天的名称缩写（Sum, Mon, ..., Sat） %d 两位数字表示月中的天数（00, 01, ..., 31） %e 数字形式表示月中的天数（1, 2, ..., 31） %D 英文后缀表示月中的天数（1st, 2nd, 3rd, ...） %w 以数字形式表示周中的天数（0 = Sunday, 1 = Monday, ..., 6 = Saturday） %j 以 3 位数字表示年中的天数（001, 002, ..., 366） %U 周（0, 1, 52），其中 Sunday 为周中的第一天 %u 周（0, 1, 52），其中 Monday 为周中的第一天 %M 月名（January, February, ..., December） %b 缩写的月名（January, February, ..., December） %m 两位数字表示的月份（01, 02, ..., 12） %c 数字表示的月份（1, 2, ..., 12） %Y 四为数字表示的年份 %y 两位数字表示的年份 %% 直接值 "%" -
日期间隔类型
表达式类型 描述 格式 HOUR 小时 hh MINUTE 分 mm SECOND 秒 ss YEAR 年 YY MONTH 月 MM DAY 日 DD YEAR_MONTH 年和月 YY-MM DAY_HOUR 日和小时 DD hh DAY_MINUTE 日和分钟 DD hh:mm DAY_SECOND 日和秒 DD hh:mm:ss HOUR_MINUTE 小时和分 hh:mm HOUR_SECOND 小时和秒 HH:ss MINUTE_SECOND 分和秒 mm:ss -
示例
> select curdate(); +------------+ | curdate() | +------------+ | 2018-06-20 | +------------+ 1 row in set (0.00 sec) > select curtime(); +-----------+ | curtime() | +-----------+ | 15:27:19 | +-----------+ 1 row in set (0.00 sec) > select now(); +---------------------+ | now() | +---------------------+ | 2018-06-20 15:28:12 | +---------------------+ 1 row in set (0.00 sec) > select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1529479736 | +-----------------------+ 1 row in set (0.00 sec) > select from_unixtime(1529479736); +---------------------------+ | from_unixtime(1529479736) | +---------------------------+ | 2018-06-20 15:28:56 | +---------------------------+ 1 row in set (0.00 sec) > select week(now()), year(now()); +-------------+-------------+ | week(now()) | year(now()) | +-------------+-------------+ | 24 | 2018 | +-------------+-------------+ 1 row in set (0.00 sec) > select hour(curtime()), minute(curtime()); +-----------------+-------------------+ | hour(curtime()) | minute(curtime()) | +-----------------+-------------------+ | 15 | 37 | +-----------------+-------------------+ 1 row in set (0.00 sec) > select monthname(now()); +------------------+ | monthname(now()) | +------------------+ | June | +------------------+ 1 row in set (0.00 sec) > select date_format(now(), '%M, %D, %Y'); +----------------------------------+ | date_format(now(), '%M, %D, %Y') | +----------------------------------+ | June, 20th, 2018 | +----------------------------------+ 1 row in set (0.00 sec) # 第 1 列返回了当前日期时间，第 2 列返回距离当前日期 31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间 > select now() current, date_add(now(), interval 31 day) after31days, date_add(now(), interval '1_2' year_month) after_oneyear_twomonth; +---------------------+---------------------+------------------------+ | current | after31days | after_oneyear_twomonth | +---------------------+---------------------+------------------------+ | 2018-06-20 15:44:16 | 2018-07-21 15:44:16 | 2019-08-20 15:44:16 | +---------------------+---------------------+------------------------+ 1 row in set (0.00 sec) # 第 1 列返回了当前日期时间，第 2 列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间 > select now() current, date_add(now(), interval -31 day) after31days, date_add(now(), interval '-1_-2' year_month) after_oneyear_twomonth; +---------------------+---------------------+------------------------+ | current | after31days | after_oneyear_twomonth | +---------------------+---------------------+------------------------+ | 2018-06-20 15:46:56 | 2018-05-20 15:46:56 | 2017-04-20 15:46:56 | +---------------------+---------------------+------------------------+ 1 row in set (0.00 sec) > select datediff(now(), '2008-08-08'); +-------------------------------+ | datediff(now(), '2008-08-08') | +-------------------------------+ | 3603 | +-------------------------------+ 1 row in set (0.00 sec)
4、流程函数
-
流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择，这样做能够提高语句的效率。
函数 功能 if(value, t f) 如果 value 是真，返回 t，否则返回 f ifnull(value1, value2) 如果 value1 不为空，返回 value1，否则返回 value2 case when [value] then [result] ... else [default] end 如果 value 是真，返回 result，否则返回 default case [expr] when [value] then [result] ... else [default] end 如果 expr 等于 value，返回 result，否则返回 default -
示例
> select salary, if(salary > 2000, 'high', 'low') from salary; +---------+----------------------------------+ | salary | if(salary > 2000, 'high', 'low') | +---------+----------------------------------+ | 1000.00 | low | | 2000.00 | low | | 3000.00 | high | | 4000.00 | high | | 5000.00 | high | | NULL | low | +---------+----------------------------------+ 6 rows in set (0.00 sec) > select salary, ifnull(salary, 0) from salary; +---------+-------------------+ | salary | ifnull(salary, 0) | +---------+-------------------+ | 1000.00 | 1000.00 | | 2000.00 | 2000.00 | | 3000.00 | 3000.00 | | 4000.00 | 4000.00 | | 5000.00 | 5000.00 | | NULL | 0.00 | +---------+-------------------+ 6 rows in set (0.00 sec) > select salary, case when salary <= 2000 then 'low' else 'high' end from salary; +---------+-----------------------------------------------------+ | salary | case when salary <= 2000 then 'low' else 'high' end | +---------+-----------------------------------------------------+ | 1000.00 | low | | 2000.00 | low | | 3000.00 | high | | 4000.00 | high | | 5000.00 | high | | NULL | high | +---------+-----------------------------------------------------+ 6 rows in set (0.00 sec) > select salary, case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary; +---------+-----------------------------------------------------------------------+ | salary | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end | +---------+-----------------------------------------------------------------------+ | 1000.00 | low | | 2000.00 | mid | | 3000.00 | high | | 4000.00 | high | | 5000.00 | high | | NULL | high | +---------+-----------------------------------------------------------------------+ 6 rows in set (0.00 sec)
5、其它常用函数
-
MySQL 提供的函数很丰富，除了前面介绍的字符串函数、数字函数、日期函数、流程函数以外还有很多其他函数。
函数 功能 database() 返回当前数据库名 version() 返回当前数据库版本 user() 返回当前登录用户名 inet_aton(ip) 返回 IP 地址的数字表示，将字符串的IP地址转换为数字表示的网络字节序，这样可以更方便地进行 IP 或者网段的比较 inet_ntoa(num) 返回数字代表的 IP 地址 password(str) 返回字符串 str 的加密版本，一个 41 位长的字符串，Removed in MySQL 8.0.11. md5() 返回字符串 str 的 MD5 值，常用来对应用中的数据进行加密 -
示例
> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) > select version(); +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.01 sec) > select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) > select inet_aton('192.16.0.100'); +---------------------------+ | inet_aton('192.16.0.100') | +---------------------------+ | 3222274148 | +---------------------------+ 1 row in set (0.00 sec) > select inet_ntoa(3222274148); +-----------------------+ | inet_ntoa(3222274148) | +-----------------------+ | 192.16.0.100 | +-----------------------+ 1 row in set (0.00 sec) > select PASSWORD('123456'); +-------------------------------------------+ | PASSWORD('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set (0.08 sec) > select md5('12345'); +----------------------------------+ | md5('12345') | +----------------------------------+ | 827ccb0eea8a706c4c34a16891f84e7b | +----------------------------------+ 1 row in set (0.00 sec)