mysql多表关联查询无法直接关联（字段里存储的需要关联的数据是以逗号隔开的）

墨蓝 2024-03-25 14:31 27阅读 0赞

#### mysql多表关联查询无法直接关联 ####

*  情景描述
 *  解决方案

## 情景描述 ##

现有两张表 `t_author`和`t_book`；  
`t_author`是作者表，`t_book`是书籍表；表数据如下：  
![在这里插入图片描述][7f462291b73c4763b955549bbd1adce6.png]  
![在这里插入图片描述][03550f9e06f34850850564c7e4f6497c.png]

需要实现以下功能：`查询书籍列表的接口中支持对作者姓名的模糊查询`  
存在的问题：不同于普通的多表关联查询，author\_uuid字段中存放的是作者uuid的集合，各个作者uuid之间以逗号隔开，无法直接进行数据关联

根本原因：设计缺陷：书籍和作者之间是一对多的关系，那么在建表的时候，可以通过建立书籍表、作者表、书籍作者关联表【表中每个字段里只填充一个值，如果是一本书，多个作者，则新增多行数据】来实现业务逻辑，而不是把这种一对多的关系建立成一对一

合理的建表如下：  
书籍表`t_book`：  
![在这里插入图片描述][15d559c645ef428f96f8febab00f7483.png]  
作者表`t_author`：  
![在这里插入图片描述][7f462291b73c4763b955549bbd1adce6.png]  
书籍作者关联关系表`t_r_book_author`：  
![在这里插入图片描述][8d3c1e2bc71e47de9c2eda4627a7c8f0.png]

1.  多表关联查询：

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

查询结果如下：  
![在这里插入图片描述][db98b22b376640b198b88432833b4710.png]  
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 ‘%’）实现去重、换拼接的分隔符为%

查询结果如下：  
![在这里插入图片描述][39c0194fdb214a83bc4108a2779519c3.png]  
以上步骤是为了让我们直观的看到每本书籍对应哪些作者，以校验模糊查询结果是否正确

`查询书籍列表的接口中支持对作者姓名的模糊查询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('%',"宰",'%')

查询结果如下：  
![在这里插入图片描述][f65a13b8e1014396b378cf6b1a6e9492.png]

## 解决方案 ##

那么，如果表就设计成了文章一开头的结构，该如何进行模糊查询呢？  
数据：  
![在这里插入图片描述][7f462291b73c4763b955549bbd1adce6.png]  
![在这里插入图片描述][03550f9e06f34850850564c7e4f6497c.png]  
想要的效果：  
![在这里插入图片描述][14786995082948f0827cfe730f516b29.png]

实现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

查询结果截图：  
![在这里插入图片描述][46448875b4754d69bd1e69dd49fe28d0.png]

`查询书籍列表的接口中支持对作者姓名的模糊查询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('%',"宰",'%')

查询结果如下：  
![在这里插入图片描述][6954e703359e414388dd77bb5ec95c27.png]

FIND\_IN\_SET(str,strlist)： str 要查询的字符串，strlist 需查询的字段，参数以”,”分隔，形式如t\_book表中的author\_uuid；该函数的作用是查询字段(strlist)中是否包含(str)的结果，返回结果为null或记录

[7f462291b73c4763b955549bbd1adce6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1573811e9afd4cb696ee02c7854f7d2c.png
[03550f9e06f34850850564c7e4f6497c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/0a2dc1ca43b443339b4b5538fd522c21.png
[15d559c645ef428f96f8febab00f7483.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e7e7b10e8fad4d788d9074bb7f791e27.png
[8d3c1e2bc71e47de9c2eda4627a7c8f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/758e4a3476294a38b4b3a4a0de80837c.png
[db98b22b376640b198b88432833b4710.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1aff67a6e00e465cb67d7a2d95551ad7.png
[39c0194fdb214a83bc4108a2779519c3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/986c4915af724425b9a952a3969ba693.png
[f65a13b8e1014396b378cf6b1a6e9492.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5191d1b445bf46b093a5998384d94f49.png
[14786995082948f0827cfe730f516b29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7ae0a3a0ceea4aefa31de58fbc247e1e.png
[46448875b4754d69bd1e69dd49fe28d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f1c5423c226b48708efbb5c41b285d12.png
[6954e703359e414388dd77bb5ec95c27.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e8fc6be9e21e4da19f5aae5fc03d34d7.png