MySQL常用应用函数笔记

原创已于 2024-01-22 18:11:16 修改 · 538 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #笔记 #数据库

于 2024-01-22 16:59:39 首次发布

本文详细介绍了MySQL中的group_concat函数与find_in_set函数的联合使用，以及date_format,date_sub,DATE_ADD等时间格式化和加减函数，还涵盖了LEFT函数和中文排序的方法。

group_concat函数和find_in_set函数的结合应用

MySQL手册中find_in_set函数的语法：

FIND_IN_SET(str,strlist)

str——要查询的字符串
strlist——字段名参数以”,”分隔如 (1,2,6,8)
查询字段(strlist)中包含(str)的结果，返回结果为null或记录
如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

示例：

--在一对多的前提下（table1一对多条table2） 
--以一（table1）为标准 返回不重复的列表数据，
--而table2以逗号拼接后 连接在主表后一并返回
	select t1.*,
	    (select group_concat(sdi.item_text separator '，') from sys_dict_item sdi
	        join sys_dict sd on sd.id = sdi.dict_id and sd.dict_code = 'xxx'
	    where find_in_set(sdi.item_value, t1.tag_value)) as xxx_name
	from (
		select a.id, a.member_name,
		group_concat(distinct b.tag_value order by b.tag_value) as tag_value
		from table1 a
		left join table2 b on a.id = b.member_id
		group by a.id,a.member_name
	)t1
--MySQL5.7 版本写法：
group_concat(distinct b.tag_value order by b.tag_value)
--MySQL8.0+版本分组拼接时更改为：
group_concat(distinct concat(b.tag_value,'') COLLATE utf8mb4_general_ci order by b.tag_value)

date_format时间格式化函数

date_format('2023-10-08 15:06:07', '%Y-%m-%d %H:%i:%s')

格式	描述
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	大写i。小时 (01-12)
%i	分钟，数值(00-59)
%m	月，数值(00-12)
%S	秒(00-59)
%s	秒(00-59)
%Y	年，4 位
%y	年，2 位

date_sub时间加减函数

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
date是指定的日期，INTERVAL为关键词，expr是具体的时间间隔，type是时间单位。

减一天： 
	date_sub('2023-10-08 01:01:01', interval 1 day)
	运行结果：2023-10-07 01:01:01
加一天： 
	date_sub('2023-10-08 01:01:01', interval -1 day) 或者 date_add('2023-10-08 01:01:01', interval 1 day)
	运行结果：2023-10-09 01:01:01

注意：type可以复合型的，比如YEAR_MONTH。如果type不是复合型的，DATE_ADD和DATE_SUB其实可以通用，因为expr可以为一个负数。对应复合型的type，需要使用引号对两个参数进行引用起来，中间用任何非数字字符作为间隔即可，并且不能使用负数。

举个栗子
 select date_add('2023-01-18', interval '1 2' YEAR_MONTH);
 运行结果： 2024-03-18                                          
 select date_add('2023-01-18', interval '1-2' YEAR_MONTH);
 运行结果： 2024-03-18                                         
 select date_add('2023-01-18', interval '1,2' YEAR_MONTH);
 运行结果： 2024-03-18

常用类型：

type	描述
MICROSECOND	间隔单位：毫秒
SECOND	间隔单位：秒
MINUTE	间隔单位：分钟
HOUR	间隔单位：小时
DAY	间隔单位：天
WEEK	间隔单位：星期
MONTH	间隔单位：月
QUARTER	间隔单位：季度
YEAR	间隔单位：年

convert转换函数

left函数(str，n)
从str字符串的左边开始截取n个字符

按照中文第一个字排序：
	order by convert(left(str,1) using GBK) asc