字符串查找
函数名 | 说明 | 参数值 |
LOCATE POSITION INSTR | 返回子字符串str1在字符串str中的开始位置 | search string, value search string IN value value, search string |
FIELD | 返回第一个与字符串s匹配的字符串的位置，返回搜索字符串位置，如search1就返回1，都没搜到的话返回0 | value,search1,search2.... |
LOCATE | 查找字符串出现位置，从1开始计数，未找到则返回0 | search string, value |
字符串函数
函数 | 说明 |
CONCAT(s1,s2，...) | 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。 |
CONCAT_WS(x,s1,s2,...) | 返回多个字符串拼接之后的字符串，每个字符串之间有一个x。 |
SUBSTRING(s,n,len)、MID(s,n,len) | 两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。 |
LEFT(s,n)、RIGHT(s,n) | 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符。 |
INSERT(s1,x,len,s2) | 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。 示例：替换手机号INSERT(a.phone,4,4,'****') |
REPLACE(s,s1,s2) | 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。 |
SUBSTRING_INDEX(VALUE,'.',-1) | 返回指定字符串以.分割的最后一个单词/后缀 |
LOWER | 大写转小写 |
UPPER | 小写转大写 |
LENGTH | 计算字符串长度 |
GROUP_CONCAT | 使用某字符串拼接这一分组的数据，如：GROUP_CONCAT(a.name SEPARATOR ',') |
字符串计算 / 填充操作
函数名 | 描述 | 参数值 |
LENGTH | 计算字符串长度 | value |
RPAD | 右侧填充字符 | value,length,pad char |
LPAD | 左侧填充字符 | value,length,pad char |
判断操作
函数名 | 描述 | 参数值 |
IF | 判断是否为true | where,true value,false value |
IFNULL | 判断是否为NULL | where, null value |
时间日期函数
日期函数中包含了MySQL中查询大部分日期、时间等的操作
函数名 | 函数描述 | 参数值 |
FROM_UNIXTIME | 时间戳转时间 详情参见格式详情 | value,format |
unix_timestamp | timestamp转unix时间戳 | value=NOW() |
date | 获取日期 yyyy-mm-dd 格式 | value |
time | 获取时间 hh:mm:ss 格式 | value |
year | 获取年 | value |
month | 获取月 | value |
day | 获取日 | value |
HOUR | 小时 0~23 | value |
MINUTE | 分钟 0~59 | value |
second | 秒 0-59 | value |
quarter | 季度 1-4 | value |
weekday | 周 0~6 0-周日......6-周六 | value |
dayofweek | 周的第几天 1~7 | value |
dayofmonth | 月的第几天 | value |
dayofyear | 年的第几天 | value |
LAST_DAY | 获取指定月的最后一天，经常配合day使用 | value |
NOW | 获取当前时间，同等函数还有localtime、current_timestamp、localtimestamp | |
CURDATE | 获取当前日期，yyyy-mm-dd格式，等同于current_date | |
CURTIME | 获取当前时间，hh:mm:ss格式，等同于current_time |
日期计算
函数名 | 作用 | 示例值 |
date_add | 日期添加 | value1, interval value2 day value1 为要操作的时间 interval 固定词 value2 计算量级 day 计算单位，支持 year、month、day、hour、minute、second、week、quarter 此函数还支持时间段形式的添加 如：value2为时间段，计量单位为DAY_MINUTE之类的时间段 |
date_sub | 日期扣减 | 和date_add相同 |
datediff | 时间间隔天数 | str1,str2 正数表示第一个时间比第二个时间大，负数表示相反 |
timediff | 时间间隔 | str1,str2 正数表示第一个时间比第二个时间大，负数表示相反 时间格式为hh:mm:ss格式，可以使用hour、minute、second进行提取 PS: 提取后正负号会丢失，建议按以下方式提取 LEFT(TIMEDIFF(NOW(),"2022-06-30 09:40:32"),1) - 提取符号 HOUR(TIMEDIFF(NOW(),"2022-06-30 09:40:32")) - 提取小时...... |
格式详情信息
格式化字符串 | 备注 | 示例值 |
%M | 月名字 | (January……December) |
%W | 星期名字 | (Sunday……Saturday) |
%D | 有英语前缀的月份的日期 | (1st, 2nd, 3rd, 等等。） |
%Y | 年, 数字 | , 4 位 |
%y | 年, 数字 | , 2 位 |
%a | 缩写的星期名字 | (Sun……Sat) |
%d | 月份中的天数, 数字 | (00……31) |
%e | 月份中的天数, 数字 | (0……31) |
%m | 月, 数字 | (01……12) |
%c | 月, 数字 | (1……12) |
%b | 缩写的月份名字 | (Jan……Dec) |
%j | 一年中的天数 | (001……366) |
%H | 小时 | (00……23) |
%k | 小时 | (0……23) |
%h | 小时 | (01……12) |
%I | 小时 | (01……12) |
%l | 小时 | (1……12) |
%i | 分钟, 数字 | (00……59) |
%r | 时间 | ,12 小时(hh:mm:ss [AP]M) |
%T | 时间 | ,24 小时(hh:mm:ss) |
%S | 秒 | (00……59) |
%s | 秒 | (00……59) |
%p AM | 或 | PM |
%w | 一个星期中的天数 | (0=周日 ……6=周六 ） |
%U | 星期 | (0……52), 这里星期天是星期的第一天 |
%u | 星期 | (0……52), 这里星期一是星期的第一天 |
%% | 一个文字“%”。 |
进制转换函数
在一些订票、日历等软件中，很多数值是使用二进制位来标示开关的，即一个字段中存储了很多的座次信息，此处我们可以直接使用MySQL支持的函数进行进制转换，用以进行快捷查询操作
函数名 | 作用 | 参数值 |
bin | 十进制转二进制 | value |
hex | 十进制转十六进制 | value |
oct | 十进制转八进制 | value |
conv | N进制转十进制 | value,from_base,to_base |
JSON函数
MySQL 5.7以上支持一些简单的JSON操作，以便我们更方便的进行查询
函数名 | 作用 | 示例 |
json_keys | 获取json中的下标列表 | json_keys(<json>) |
json_extract | 提取JSON中的值 | json_extract(<json>,<提取规则>) |
json_search | 从json字符串中搜索value值【返回匹配规则】 | JSON_SEARCH(<json>,<one 或者 all>,<搜索的值>) |
JSON_DEPTH | 返回JSON字符串的最大深度 | JSON_DEPTH(<json>) |
JSON_LENGTH | 返回JSON长度【json内的属性数量】 | JSON_LENGTH(<json>) |
JSON_REMOVE | 删除json中的对象 | JSON_REMOVE(<json>,<提取规则>) |
JSON_MERGE | json数组合并 | JSON_MERGE(<json>,<json>) |
常用嵌套函数
一些常用操作中，有些是可以直接嵌套来实现相应功能的，如：
嵌套函数 | 作用 |
DAYOFWEEK(NOW())-1 | 获取当前周几，0-日 1-一...6-六 |
LPAD(BIN(value),7,0) | 将value转化为2进制并补足7位，用于周开关控制 |
SUBSTRING(LPAD(BIN(value),7,0),DAYOFWEEK(NOW())-1,1) | 根据二进制的开关值判断今天是否允许操作 |