两表关联查询：sql、mybatis

MySQL 8.0.33

mybatis 3.5.15

mybatis-plus 3.5.5

---

序章

功能：给文本内容打标签。

文本表：text，主键 uuid，还有 content 字段。

文本标签表：text_tag，主键uuid，字段text_uuid 为 text表的主键，还有 tag_name 字段——标签名。

ben发布于博客园

需求：

查找包含所有标签名的文本。

数据表准备

text

CREATE TABLE `text` (
  `uuid` varchar(100) NOT NULL COMMENT '主键',
  `user_uuid` varchar(100) NOT NULL COMMENT '用户主键',
  `content` varchar(1024) NOT NULL COMMENT '内容。原始文本，包含 空格、换行符、链接 等。',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '已删除？',
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文本';

text_tag

CREATE TABLE `text_tag` (
  `uuid` varchar(100) NOT NULL COMMENT '主键',
  `user_uuid` varchar(100) NOT NULL COMMENT '用户主键',
  `text_uuid` varchar(100) NOT NULL COMMENT '文本主键',
  `tag_name` varchar(50) NOT NULL COMMENT '标签名称',
  `tag_order` int DEFAULT '0',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `uq_idx_text_tag` (`text_uuid`,`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文本标签';

SQL 部分

1个标签名

1个标签名：a

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	where t1.tag_name = 'a'
)
order by create_time desc
;

ben发布于博客园

2个标签名

2个标签名：a, b

要点：

用到了 join；

join 表自己；

on 后面有多个条件。

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	join text_tag t2 on t1.text_uuid = t2.text_uuid and t1.tag_name = 'a' and t2.tag_name = 'b'
)
order by create_time desc
;

多于 2个标签名

4个标签名：a, b, c, d

要点：

多次 join 表自己。

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	join text_tag t2 on t1.text_uuid = t2.text_uuid and t1.tag_name = 'a' and t2.tag_name = 'b'
	join text_tag t3 on t1.text_uuid = t3.text_uuid and t3.tag_name = 'c'
	join text_tag t4 on t1.text_uuid = t4.text_uuid and t4.tag_name = 'd'
)
order by create_time desc
;

通义千问的建议

最初，通义千问建议使用 INTERSECT（求交集）的：

select uuid from text where uuid in (
select  text_uuid from text_tag where tag_name = '1'
INTERSECT
select  text_uuid from text_tag where tag_name = 'a'
)

可是，自己的MySQL不支持：可千问说 mysql 8.0.31 就支持了。

SELECT VERSION(); -- 8.0.33
SHOW VARIABLES LIKE 'sql_mode'; -- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

参考资料：

1. SQL INTERSECT操作符简介

https://www.yiibai.com/mysql/sql-union-mysql.html

未读 TODO。

还需调查。

ben发布于博客园

mybatis 部分

java

定义了分页查询接口：

pageFindCount 查总数；

pageFindData 查数据。

public interface AppTextMapper extends BaseMapper<AppText> {

    /**
     * 分页查找：返回总数
     * @param dto 入参
     * @param tag1 可以为null，来自 dto tagNameList#1
     * @param tag2 可以为null，来自 dto tagNameList#2
     * @param tag3 可以为null，来自 dto tagNameList#3
     * @param tag4 可以为null，来自 dto tagNameList#4
     * @param tag5 可以为null，来自 dto tagNameList#5
     * @return
     */
    long pageFindCount(PageFindDTO dto,
                       String tag1,
                       String tag2,
                       String tag3,
                       String tag4,
                       String tag5);

    /**
     * 分页查找：返回 记录列表
     * @param dto 入参
     * @param tag1 可以为null，来自 dto tagNameList#1
     * @param tag2 可以为null，来自 dto tagNameList#2
     * @param tag3 可以为null，来自 dto tagNameList#3
     * @param tag4 可以为null，来自 dto tagNameList#4
     * @param tag5 可以为null，来自 dto tagNameList#5
     * @return
     */
    List<AppText> pageFindData(PageFindDTO dto,
                               String tag1,
                               String tag2,
                               String tag3,
                               String tag4,
                               String tag5);

}

ben发布于博客园

调用 mapper 的函数（有些搞笑）：

@Override
public long pageFindCount(PageFindDTO dto) {
	if (Objects.isNull(dto)) {
		// 可改为 抛出异常
		return 0;
	}

	System.out.println("dto=" + dto);
	List<String> tags = dto.getTagNameList();
	if (CollectionUtils.isEmpty(tags)) {
		return this.baseMapper.pageFindCount(dto, null, null, null, null, null);
	}
	// 搞笑...TODO
	int size = tags.size();
	switch (size) {
		case 1:
			return this.baseMapper.pageFindCount(dto, tags.get(0), null, null, null, null);
		case 2:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), null, null, null);
		case 3:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), null, null);
		case 4:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), tags.get(3), null);
		case 5:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), tags.get(3), tags.get(4));
			default:
				return 0;
	}
}

这么些的原因：

在 mybatis xml 中使用下面的方式获取数据失败：

tagNameList[0]
tagNameList.get(0)

疑问：

难道就没有其它方式了吗？

比如，mybatis xml 的 <bind> 标签？晚点问问通义灵码。

mybatis xml

pageFindCount 的：

要点：

<where> 的使用；

判断 dto 中列表大小：size() == 1，两个等号。

<select id="pageFindCount" resultType="java.lang.Long">
	select count(1) from text
	<where>
		<if test="dto.tagNameList != null and dto.tagNameList.size() > 0">
			uuid in (
			select t1.text_uuid from text_tag t1
			<if test="dto.tagNameList.size() == 1">
				WHERE t1.tag_name = #{tag1}
			</if>
			<if test="dto.tagNameList.size() > 1">
				JOIN text_tag t2 on t1.text_uuid = t2.text_uuid
				AND t1.tag_name = #{tag1}
				AND t2.tag_name = #{tag2}
			</if>
			<if test="dto.tagNameList.size() > 2">
				join text_tag t3 on t1.text_uuid = t3.text_uuid
				AND t3.tag_name = #{tag3}
			</if>
			<if test="dto.tagNameList.size() > 3">
				join text_tag t4 on t1.text_uuid = t4.text_uuid
				AND t4.tag_name = #{tag4}
			</if>
			<if test="dto.tagNameList.size() > 4">
				join text_tag t5 on t1.text_uuid = t5.text_uuid
				AND t4.tag_name =#{tag5}
			</if>
			)
		</if>

		<if test="dto.startTime != null">
			and create_time &gt;= #{dto.startTime}
		</if>

		<if test="dto.endTime != null">
			and create_time &lt; #{dto.endTime}
		</if>

		<if test="dto.keywords != null and dto.keywords.size() > 0">
			<foreach collection="dto.keywords" item="kw" open="" close="" separator="">
				and content like concat('%', #{kw} , '%')
			</foreach>
		</if>
	</where>

	;
</select>

自测：

dto.tagNameList 为null；
dto.tagNameList 为空；
dto.tagNameList 有 1个值；
dto.tagNameList 有 2个值；
dto.tagNameList 有 5个值；

mybatis 部分更新

注，240418 更新。

之前写的太“搞笑”了。试着用 <bind>标签进行了改进，果然行。

mapper 就是正常的传一个 DTO 即可。

在 mybatis 的 xml 中，可以如下面的进行改造（另一个模块的 pageFindCount 接口）：

<if test="tags != null and tags.size() > 0">
	<bind name="tag1" value="tags[0]"></bind>
	uuid in (
	select t1.record_uuid from file_tag t1
	<if test="tags.size() == 1">
		WHERE t1.tag_name = #{tag1} and t1.type = 1
	</if>
	<if test="tags.size() > 1">
		<bind name="tag2" value="tags[1]"></bind>
		JOIN file_tag t2 on t1.type = 1 AND t2.type = 1 AND t1.record_uuid = t2.record_uuid
		AND t1.tag_name = #{tag1}
		AND t2.tag_name = #{tag2}
	</if>
	<if test="tags.size() > 2">
		<bind name="tag3" value="tags[2]"></bind>
		JOIN file_tag t3 on t3.type = 1 AND t1.record_uuid = t3.record_uuid
		AND t3.tag_name = #{tag3}
	</if>
	<if test="tags.size() > 3">
		<bind name="tag4" value="tags[3]"></bind>
		JOIN file_tag t4 on t4.type = 1 AND t1.record_uuid = t4.record_uuid
		AND t4.tag_name = #{tag4}
	</if>
	<if test="tags.size() > 4">
		<bind name="tag5" value="tags[4]"></bind>
		JOIN file_tag t5 on t5.type = 1 AND t1.record_uuid = t5.record_uuid
		AND t5.tag_name = #{tag5}
	</if>
	)
</if>

可行。晚点再把之前的“搞笑”代码完善下。

致谢

通义千问

通义灵码

各位博主

---END---

本文链接：

https://www.cnblogs.com/luo630/p/18130381

ben发布于博客园

参考资料

1、left join on多条件深度理解
perfect-ws
已于 2022-06-17 17:34:10 修改
原文链接：
https://blog.csdn.net/cxywangshun/article/details/124472945

2、mybatis中数组和List使用下标取值以及传入多个参数时取值
zhangbeizhen18
于 2023-04-18 22:36:45 发布
https://blog.csdn.net/zhangbeizhen18/article/details/130231821

3、

ben发布于博客园

posted @ 2024-04-12 05:56 快乐的欧阳天美1114 阅读(20) 评论(0) 编辑收藏举报

刷新页面返回顶部

`三一三`

用问题刺激思想，用执行消灭懒散，

两表关联查询：sql、mybatis

序章

数据表准备

SQL 部分

1个标签名

2个标签名

多于 2个标签名

通义千问的建议

mybatis 部分

java

mybatis xml

mybatis 部分更新

致谢

参考资料

公告

`三一三`

用问题刺激思想，用执行消灭懒散，

两表关联查询：sql、mybatis

序章

数据表准备

SQL 部分

1个 标签名

2个 标签名

多于 2个 标签名

通义千问 的建议

mybatis 部分

java

mybatis xml

mybatis 部分 更新

致谢

参考资料

公告

1个标签名

2个标签名

多于 2个标签名

通义千问的建议

mybatis 部分更新