两表关联查询: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发布于博客园

ben发布于博客园

 

posted @ 2024-04-12 05:56  快乐的总统95  阅读(14)  评论(0编辑  收藏  举报