mysql多表关联查询无法直接关联
- 情景描述
- 解决方案
情景描述
现有两张表 t_author
和t_book
;
t_author
是作者表,t_book
是书籍表;表数据如下:
需要实现以下功能:查询书籍列表的接口中支持对作者姓名的模糊查询
存在的问题:不同于普通的多表关联查询,author_uuid字段中存放的是作者uuid的集合,各个作者uuid之间以逗号隔开,无法直接进行数据关联
根本原因:设计缺陷:书籍和作者之间是一对多的关系,那么在建表的时候,可以通过建立书籍表、作者表、书籍作者关联表【表中每个字段里只填充一个值,如果是一本书,多个作者,则新增多行数据】来实现业务逻辑,而不是把这种一对多的关系建立成一对一
合理的建表如下:
书籍表t_book
:
作者表t_author
:
书籍作者关联关系表t_r_book_author
:
- 多表关联查询:
SELECT
book.book_id,
book.book_name,
author.author_name
FROM
t_book_copy1 book
LEFT JOIN t_r_book_author relation ON book.book_id = relation.book_id
LEFT JOIN t_author_copy1 author ON relation.author_uuid = author.uuid
查询结果如下:
2. 将查询到的数据根据书籍进行合并【借助group_concat
和group by
合并作者那一栏】
SELECT
book.book_id,
book.book_name,
group_concat(author.author_name) as author_names
FROM
t_book_copy1 book
LEFT JOIN t_r_book_author relation ON book.book_id = relation.book_id
LEFT JOIN t_author_copy1 author ON relation.author_uuid = author.uuid
group by book.book_id,
book.book_name
group_concat:字符串拼接函数,默认以,拼接各项数据
group_concat(distinct author.author_name separator ‘%’)实现去重、换拼接的分隔符为%
查询结果如下:
以上步骤是为了让我们直观的看到每本书籍对应哪些作者,以校验模糊查询结果是否正确
查询书籍列表的接口中支持对作者姓名的模糊查询sql如下
SELECT
book.book_id,
book.book_name
FROM
t_book_copy1 book
LEFT JOIN t_r_book_author relation ON book.book_id = relation.book_id
LEFT JOIN t_author_copy1 author ON relation.author_uuid = author.uuid
WHERE
author.author_name LIKE concat('%',"宰",'%')
查询结果如下:
解决方案
那么,如果表就设计成了文章一开头的结构,该如何进行模糊查询呢?
数据:
想要的效果:
实现sql:
SELECT
book.book_id,
book.book_name,
GROUP_CONCAT( author.author_name ) as author_names
FROM
t_book book,
t_author author
WHERE
FIND_IN_SET( author.uuid, book.author_uuid )
GROUP BY
book.book_id,
book.book_name
查询结果截图:
查询书籍列表的接口中支持对作者姓名的模糊查询sql如下
SELECT
book.book_id,
book.book_name
FROM
t_book book,
t_author author
WHERE
FIND_IN_SET( author.uuid, book.author_uuid ) and author.author_name LIKE concat('%',"宰",'%')
查询结果如下:
FIND_IN_SET(str,strlist): str 要查询的字符串,strlist 需查询的字段,参数以”,”分隔,形式如t_book表中的author_uuid;该函数的作用是查询字段(strlist)中是否包含(str)的结果,返回结果为null或记录