常用函数

1. 概述

MySQL 中的函数类似于 Java 中的方法，优势是提高重用性和隐藏实现细节。调用格式为 select 函数名(参数列表)。

函数按用途可分为：

• 字符函数；
• 数学函数；
• 日期函数；
• 聚合函数（之前已经学习过）；
• 其他函数。

2. 字符函数

2.1 获取字符串所占字节数

语法：length('字符串')。

注

在 utf-8 编码下英文字符占一个字节，中文占三个字节。

# 获取“你好hello”字符串所占字节数
select length('你好hello');
# 查询员工姓名及姓名所占字节数
select ename, length(ename) from emp;

2.2 获取字符个数

语法：char_length('字符串')。

# 获取“你好hello”字符个数
select char_length('你好hello');
# 获取员工姓名及姓名所占字符个数
select ename, char_length(ename) from emp;

2.3 拼接字符串

语法：concat('字符串1','字符串2','字符串3',...)。

select concat('abc', '_', 'def');
# 查询员工及其上级的姓名，并使用"_"分隔
select concat(e.ename, '_', m.ename) from emp e, emp m where e.mgr=m.empno;

2.4 大小写转换

• 变大写：upper('字符串')；
• 变小写：lower('字符串')。

select upper('abc');
select lower('AB');
# 查询所有员工的姓名，将其姓名转换为大写
select upper(ename) from emp;
# 查询所有员工的姓名，将其姓名转换为小写
select lower(ename) from emp;

2.5 获取子串

语法：

• substr('字符串', 起始位置)：从起始位置开始到结尾；
• substr('字符串', 起始位置, 长度)：从起始位置开始，返回特定长度的字符串。

注

字符串的第一个字符的下标为 1。

# 返回“bcd”
select substr('abcd', 2);
# 返回“ello”
select substr('hello world', 2, 4);

2.6 获取子串第一次出现的索引

语法：instr('字符串','子字符串')。

# 返回7
select instr('hello world', 'world');

2.7 去除字符串前后子字符串

语法：trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)。

# 去除开头的'a'
select trim(leading 'a' from 'aaaaahelloaaa');
# 去除末尾的'a'
select trim(trailing 'a' from 'aaaaahelloaaa');
# 去掉前后的'a'
select trim(both 'a' from 'aaaaahelloaaa');
select trim('a' from 'aaaaahelloaaa');
# 去掉两侧空格
select trim('        mysql        ');

2.7.1 去掉左侧空格

语法：ltrim('字符串')。

select ltrim('   aaaa ');

2.7.2 去掉右侧空格

语法：rtrim('字符串')。

select rtrim('   aaaa   ');

2.8 左右填充

• lpad('字符串',填充后的总长度,'填充的字符串')：左填充；
• rpad('字符串',填充后的总长度,'填充的字符串')：右填充。

select lpad('aaa', 5, '-');
select rpad('aaa', 5, '-');

2.9 字符串替换

语法：replace('字符串', '被替换的字符串', '替换之后的字符串')。

select replace('hello java', 'java', 'mysql');

3. 数学函数

3.1 四舍五入

语法：round(数字, 小数位数)。

# 3
select round(3.145);
# 3.15
select round(3.145, 2);

3.2 向上取整

语法：ceil(数字)。

# 3
select ceil(2.02);
# -2
select ceil(-2.02);

3.3 向下取整

语法：floor(数字)。

# 5
select floor(5.01);
# 4
select floor(4.99);
# -6
select floor(-5.01);

3.4 取模

语法：mod(被模数, 模数)。

# 1
select mod(5, 2);

3.5 截断

语法：truncate(数字, 截取后小数的位数)。

# 3.14
select truncate(3.1456, 2);

3.6 随机数

语法：rand()，生成 0~1 之间的小数。

select rand();

4. 日期函数

4.1 当前日期和时间

• 获取当前日期和时间：now()；
• 获取当前日期：curdate()；
• 获取当前时间：curtime()。

select now();
select curdate();
select curtime();

4.2 获取年月日时分秒

• 年：year(日期)；
• 月：month(日期)；
• 日：day(日期)；
• 时：hour(日期)；
• 分：minute(日期)；
• 秒：second(日期)。

select year(now());
select year('2010/10/11 20:11:35');

select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
# 查询员工姓名和入职年份
select ename, year(hiredate) from emp;

4.3 日期转换

日期格式的字符串转换成日期：str_to_date('日期格式字符串','格式')。

# 2020-01-02
select str_to_date('1-2/2020', '%c-%d/%Y');

格式符	功能
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值 (00-31)
%e	月的天，数值 (0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值 (00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值 (00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒 (00-59)
%s	秒 (00-59)
%T	时间，24-小时 (hh:mm:ss)
%U	周 (00-53)，星期日是一周的第一天
%u	周 (00-53)，星期一是一周的第一天
%V	周 (01-53)，星期日是一周的第一天，与 %X 使用
%v	周 (01-53)，星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 (0=星期日, 6=星期六)
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

日期转换为字符：date_format(日期, '格式')。

select date_format(now(),'%Y年%m月%d日');
select date_format('1999-01-02','%Y年%c月%e日');
# 查询员工姓名和入职日期，以xxxx年xx月xx日显示
select ename, date_format(hiredate, '%Y年%m月%d日') from emp;

5. 其他函数

• 当前数据库版本：version()；
• 当前打开的数据库：database()；
• 当前用户：user()；
• 当前字符串 md5 加密形式：md5(字符)。