前言
平时MySQL开发时，经常用到的小技巧。简单记录总结一下，方便下次查看技巧的使用方式。
1、MySQL的MD5函数
select md5(uuid());
2、MySQL建表时常有的3个字段，逻辑删除标识，创建时间，更新时间，可用Navicat for MySQL可视化建表
del_state tinyint(4) 非空 逻辑删除标识，0正常，1已删除
create_time datetime 非空 创建时间 CURRENT_TIMESTAMP
create_time datetime 非空 更新时间 CURRENT_TIMESTAMP 根据当前时间戳更新
3、MySQL某一列多行合并为一行
select user_id, group_concat(dept) from student group by user_id;
4、TIMESTAMPDIFF(unit, begin, end)函数，该函数返回begin-end的结果，其中begin和end是date或datetime表达式
select TIMESTAMPDIFF(month, '2020-08-01', '2021-11-01')
5、FIND_IN_SET(字段, 集合)，查询数量上相比in函数，无限制，但是不走索引
FIND_IN_SET(id, ('1','3','5'))
6、多行转一行
select GROUP_CONCAT(目标字段 SEPARATOR ',') from 数据表
7、根据查询的数据进行更新的SQL语句格式
update table_a set type = 0 where id in(
select temp.id from (
select id from table_b where number like "AAA"
) temp
)
8、MySQL使用timediff方法时，Java处理小时数值大于23时报错的解决方案
timediff(czq.end_date, czq.start_date)
说明：timediff方法返回两个时间/日期时间值之间的差，由于时间格式中小时的最大值是23，一旦结果超过这个数字，就会报错
# 123:18:12
CONCAT(timediff(czq.end_date, czq.start_date), '')
说明：CONCAT方法返回结果为连接参数产生的字符串
# 123:18:12
9、MySQL多表连接批量更新语句示例，还可多表更新，如再加b.job = '科学家', c.number = 15
update
A表 as a
left join B表 as b on b.name = a.dept_name
left join C表 as c on c.id = a.emp_id
set
a.dept_id = b.id, a.desc = b.desc
where
c.version_id = 25
and c.id in (333, 666, 999)
10、使用MySQL对单行ID集合转成多行并且去重
select
DISTINCT substring_index(substring_index('126672,126673,126675,126676', ',', help_topic_id + 1), ',' , -1) as num
from
mysql.help_topic
where
help_topic_id < length('126672,126673,126675,126676') - length(replace('126672,126673,126675,126676', ',', '')) + 1
11、抑制ONLY_FULL_GROUP_BY值被拒绝的函数
-- 报错：which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select id, product_id, info, create_time from t_history_operate group by product_id
-- MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝，另外可使用MIN或MAX聚合函数代替ANY_VALUE。
select id, product_id, info, create_time from t_history_operate where id in (
select MAX(id) as history_id from t_history_operate group by product_id
)
12、Case When 用法
select id, hero from t_glory_of_kings;
id hero
1 亚瑟
2 云缨
3 后羿
4 吕布
select
id,
hero as '英雄',
case hero
when '亚瑟' then '契约胜利之剑'
when '云缨' then '掠火神枪'
when '后羿' then '射日弓'
when '吕布' then '方天画戟'
end as '装备'
from
t_glory_of_kings;
id 英雄 装备
1 亚瑟 契约胜利之剑
2 云缨 掠火神枪
3 后羿 射日弓
4 吕布 方天画戟
