一些有的没得小知识

1. 空间函数 st_distance_sphere / st_distance 计算距离

st_distance在mysql5.6+加入的;st_distance_sphere 在mysql5.7+加入的

通过经纬度查询距目标(113.8064049, 22.7300434)最近的五个地点，详情可见链接：参考链接

SELECT city,distance FROM (SELECT *, ST_DISTANCE_SPHERE(point (113.8064049, 22.7300434),point(longitude,latitude)) AS DISTANCE FROM data_point) A
ORDER BY distance ASC LIMIT 5

2. Stream流将List转Map

List<ContentVO> vos = contentService.findContentServicePage(vo);

Map<String, List<ContentVO>> listMap = vos.stream().collect(Collectors.groupingBy(ContentVO::getName));

Map<String, ContentVO> voMap = vos.stream().collect(Collectors.toMap(ContentVO::getName, Function.identity()));
// key 冲突时后者进行覆盖
Map<String, ContentVO> contentVOMap = vos.stream().collect(Collectors.toMap(ContentVO::getName, Function.identity(), (oldValue, newValue) -> newValue));

Map<String, String> stringMap = vos.stream().collect(Collectors.toMap(ContentVO::getName, ContentVO::getSubTitle));
Map<String, String> collect = vos.stream().collect(Collectors.toMap(ContentVO::getName, ContentVO::getSubTitle, (oldValue, newValue) -> newValue));

3. 批量连表更新：

举个例子：将下表的business_id值刷到supplier_branch_id字段

刷数据SQL脚本如下：

• 通过UPDATE 和INNER JOIN 进行连表更新即可，明确号关联条件
• 当然也可以用CASE WHEN 的形式，当没有这种语法方便

UPDATE `t_process_instance` a
INNER JOIN (
SELECT b.id , b.business_id FROM t_process_instance b WHERE b.process_type = 23
) c ON a.id = c.id
SET a.supplier_branch_id = c.business_id