MySQL-常用语句记录

按照子表的id升序查询出子表字段去重并且逗号隔开

GROUP_CONCAT(DISTINCT column_name ORDER BY column_name SEPARATOR ',')

举例

b.xxx 的值不区分大小写 select a.*, GROUP_CONCAT(DISTINCT b.xxx ORDER BY b.id ASC SEPARATOR ',') as bxxx from order a left join detail b a.id= b.o_id group by a.id
b.xxx的值区分大小写 select a.*, GROUP_CONCAT(DISTINCT BINARY b.xxx ORDER BY b.id ASC SEPARATOR ',') as bxxx from order a left join detail b a.id= b.o_id group by a.id

count(1) count(*) 和 count(name)的区别

count(1)和count(*)

在 MySQL 中，COUNT(*) 和 COUNT(1) 通常是等价的，因为它们都用来计算表中的行数，并且在大多数情况下，它们的性能和结果是一样的,且性能差异微乎其微

SELECT COUNT(*) FROM table;
SELECT COUNT(1) FROM table;

count(name)

功能: 计算 name 列中非 NULL 的行数。如果 name 列包含 NULL 值，则这些 NULL 值不会被计入。
性能: 由于需要考虑 name 列的 NULL 值，因此在某些情况下，性能可能会略逊色于 COUNT(*)，但差别通常不大。

总结: 在大多数情况下，COUNT() 和 COUNT(1) 在 MySQL 中表现一致，你可以使用它们中的任何一个来计算行数。不过，**COUNT() 通常是更直观和推荐的选择**。

数据同步差异排查

场景: 从A库同步一张表A1到B库的B1, A表总共30001,B表30000条.找出没有同步过去的那一条

方案是查询出A表的所有ID,然后把所有id UNION ALL,在进行两个表的关联

SELECT ids.id
FROM (
SELECT  1 as id
UNION ALL SELECT  43581
UNION ALL SELECT  43583
.... 假设中间还有n条
UNION ALL SELECT  43607
UNION ALL SELECT  43609
) AS ids
left join shop_new ds on ds.id = ids.id
where ds.id is null
;

通过叶子结点找到上级树

返回的结果包含blood_id = 23的数据行

用途是, 通过叶子节点找所有上级,然后封装成链表的形式,代码相对复杂.所以可以换一个思路,先通过以下SQL找到上级树.然后从parent_id集合中排除blood_id集合,得到first_prent_id集合,表示当前节点所在上级树的第一层节点的id集合

然后遍历first_prent_id集合,通过Java查询以下的SQL的结果集中, 父id为**first_prent_id.get(i)**的数据,则取到了当前节点的下一级节点列表.

WITH RECURSIVE ancestors AS (
    SELECT id,blood_id,parent_id FROM blood_tree WHERE blood_id = 23
    UNION 
    SELECT p.id,p.blood_id,p.parent_id FROM blood_tree p
    JOIN ancestors a ON p.blood_id = a.parent_id
)
select * from ancestors;

解释:

CTE 表示递归表达式
WITH RECURSIVE ancestors AS (...) 定义了一个名为 ancestors(可以自定义) 的递归 CTE
表达的意思是:
- 以blood_id = 23的数据行row1为参考,查询所有blood_id=row1.parent_id的数据行rowM
- 然后继续对rowM的每一行数据, 进行递归,查询blood_id=rowM.parent_id的所有数据行
- 最后将这些数据UNION在一起.得到关于某个叶子节点的上级树

完整的Java代码如下:

    private List<BloodNodeResVo> getParentBloods(Integer bloodId) {
        List<BloodNodeResVo> treeList = new ArrayList<>();
        List<PtBloodTree> bloodTreeList = ptBloodTreeService.getParentTreeByBloodId(bloodId);
        if (CollectionUtils.isEmpty(bloodTreeList)) {
            return treeList;
        }
        //包括每一层的父节点
        Set<Integer> parentIds = bloodTreeList.stream().map(PtBloodTree::getParentId).collect(Collectors.toSet());
        //包括每一层的子节点
        Set<Integer> bloodIds = bloodTreeList.stream().map(PtBloodTree::getBloodId).collect(Collectors.toSet());
        //所有节点.用于后期批量一次性查SQL.
        Set<Integer> allBloodIds = new HashSet<>();
        allBloodIds.addAll(parentIds);
        allBloodIds.addAll(bloodIds);
        //父节点中移除所有的叶子节点.因为第一层的节点不再有父节点.所以第一层的节点也就不存在于子节点中
        parentIds.removeAll(bloodIds);
        List<PtBloodIdx> ptBloodIdxes = listByIds(allBloodIds);
        Map<Integer, PtBloodIdx> bloodIdxMap = Optional.ofNullable(ptBloodIdxes).orElse(new ArrayList<>()).stream().collect(Collectors.toMap(PtBloodIdx::getId, Function.identity(), (k1, k2) -> k1));
        //遍历第一层的节点
        for (Integer parentId : parentIds) {
            PtBloodIdx ptBloodIdx = bloodIdxMap.get(parentId);
            BloodNodeResVo bloodNodeResVo = new BloodNodeResVo();
            bloodNodeResVo.setBloodId(ptBloodIdx.getId());
            bloodNodeResVo.setBloodName(ptBloodIdx.getBloodName());
            bloodNodeResVo.setNumber(1);
            treeList.add(bloodNodeResVo);
            //获取子节点
            getChild(bloodTreeList, bloodNodeResVo, bloodIdxMap);
        }
        return treeList;
    }

    void getChild(List<PtBloodTree> bloodTreeList, BloodNodeResVo parent, Map<Integer, PtBloodIdx> bloodIdxMap) {
        bloodTreeList.stream().filter(p -> p.getParentId().equals(parent.getBloodId())).forEach(p -> {
            PtBloodIdx bloodIdx = bloodIdxMap.get(p.getBloodId());
            BloodNodeResVo child = new BloodNodeResVo();
            child.setBloodId(bloodIdx.getId());
            child.setBloodName(bloodIdx.getBloodName());
            parent.getChilds().add(child);
            //递归调用子节点的子节点
            getChild(bloodTreeList, child, bloodIdxMap);
        });
    }

通过父节点找到所有子下级树

返回的结果包含blood_id = 23的数据行

WITH RECURSIVE descendants AS (
    SELECT blood_id,parent_id,blood_level FROM blood_tree WHERE blood_id = 23
    UNION 
    SELECT p.blood_id,p.parent_id,p.blood_level FROM blood_tree p
    JOIN descendants a ON p.parent_id = a.blood_id
)
select * from descendants;