MySQL中提供了丰富的函数支持，包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等，通过这些函数，一方面可以简化业务的代码量，另一方面还能更好的实现各类特殊业务需求，下来一起来聊聊MySQL支持的函数。
1.1、数学函数
数学函数是MySQL中最常用的一类函数，主要用来处理所有数值类型的字段值，一起来看看。
• abs(X)：返回X的绝对值，如传进-1，则返回1。
• ln(X)：返回X的自然相对数。
• log(X,Y)：返回以X的以Y为底的对数。
• log10(X):返回以X基数为10的对数。
• bin(X)：返回X的二进制值。
• oct(X)：返回X的八进制值。
• hex(X)：返回X的十六进制值。
• mod(X,Y)：返回X除以Y的余数。
• ceil(X) | ceiling(X)：返回不小于X的最小整数，如传入1.23，则返回2。
• round(X)：返回X四舍五入的整数。
• floor(X)：返回X向下取整后的值，如传入2.34，会返回2。
• greatest(X1,X2....,Xn)：返回集合中的最大整数值。
• least(X1,X2....,Xn)：返回集合中的最小整数值。
• rand(N)：返回一个0~N``0~1之间的随机小数（不传参默认返回0~1之间的随机小数）。
• sign(X)：传入正数，返回1；传入负数，返回-1；传入0，返回0。
• pow(X,Y) | power(X,Y)：返回X的Y次方值。
• pi()：返回四舍五入后的圆周率，3.141593。
• sin(X)：返回X的正弦值。
• asin(X)：返回X的反正弦值。
• cos(X)：返回X的余弦值。
• acos(X)：返回X的反余弦值。
• tan(X)：返回X的正切值。
• atan(X)：返回X的反正切值。
• cot(X)：返回X的余切值。
• radians(x)：返回x由角度转化为弧度的值。
• degrees(x)：返回x由弧度转化为角度的值。
• sqrt(X)：返回X的平方根。
• exp(e,X)：返回e的x乘方的值。
• truncate(X,N)：返回小数X保留N位精准度的小数。
• format(x,y)：将x格式化位以逗号隔开的数字列表，y是结果的小数位数。
• inet_aton(ip)：将IP地址以数字的形式展现。
• inet_ntoa(number)：显示数字代表的IP地址。
1.2、字符串函数
• ascii(C)：返回字符C的ASCII码。
• length(S)：返回字符串的占位空间，传入“竹子爱熊猫”，返回15，一个汉字占位3字节。
• bit_length(S)：返回字符串的比特长度。
• concat(S1,S2,...)：合并传入的多个字符串。
• concat_wa(sep,S1,S2...)：合并传入的多个字符串，每个字符串之间用sep间隔。
• position(str,s) | locate(str,s)：返回s在str中第一次出现的位置，没有则返回0。
• find_in_set(S,list)：返回字符串S在list列表中的位置。
• insert(S1,start,end,S2)：使用S2字符串替换掉S1字符串中start~end的内容。
• lcase(S) | lower(S)：将传入的字符串中所有大写字母转换为小写。
• ucase(S) | upper(S)：将传入的字符串中所有小写字母转换为大写。
• left(S,index)：从左侧开始截取字符串S的index个字符。
• right(S,index)：从右侧开始截取字符串S的index个字符。
• trim(S)：删除字符S左右两侧的空格。
• rtrim(S)：删除字符S右侧的空格。
• replace(S,old,new)：使用new新字符替换掉S字符串中的old字符。
• repeat(str,count)：将str字符串重复count次后返回。
• substring(S,index,N)：截取S字符串，从index位置开始，返回长度为N的字符串。
• reverse(S)：将传入的字符串反转，即传入Java，返回avaJ。
• quote(str)：用反斜杠转移str中的英文单引号。
• strcmp(S1,S2)：比较两个字符是否相同。
• lpad(str,len,s)：对str字符串左边填充len个s字符。
• rpad(str,len,s)：对str字符串右边填充len个s字符。
1.3、日期和时间函数
• curdate() | current_date()：返回当前系统的日期，如2022-10-21。
• curtime() | current_time()：返回当前系统的时间，如17:30:52。
• now() | sysdate()：返回当前系统的日期时间，如2022-10-21 17:30:59。
• unix_timestamp()：获取一个数值类型的unix时间戳，如1666348711。
• from_unixtime()：将unix_timestamp()获取的数值时间戳，格式化成日期格式。
• month(date)：获取date中的月份。
• year(date)：获取date中的年份。
• hour(date)：获取date中的小时。
• minute(date)：获取date中的分钟。
• second(date)：获取date中的秒数。
• monthname(date)：返回date中月份的英文名称。
• dayname(date)：获取日期date是星期几，如Friday。
• dayofweek(date)：获取date位于一周的索引位置，周日是1、周一是2...周六是7。
• week(date)：获取date是本年的第多少周。
• quarter(date)：获取date位于一年中的哪个季度（1~4）。
• dayofyear(date)：获取date是本年的第多少天。
• dayofmonth(date)：获取date是本月的第多少天。
• time_to_sec(time)：将传入的时间time转换为秒数，比如"01:00:00" = 3600s。
• date_add(date,interval 时间 单位) | adddate(...)：将date与给定的时间按单位相加。
• date_sub(date,interval 时间 单位) | subdate(...)：将date与给定的时间按单位相减。
• addtime(date,time)：将date加上指定的时间，如addtime(now(),"01:01:01")。
• subtime(date,time)：将date减去指定的时间。
• datediff(date1,date2)：计算两个日期之间的间隔天数。
• last_day(date)：获取date日期这个月的最后一天。
• date_format(date,format)：将一个日期格式化成指定格式，format可选项如下：
• %a：工作日的英文缩写（Sun~Sat）。
• %b：月份的英文缩写（Jan~Dec）。
• %c：月份的数字格式（1~12）。
• %M：月份的英文全称（January~December）。
• %D：带有英文后缀的数字月份（1th、2st、3nd....）。
• %d：一个月内的天数，双数形式（01、02、03....31）。
• %e：一个月内的天数，单数形式（1、2、3、4....31）。
• %f：微妙（000000~999999）。
• %H：一天内的小时，24小时的周期（00、01、02...23）。
• %h | %I：一天内的小时，12小时的周期（01、02、03...12）。
• %i：一小时内的分钟（00~59）。
• %j：一年中的天数（001~366）。
• %k：以24小时制显示时间（00~23）。
• %l：以12小时制显示时间（01~12）。
• %m：月份的数字形式，双数形式（01~12）。
• %p：一天内的时间段（上午AM、下午PM）。
• %r：12小时制的时间（12:01:09 AM）。
• %S | %s：秒数，双数形式（00~59）。
• %T：24小时制的时间（23:18:22）。
• %U：一年内的周（00~53）。
• time_format(time,format)：将一个时间格式化成指定格式。
• str_to_date(str,format)：将日期字符串，格式化成指定格式。
• timestampdiff(unit,start,end)：计算两个日期之间间隔的具体时间，unit是单位：
• year：年。
• quarter：季度。
• month：月。
• week：周。
• day：天。
• hour：小时。
• minute：分钟。
• second：秒数。
• microsecond：微妙。
• weekday(date)：返回date位于一周内的索引（0是周一...6是周日）。
1.4、聚合函数
聚合函数一般是会结合select、group by having筛选数据使用。
• max(字段名)：查询指定字段值中的最大值。
• min(字段名)：查询指定字段值中的最小值。
• count(字段名)：统计查询结果中的行数。
• sum(字段名)：求和指定字段的所有值。
• avg(字段名)：对指定字段的所有值，求出平均值。
• group_concat(字段名)：返回指定字段所有值组合成的结果，如下：
• distinct(字段名)：对于查询结果中的指定的字段去重。
这里稍微介绍一个日常业务中碰到次数较多的需求：
select *from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time |
+---------+-----------+----------+----------+---------------------+
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
|8|猫熊|女|8888|2022-09-2717:22:29|
|9|棕熊|男|0369|2022-10-1723:48:29|
+---------+-----------+----------+----------+---------------------+
-- 基于性别字段分组，然后显示各组中的所有ID
select
convert(
group_concat(user_id orderby user_id asc separator ",")
using utf8)as"分组统计"
from`zz_users`groupby user_sex;
+-------------+
|分组统计|
+-------------+
|1,8|
|2,3,4,9|
+-------------+
上述利用了group_concat()、group by实现了按照一个字段分组后，显示对应分组的所有ID。
1.5、控制流程函数
• if(expr,r1,r2)：expr是表达式，如果成立返回r1，否则返回r2。
• ifnull(v,r)：如果v不为null则返回v，否则返回r。
• nullif(v1,v2)：如果v1 == v2，则返回null，如果不相等则返回V1。
-- if的用例
selectif(user_id >3,"√","×")from zz_users;
-- ifnull的用例
select ifnull(user_id,"×")from zz_users;
-- case语法1：
case<表达式>
when<值1>then<操作>
when<值2>then<操作>
...
else<操作>
end;
-- 用例：判断当前时间是星期几
selectcase weekday(now())
when0then'星期一'
when1then'星期二'
when2then'星期三'
when3then'星期四'
when4then'星期五'
when5then'星期六'
else'星期天'
endas"今天是星期几？";
-- case语法2：
case
when<条件1>then<命令>
when<条件2>then<命令>
...
else commands
end;
-- 用例：判断今天是星期几
selectcase
when weekday(now())=0then'星期一'
when weekday(now())=1then'星期二'
when weekday(now())=2then'星期三'
when weekday(now())=3then'星期四'
when weekday(now())=4then'星期五'
when weekday(now())=5then'星期六'
else'星期天'
endas"今天是星期几？";
简单聊一下CASE语法，第一种语法就类似于Java中的switch，而第二种语法就类似于多重if，通过CASE语法能够让SQL更加灵活，完成类似于存储过程的工作。
1.6、加密函数
• password(str)：将str字符串以数据库密码的形式加密，一般用在设置DB用户密码上。
• md5(str)：对str字符串以MD5不可逆算法模式加密。
• encode(str,key)：通过key密钥对str字符串进行加密（对称加密算法）。
• decode(str,key)：通过key密钥对str字符串进行解密。
• aes_encrypt(str,key)：通过key密钥对str字符串，以AES算法进行加密。
• aes_decrypt(str,key)：通过key密钥对str字符串，以AES算法进行解密。
• sha(str)：计算str字符串的散列算法校验值。
• encrypt(str,salt)：使用salt盐值对str字符串进行加密。
• decrypt(str,salt)：使用salt盐值对str字符串进行解密。
1.7、系统函数
• version()：查询当前数据库的版本。
• connection_id()：返回当前数据库连接的ID。
• database() | schema()：返回当前连接位于哪个数据库，即use进入的库。
• user()：查询当前的登录的所有用户信息。
• system_user()：返回当前登录的所有系统用户信息。
• session_user()：查询所有连接的用户信息。
• current_user()：查询当前连接的用户信息。
• charset(str)：返回当前数据库的编码格式。
• collation(str)：返回当前数据库的字符排序规则。
• benchmark(count,expr)：将expr表达式重复运行count次。
• found_rows()：返回最后一个select查询语句检索的数据总行数。
• cast(v as 类型)：将v转换为指定的数据类型。
4764
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
