兩表關聯查詢：sql、mybatis

快乐的凡人721發表於2024-04-12
SQLMyBatis

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> 標籤？晚點 問問 通義靈碼。

兩表關聯查詢：sql、mybatis

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個值；

致謝

通義千問

通義靈碼

各位博主

---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、

ben釋出於部落格園

ben釋出於部落格園

相關文章