业务关系是一对多要分页,并且多的一侧可以作为查询条件,要怎么设计?

减不了肥的小李
  • 23
上海市

比如说一个人有几种性格标签

我自己想了两种方法:

第一种:
设计两张表
user表:

a_idname
1小明
2小李
3小张

tag表:

iduser_idtag
11活泼
21可爱
22活泼
23可爱

分页sql:

select u.id,u.name,t.tag from  (select id,name from `user` limit #{crrent},#{size}) u LEFT JOIN tag t on  u.id=t.user_id;

结果:

1    小明    活泼
1    小明    可爱

然后用stream流在代码里对u.name 进行分组,将t.tag作为子集合返回。如果查询t.tag=活泼作为查询条件,则少了小明的另一个tag=可爱,结果:

1    小明    活泼
2    小李    活泼

达不到预期效果,感觉要是数据量过多,这样做性能也会出问题的吧

第二种:
设计一张表

a_idnametag
1小明活泼,可爱
2小李活泼
3小张可爱

sql:
select * from user where tag like '%活泼%' limit #{crrent},#{size}
能达到预期效果,但是违反了第一范式,数据量大可能性能也会有问题,然后tag去like
查询结果也不太准确

有没有更好的设计思路?

回复
阅读 928
4 个回答
唯一丶
  • 17.4k
美国
✓ 已被采纳

这样?

select *, (select group_concat(tag) from tag where user_id = user.id and tag = '活泼') as tags
from user
where exists(select 1 from tag where user_id = user.id and tag = '活泼' limit 1)
limit 0, 10;

无名
  • 133

这样?

结果

idnametag
1小明活泼
1小明可爱
2小李活泼

关于方法二

我觉得是不可能的,因为你查询多端时,需要扫全表。这是不可接受的

SQL 代码SQLite 测试通过)

WITH
  user(id, name) AS (
    VALUES
      (1, '小明'),
      (2, '小李'),
      (3, '小张')
  ),
  
  tag(id, user_id, tag) AS (
    VALUES
      (1, 1, '活泼'),
      (2, 1, '可爱'),
      (2, 2, '活泼'),
      (2, 3, '可爱')
  )

SELECT u.id, u.name, t.tag
  FROM (SELECT id, name
          FROM user
         WHERE EXISTS (SELECT 1
                         FROM tag
                        WHERE user_id = user.id
                          AND tag = '活泼')
         LIMIT 0, 10) u
  JOIN tag t ON u.id = t.user_id;

可能的性能改进

  1. 上述 SQL 拆成两条后再执行,性能应该会更好(因为外层不需要每一行都有 u.name,应避免这种冗余数据的传输)
  2. 分页方式改成 WHERE user.id > {上一次分页最后一个user.id} ORDER BY user.id LIMIT {每页数量},性能应该会更好(页码越大,越能体现)

如果是mysql的话,第二种一张表的方法,可以使用FIND_IN_SET这个方法,tag里的每一项以逗号隔开,如下

select * from user where FIND_IN_SET('活泼',tag) limit #{crrent},#{size}
宣传栏