一、截取字符串函数
mysql索引从1开始
1、left(str,length) 从左边截取length
select left('abcd4528',3);
结果为：abc
2、right(str,length)从右边截取length
select right('abcd4528',3);
结果为：528
3、substring(str,index)当index>0从左边开始截取直到结束 当index<0从右边开始截取直到结束 当index=0返回空
select substring('abcd4528',3);
结果为：cd4528
4、substring(str,index,len) 截取str,从index开始，截取len长度
select substring('abcd4528',3,4);
结果为：cd45
5、substring_index(str,delim,count)，str是要截取的字符串，delim是截取的字段，count是从哪里开始截取(为0则是左边第0个开始，1为左边开始第一个选取左边的，-1从右边第一个开始选取右边的。找不到返回整个字符串。
例：截取第二个 '.' 之前的所有字符
select substring_index('www.sqlstudy.com.cn', '.', 2);
结果：www.sqlstudy
例：截取第二个 '.' （倒数）之后的所有字符
select substring_index('www.sqlstudy.com.cn', '.', -2);
结果：com.cn
6、subdate(date,day)截取时间，时间减去后面的day
7、subtime(expr1,expr2) 时分秒expr1-expr2
二、find_in_set()函数
语法：
FIND_IN_SET(str,strlist)
str 要查询的字符串，strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果，返回结果为null或记录。
注意：上面的sql语句执行的结果和下面的相同
SELECT * FROM `t_area_code` where find_in_set(id,'110000,110100,110101')>0
例子
SELECT FIND_IN_SET('b', 'a,b,c,d');
结果为2，因为b 在strlist集合中放在2的位置，从1开始。
select FIND_IN_SET('6', '1');
返回0，strlist中不存在str，所以返回0。
find_in_set()和in的区别：
我们来创建表来说明两者的区别
CREATE TABLE `tb_test` ( `id` int NOT NULL auto_increment, `name` varchar(255) NOT NULL, `list` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
插入数据
INSERT INTO `tb_test` VALUES (1, 'name', 'daodao,xiaohu,xiaoqin'); INSERT INTO `tb_test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin'); INSERT INTO `tb_test` VALUES (3, 'name3', 'xiaoqin,daodao,xiaohu');
数据如下：
使用in查询list中的部分数据
SELECT id,name,list from tb_test WHERE 'daodao' IN(list);
结果: 发现查不到数据，因为使用in的话必须是完全匹配，否则查不到。
我们使用find_in_set()
SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list);
三、ISNULL(exp) 函数
用isnull判断是否为空：只有name 为null 的时候 ISNULL(exp) 函数的返回值为1 ，空字符串和有数据都为0；
过滤掉null的sql 语句：
select * from user where name is not null;
或者
select * from user where ISNULL(name)=0;
ISNULL为0表示非NULL，即有数据和空字符串。结果：
同时剔除null 和 空字符串
select * from user where ISNULL(name)=0 and LENGTH(trim(name))>0;
ISNULL(name)=0表示非NULL，即有数据和空字符串，LENGTH(trim(name))>0表示
结果：
在函数或者存储过程中判断是否为null 或者 空字符串
ISNULL(NAME)=1) || (LENGTH(trim(NAME))=0
四、IFNULL函数
IFNULL(expr1,expr2):如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。
select IFNULL(1,0); // 1 select IFNULL(0,10); // 0 select IFNULL(null,10); // 10 select IFNULL(null,'yes'); // yes
五、IF函数
IF(expr1,expr2,expr3) ：如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。
select IF(1>2,2,3); -- 3 select IF(1<2,'yes','no'); -- yes select IF(strcmp('test','test1'),'yes','no'); -- yes
六、strcmp函数
此函数比较两个给定的字符串，如果两个字符串相同，则此函数返回0，如果第一个字符串小于第二个字符串，则此函数返回-1，否则返回1
select strcmp('test','test1') -- -1
七、group_concat函数
语法：
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
group_concat函数常用于select 语句中，下面我们通过一张表来讲解group_concat函数的用法。
首先来看下初始的select函数：
select * from exam;
上述sql执行结果为：
|id |subject |student|teacher|score| --------------------------------------- |1 |数学 |小红 |王老师 |80 | |2 |数学 |小李 |王老师 |80 | |3 |数学 |小王 |王老师 |70 | |4 |数学 |小张 |王老师 |90 | |5 |数学 |小赵 |王老师 |70 | |6 |数学 |小孙 |王老师 |80 | |7 |数学 |小钱 |王老师 |90 | |8 |数学 |小高 |王老师 |70 | |9 |数学 |小秦 |王老师 |80 | |10 |数学 |小马 |王老师 |90 | |11 |数学 |小朱 |王老师 |90 | |12 |语文 |小高 |李老师 |70 | |15 |语文 |小秦 |李老师 |70 | |18 |语文 |小马 |李老师 |80 | |21 |语文 |小朱 |李老师 |90 | |24 |语文 |小钱 |李老师 |90 |
如果我们希望按分数score进行分组，并将分组后的学生姓名打印下来，就可以用group_concat实现。执行sql：
select score,group_concat(student) from exam group by score;
执行结果为：
|score |group_concat(student) | ------------------------------------- |70 |小王,小赵,小高,小高,小秦 | |80 |小红,小李,小孙,小秦,小马 | |90 |小张,小钱,小马,小朱,小朱,小钱 |
不难看出，在70分这一行有两条小高的记录，90分这一行有两条小钱和小朱的记录，如果我们需要去重，则需要给函数中加一个distinct参数：
select score,group_concat(distinct student) from exam group by score;
执行结果为：
|score |group_concat(student) | --------------------------------- |70 |小王,小赵,小高,小秦 | |80 |小红,小李,小孙,小秦,小马 | |90 |小张,小钱,小马,小朱 |
这样group_concat每行数据的结果中就没有了重复值，但是在数据中的分隔符为默认的逗号','，如果想修改默认的分隔符，只需要在上述指令中稍作修改：
select score,group_concat(distinct student separator '%') from exam group by score;
执行结果：
|score |group_concat(student) | --------------------------------- |70 |小王%小秦%小赵%小高 | |80 |小孙%小李%小秦%小红%小马 | |90 |小张%小朱%小钱%小马 |
下面举例说明：
select id,price from goods;
结果如下：
以id分组，把price字段的值在同一行打印出来，逗号分隔(默认)
select id, group_concat(price) from goods group by id;
结果如下：
以id分组，把去除重复冗余的price字段的值打印在一行，逗号分隔
select id,group_concat(distinct price) from goods group by id;
结果：
以id分组，把price字段的值去重打印在一行，逗号分隔，按照price倒序排列
select id,group_concat(DISTINCT price order by price desc) from goods group by id;
结果:
示例2：
SELECT grade,hisal FROM `salgrade`
结果：
SELECT grade,group_concat(hisal) FROM `salgrade`
结果：
当使用|进行分割时，
SELECT grade,group_concat(hisal SEPARATOR '|') FROM `salgrade`
结果：
SELECT grade,group_concat(concat(grade,'.',hisal) SEPARATOR '|') FROM `salgrade`
结果：
八、substring_index()函数
按分隔符截取字符串
语法：
SUBSTRING_INDEX(str, delimiter, count)
返回一个 str 的子字符串，在 delimiter 出现 count 次的位置截取。如果 count > 0，从则左边数起，且返回位置前的子串；如果 count < 0，从则右边数起，且返回位置后的子串。
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
例子：
SELECT substring_index(substring_index('海湾煤矿三号井-汽运-中鸡','-',2),'-',-1); // 汽运 SELECT substring_index('海湾煤矿三号井-汽运-中鸡','-',2); // 海湾煤矿三号井-汽运
九、replace函数
语法：
REPLACE ( string_expression , string_pattern , string_replacement )
参数
string_expression 要搜索的字符串表达式。string_expression 可以是字符或二进制数据类型。
string_pattern 是要查找的子字符串。string_pattern 可以是字符或二进制数据类型。string_pattern 不能是空字符串 ('')。
string_replacement 替换字符串。string_replacement 可以是字符或二进制数据类型。
翻成白话：REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。
十、date_format函数
mysql中没有to_char函数，应该使用date_format，日期转为字符串使用 date_format(create_time,’%Y-%m-%d %H:%i:%s’)
<if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''"> AND DATE_FORMAT(t1.create_time,'%Y-%m-%d') BETWEEN #{startTime} and #{endTime}; </if>
date_format与str_to_date:
SELECT * FROM `t_approve_record` where update_time BETWEEN STR_TO_DATE('2021-06-16 00:00:00','%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-06-16 23:59:59','%Y-%m-%d %H:%i:%s'); SELECT * FROM `t_approve_record` where DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s') BETWEEN '2021-06-16 00:00:00' AND '2021-06-16 23:59:59';
查询某一天的数据，可以是将yyyy-MM-dd拼接上00:00:00和23:59:59再来进行比较：
SELECT * FROM `t_approve_record` where DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s') BETWEEN '2021-06-16 00:00:00' AND '2021-06-16 23:59:59';
也可以将表字段的datetime类型转成%Y-%m-%d字符串，再比较，如下：
SELECT * FROM `t_approve_record` where DATE_FORMAT(update_time,'%Y-%m-%d') BETWEEN '2021-06-16' AND '2021-06-16';
date_format可以直接将20240113063000这个格式的时间转为正常日期时间格式
select DATE_FORMAT(20240113063000,'%Y-%m-%d %H:%i:%s') as load_time
结果：2024-01-13 06:30:00
十一、date_add函数
DATE_ADD() 函数向日期添加指定的时间间隔。
语法
DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。type 参数可以是下列值：
| Type 值 |
|---|
| MICROSECOND |
| SECOND |
| MINUTE |
| HOUR |
| DAY |
| WEEK |
| MONTH |
| QUARTER |
| YEAR |
| SECOND_MICROSECOND |
| MINUTE_MICROSECOND |
| MINUTE_SECOND |
| HOUR_MICROSECOND |
| HOUR_SECOND |
| HOUR_MINUTE |
| DAY_MICROSECOND |
| DAY_SECOND |
| DAY_MINUTE |
| DAY_HOUR |
| YEAR_MONTH |
使用
<if test="startTime !=null and startTime!='' and endTime!=null and endTime!=''"> AND we.verify_time BETWEEN #{startTime} AND DATE_ADD(#{endTime},INTERVAL 1 DAY) </if>
这里有个特殊情况，就是如果我们数据库字段类型为datetime，但前端传递过来的是日期，这样实际数据库中的数据后面是带有00:00:00的，这导致我们在查询该字段时可能会出现问题。
比如你查询的是2023-02-17到2023-02-18，传递到后台是查的2023-02-17 00:00:00到2023-02-18 00:00:00，也就是只能查到17号的数据，为了能查到18号的数据所以加了一天，即变成2023-02-17 00:00:00到2023-02-19 00:00:00。但是如果你录入的时间是2023-02-19，则数据库存的是2023-02-19 00:00:00，正好也能被查到，就多了1毫秒，导致你查询的是17号和18号，但是19号零点零分零秒的数据也能被查到。
错误代码：
修改如下：
<if test="startTime !=null and startTime!='' and endTime!=null and endTime!=''"> AND we.verify_time BETWEEN #{startTime} AND DATE_ADD(#{endTime},INTERVAL 86399 SECOND) </if>
即不加一天，而是加23:59:59秒，即86399秒。
十二、cast函数
用于将值从一种数据类型转换为表达式中指定的另一种数据类型
CAST(value AS datatype)
在实际开发中，当我们的主键为雪花算法生成的ID时，此时应该将Long类型转为字符串类型，否则精度会丢失
select cast(a.post_user_id as char) as post_user_id,...
AS关键字用于分隔两个参数，在AS之前的是要处理的数据，在AS之后的是要转换的数据类型
1.将值转换为DATE数据类型
-- 2017-08-29 SELECT CAST('2017-08-29' AS DATE); -- 2022-04-27 15:42:19 SELECT NOW(); -- 2022-04-27 SELECT CAST(NOW() AS DATE);
2、将值转换为DATETIME数据类型
-- 2022-04-27 00:00:00 SELECT CAST('2022-04-27' AS DATETIME);
3、将值转换为CHAR数据类型
-- '150' SELECT CAST(150 AS CHAR); -- 出错Error SELECT CONCAT('Hello World',437)); -- 'Hello World437' SELECT CONCAT('Hello World',CAST(437 AS CHAR));
4、将值转换为INT（SIGNED）数据类型
-- 5 SELECT CAST('5.0' AS SIGNED); -- 2 SELECT (1 + CAST('3' AS SIGNED))/2; -- -5 SELECT CAST(5-10 AS SIGNED); -- 6 SELECT CAST(6.4 AS SIGNED); -- -6 SELECT CAST(-6.4 AS SIGNED); -- 7 SELECT CAST(6.5 AS SIGNED); -- -7 SELECT CAST(-6.5 AS SIGNED);
如果字段是字符串类型，没有通过cast转换的话，就无法通过大于号或小于号比较大小。
另外，根据字符串类型的字段进行升序排序的时候，也需要通过cast转换成INT类型，否在无法实现排序。
order by CAST(s.ATTRIB_ONE AS SIGNED) asc
5、将值转换为UNSIGNED数据类型
-- 5 SELECT CAST('5.0' AS UNSIGNED); -- 6 SELECT CAST(6.4 AS UNSIGNED); -- 0 SELECT CAST(-6.4 AS UNSIGNED); -- 7 SELECT CAST(6.5 AS UNSIGNED); -- 0 SELECT CAST(-6.5 AS UNSIGNED);
6、将值转换为DECIMAL数据类型
-- 9 SELECT CAST('9.0' AS DECIMAL); -- DECIMAL(数值精度，小数点保留长度) -- DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字 -- 精度与小数位数分别为10与2 -- 精度是总的数字位数，包括小数点左边和右边位数的总和 -- 小数位数是小数点右边的位数 -- 9.50 SELECT CAST('9.5' AS DECIMAL(10,2)); -- 99999999.99 SELECT CAST('1234567890.123' AS DECIMAL(10,2)); -- 220.232 SELECT CAST('220.23211231' AS DECIMAL(10, 3)); -- 220.232 SELECT CAST(220.23211231 AS DECIMAL(10, 3));
十三、current_date()函数
CURRENT_DATE()函数按YYYY-MM-DD格式返回系统的当前日期。
CURDATE()函数与CURRENT_DATE()函数完全相同。
SELECT CURRENT_DATE, CURRENT_DATE(), CURRENT_DATE() + 0;
结果：
注意： CURRENT_DATE() + 0 的结果为 YYYYMMDD 格式。
CURRENT_DATE() + N 意味着当前日期加上 N 天。比如，系统当前日期加 1 天：
SELECT CURRENT_DATE() + 1;
结果：
十三、字符串函数insert()
INSERT函数是MySQL中用于在字符串中插入指定字符或子字符串的函数。该函数的语法如下：
INSERT(str, pos, len, newstr)
其中，参数说明如下：
– str：要进行插入操作的原始字符串。
– pos：插入位置的索引值。从1开始计数。
– len：插入的字符个数。
– newstr：要插入的字符串。
在两个字符串之间添加空格的示例
下面是一个示例，用于演示如何使用INSERT函数在两个字符串之间添加一个空格。
假设我们有两个字符串，分别为”Hello”和”World”，我们想在这两个字符串之间添加一个空格。
可以使用如下的INSERT函数来实现：
SELECT INSERT('HelloWorld', 6, 0, ' ');
运行以上代码，会返回结果为”Hello World”。可以看到，在第6个位置插入了一个空格。
同样，可以通过改变参数来插入多个空格，在不同的位置进行插入。
INSERT函数的注意事项
在使用INSERT函数时，需要注意以下几点：
1. 插入位置的索引值pos不能超过原始字符串的长度。否则会导致插入失败。
2. 如果需要插入的字符个数len超过了原始字符串的长度，对于MySQL来说，会自动截断原始字符串，插入指定字符。
十四、locate()
LOCATE()函数在SQL中用于在字符串中查找字符串的位置，LOCATE()函数返回子字符串在主字符串中第一次出现的位置，如果未找到子字符串，则返回0。
语法：
LOCATE(substring, string, start)
参数
substring是要查找的子字符串string是要在其中查找子字符串的主字符串start是可选参数，表示搜索的起始位置。如果省略，则从字符串的开头开始搜索
select locate('海勒斯壕南','海勒斯壕南泰一亨')
结果为1.
十四、去除空格
在MySQL中，可以使用TRIM()函数去除字符串前后的空格。
例如，假设有一个名为users的表，其中有一个名为name的字段，你想要去除这个字段值前后的空格：
UPDATE users SET name = TRIM(name);
如果只想去除前面或后面的空格，可以使用LTRIM()或RTRIM()函数。
如果只想去除前面的空格：
UPDATE users SET name = LTRIM(name);
如果只想去除后面的空格：
UPDATE users SET name = RTRIM(name);
十五、判断字符串长度
在MySQL中，可以使用CHAR_LENGTH()函数来判断字符串的长度，该函数返回字符串的字符数。如果需要判断字节长度，可以使用LENGTH()函数。
SELECT CHAR_LENGTH('你好世界') AS length; -- 输出 4，因为这是中文字符
使用LENGTH()函数时，需要注意它将根据字符集返回字节长度，对于UTF-8编码，一个中文字符将占用3个字节。
SELECT LENGTH('你好世界') AS length; -- 输出 12，因为每个中文字符在UTF-8下占3个字节
如果需要基于某种条件判断字符串长度，可以在SELECT语句中嵌套IF或CASE语句。例如，判断一个字符串长度，如果长度大于10，则返回1，否则返回0：
SELECT IF(CHAR_LENGTH('你好世界') > 10, 1, 0) AS length_check; -- 输出 0
案例
SELECT distinct type_code, IF(CHAR_LENGTH(type_code) > 4, '其他', type_name) from table_name;
十六、四舍五入
在MySQL中，可以使用ROUND()函数来实现四舍五入。ROUND()函数可以将数字四舍五入到指定的小数位数。
例如，如果你想要将数值3.56789四舍五入到小数点后两位，你可以使用以下SQL语句：
SELECT ROUND(3.56789, 2);
