mysql 常用函数

记录Mysql的学习笔记

常用函数

1.reverse

将字符串反转，即顺序取反

语法格式

reverse(s)

🌰

# 翻转字符串
set @mes = '0123456789';
SELECT @mes as '原字符', REVERSE(@mes) as '反转后的字符';#结果：9876543210

2.截取函数

2.1 left

语法格式

left(str,length)

• str：指定字符串
• length：截取的长度
• 总结：下标从1开始

🌰

# 从左边第一位向右截取5位长度字符串。
set @mes = '0123456789';
SELECT @mes as '原字符串', LEFT(@mes,5) as '截取后的字符串'; #结果：01234

2.2 right

语法格式

right(str, length)

• str：指定字符串
• length：截取的长度
• 总结：下标从1开始

🌰

# 从右边第一位向左截取5位长度字符串。
set @mes = '0123456789';
SELECT @mes as '原字符串', RIGHT(@mes,5) as '截取后的字符串';#结果：56789

2.3 substring

语法格式

substring(str, pos, length) 

• str：指定字符串 length为空截取后面所有内容
• pos：从哪个位置开始截取，正数从左往右，负数从右往左
• length：截取的长度

🌰

# 从指定位置向右截取5位长度字符串。
set @mes = '0123456789';
SELECT @mes as '原字符串', substring(@mes,5,5) as '截取后的字符串'; #结果：45678

2.4 substr、mid

语法格式

SUBSTR(str,pos,length)
MID(str,pos,length)

• str：指定字符串 length为空截取后面所有内容
• pos：从哪个位置开始截取，正数从左往右，负数从右往左
• length： 截取的长度

🌰

# 从指定位置向右截取5位长度字符串。
set @mes = '0123456789';
SELECT @mes as '原字符串', substr(@mes,5,5) as 'substr截取后的字符串', MID(@mes,5,5) as 'MID截取后的字符串';#结果：45678

2.5 substring_index

语法格式

substring_index(str, dim, count)

语法格式说明

• str：指定字符串
• dim：查询的字符串，字符未包含"dim"时返回整个字符
• count：第几个dim，正数代表从左往右查，负数从右往左查

🌰

# 从首位开始截取到第2个.点号前一位的字符串
SELECT substring_index('www.baidu.com.cn','.',2) as '截取后的字符';#结果：www.baidu

3.if

根据表达式的某个条件或值结果来执行一组 sql 语句

语法格式

IF(expr1,expr2,expr3)

语法格式说明

• expr：条件表达式
• v1：expr 为true时执行
• v2：expr 为false时执行

🌰

SELECT IF(1 = 1,'1','2') as '结果1' ,IF(1 = 2,'1','2') as '结果2' #结果：1 、2

4.curdate

以字符串或数字形式使用该函数， 以 ‘YYYY-MM-DD’ 或 YYYYMMDD 格式返回当前日期

特殊注意，以下几种写法的返回值是一样的

1. curdate()
2. current_date()
3. current_date

🌰

# 'YYYY-MM-DD'
SELECT CURRENT_DATE(); #结果：2023-02-02
#  YYYYMMDD
SELECT CURRENT_DATE() + 0; #结果：20230202

5.curtime

以字符串或数字形式使用该函数， 以 ‘hh:mm:ss’ 或 Yhhmmss格式返回当前时分秒

语法格式

curtime(fsp)

语法格式说明

1. fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒

特殊注意，以下几种写法的返回值是一样的

1. curtime()
2. current_time()
3. current_time

🌰

# 'hh:mm:ss'
SELECT CURTIME();#结果：11:01:01
# hhmmss
SELECT  CURTIME() + 0; #结果：110101

6.insert

语法格式

INSERT(str,pos,len,newstr)

语法格式说明

• str：指定字符串
• pos：开始被替换的位置
• len：被替换的字符串长度
• newstr：新的字符串
• 总结：替换掉 str 范围为 [ pos, pos+len ] 的字符串
• 注意：如果 pos > str 长度以内，则返回 str 不会被替换；如果 len > str 剩余字符串的长度，则将 str 剩下所有字符都替换成 newstr

🌰

SELECT INSERT('1237897',4,3,'456') #结果：1234567

7.replace

语法格式

REPLACE(str,from_str,to_str)

语法格式说明

• str：指定字符串
• from_str：需要替换掉的字符串
• to_str：新的字符串
• 注意：非字符串类型也是能替换的，譬如 int整数、datetime日期等

🌰

SELECT REPLACE('1237897','123','ABC') as '替换后的字符' #结果：ABC7897

8.strcmp

语法格式

STRCMP(expr1,expr2)

语法格式说明

• expr 就是两个需要比较的字符串
• 若expr1 小于 expr2 ，则返回 -1
• 若expr1 大于 expr2 ，则返回 1
• 其他情况则返回 0（如相等，不区分大小写

🌰

SELECT STRCMP('text', 'text2'); #结果：-1
SELECT STRCMP('text2', 'text'); #结果：1
SELECT STRCMP('text', 'Text');  #结果：0

9.locate

返回 substr 在 str 中第一次出现的位置，从 pos 位置开始搜索

语法格式

LOCATE(substr,str,pos)

• substr：查询的字符
• str：被查询字符串
• 从哪个位置开始，为空从1开始

🌰

SELECT LOCATE('333', '133355333', 5); #结果：7

10.ceiling 、floor、round

• ceiling：向上取值，ceil 函数一样效果哦
• floor：向下取整
• round：四舍五入

语法格式

CEILING(X)
CEIL(X)

FLOOR(X)

ROUND(X)

ROUND(X,D)

• X：取值的字符
• D：将 X 四舍五入到第D小数位，D可取最大值为 30

🌰

SELECT CEILING(1.23); #结果：2
SELECT CEIL(-1.23); #结果：-1
SELECT CEILING('1.23') #结果：2
SELECT CEIL('-1.23') #结果：-1

SELECT FLOOR(1.23); #结果：1
SELECT FLOOR(-1.23); #结果：-2

SELECT ROUND(-1.23); #结果：-1
SELECT ROUND(-1.58); #结果：-2
SELECT ROUND(1.58); #结果：2

11.datediff

返回两个指定的日期之间相差的天数，计算只管日期部分，不会管时分秒部分

语法格式

DATEDIFF(expr1,expr2)

语法格式说明

• 返回 expr1 - expr2 的相差天数
• expr 可以是具体的日期，也可以是日期表达式（即日期函数）
• 计算仅使用 expr 的日期部分，不会管时分秒部分

🌰

SELECT DATEDIFF('2023-02-02 23:59:59','2023-02-01 '); #结果：1

12.sysdate、now

获取当前时间

语法格式

SYSDATE(fsp)
NOW(fsp)

语法格式说明

• fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒

🌰

SELECT SYSDATE(); 		#结果：2023-02-02 14:29:38
SELECT SYSDATE(3);		#结果：2023-02-02 14:29:38.008
SELECT SYSDATE() + 0; 	#结果：20230202143021
SELECT NOW();			#结果：2023-02-02 14:29:38
SELECT NOW(3);			#结果：2023-02-02 14:29:38.008
SELECT NOW() + 0;		#结果：20230202143021

13.dayofyear、dayofweek、monthname、dayname

• dayofyear：返回日期在本年的第几天，范围为1到366。
• dayofweek：返回日期在本周的星期几索引，星期天 = 1，星期一 = 2，星期六 = 7
• monthname：回指定日期的月份的全名**（默认：英文）**
• 返回指定日期在本周周几的全名**（默认：英文）**

语法格式

DAYOFWEEK(date);
DAYOFYEAR(date);
MONTHNAME(date);
DAYNAME(date);

语法格式说明

• date：可以是指定的具体日期，也可以是日期表达式

14.time_to_sec 、sec_to_time

• time_to_sec：将指定时间转换为秒

• sec_to_time：将秒转换成时间（时、分、秒）

  语法格式

TIME_TO_SEC(time)
SEC_TO_TIME(seconds)

• time：传入时间，如果传入了日期部分，也不会管，只将时间部分转换成秒
• seconds：传入秒数

15.ifnull

可以判断某个字段的值是否为 null，为 null 则返回预先设定的值；有点像编程里面的三元表达式（只可意会不可言传，不懂三元表达式就忽略吧~

语法格式

IFNULL(expr1,expr2)

语法格式说明

• 如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
• v1、v2 均可以是值或表达式

🌰

SET @mes = NULL;
SELECT IFNULL(@mes,'1234') #结果：1234
SET @mes = '4567';
SELECT IFNULL(@mes,'1234') #结果：4567

16.case

• if 的高级版

类似java中的switch

 switch (var){
     case "":
         break;
     default:
         break;
 }

mybatis中的choose

<choose>
    <when test="">
    </when>
</choose>

• 通过条件表达式匹配 case 对应的值，然后执行对应的操作

语法格式

CASE <表达式>
	WHEN  <值1> THEN
		<操作2>
	WHEN  <值2> THEN
		<操作2>
	ELSE
		<操作3>
END <别名>;

语法格式说明

🌰

set @sexCode = 0;

SELECT 
CASE @sexCode
	WHEN 0 THEN
		'男'
	WHEN 1 THEN
		'女'
	ELSE
		'未知'
END sex; #结果：男

17.length

• 返回字符串的字节长度
• 注意：使用 uft8 编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节

语法格式

LENGTH(str)

🌰

SELECT LENGTH('闫妍') #结果：6
SELECT LENGTH(11) #结果：2

18.concat

• 连接多个字符串
• 若任何一个参数为NULL，则最终也返回NULL

语法格式

CONCAT(sl，s2，...) 

🌰

SELECT CONCAT('1','2','3') #结果：123;
SELECT CONCAT('1','2',NULL) #结果：NULL;

19.trim

删除字符串左右两侧的空格

语法格式

TRIM(str)

🌰

SELECT TRIM('    i love you ') #结果：i love you

高级语法格式

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

• {BOTH | LEADING | TRAILING}：指删除的范围， BOTH 是前后都删除， LEADING 是只删除前面， TRAILING 是只删除后面
• remstr：指定删除的符号，默认是空格，不指定就是空格
• str：原字符

🌰

SELECT TRIM(BOTH 'a' FROM 'abcadefga') #结果：bcadefg

小扩展

SELECT LTRIM('        i love u    ')# 删除左边的空格
SELECT RTRIM('        i love u    ')# 删除右边的空格

20.lower、upper

• upper：将所有字符串更改为大写，然后返回
• lower：将所有字符串更改为小写，然后返回

语法格式

LOWER(str)
UPPER(str)

🌰

SELECT LOWER('QUADRATICALLY'); #结果：quadratically

SELECT LOWER('ABc123asdBBB');  #结果：abc123asdbbb

21.instr

返回第一次出现子串的位置，和 locate 一样的作用，只是函数内部参数顺序调过来了而已

语法格式

INSTR(str,substr)

• 返回 substr 在 str 中第一次出现的位置

🌰

SELECT INSTR('123123','123') #结果：1

22.month 、year、date、time

• month：返回指定日期的月份，1月至12月的范围为1到12
• year：返回指定日期的年份，在1000到9999之间
• date：返回指定日期时间的日期部分
• time：返回指定日期时间的时间部分

23.group_concat

未分组的字段拼接成一个结果

语法格式

GROUP_CONCAT(distinct <字段> ORDER BY <排序> separator <分隔符>)

🌰

DROP TABLE IF EXISTS `testMes`;
# 创建虚拟表
CREATE TEMPORARY TABLE testMes ( id INT NOT NULL , `mes` VARCHAR ( 100 ) DEFAULT NULL);
# 插入数据
INSERT INTO `testMes` (`id`, `mes`) VALUES (1, '123');
INSERT INTO `testMes` (`id`, `mes`) VALUES (2, '123');
INSERT INTO `testMes` (`id`, `mes`) VALUES (3, '456');
INSERT INTO `testMes` (`id`, `mes`) VALUES (4, '789');

SELECT mes,GROUP_CONCAT(id separator '、') FROM test GROUP BY mes

结果

24.concat_ws

拼接字符

语法格式

CONCAT_WS(separator,str1,str2,...)

• separator：分隔符
• str：拼接的字符

🌰

SELECT CONCAT_WS('-','123','456') #结果：123-456

25.database

返回当前数据库名

26.date_format

格式化日期

语法格式

DATE_FORMAT(date,format)

• date：日期
• format：格式化类型

1. %Y：年，4 位
2. %y：年，2 位
3. %m：月，数值(01-12)
4. %d：月的天，数值(01-31)
5. %e：月的天，数值(0-31)
6. %H：小时 (00-23)
7. %h：小时 (01-12)
8. %i：分钟，数值(00-59)
9. %S：秒(00-59)

🌰

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'); #结果：2023-02-02 15:55:28

27.format

FORMAT(x,y)

• 把x格式化为以逗号隔开的数字序列，y是结果的小数位数

🌰

SELECT FORMAT('1234111',0); #结果：1,234,111