MYSQL中UNION多张表之后对共同字段进行模糊查询失效

最新推荐文章于 2024-01-04 16:16:38 发布

小白0270

最新推荐文章于 2024-01-04 16:16:38 发布

阅读量482

点赞数

文章标签： java mysql

本文链接：https://blog.csdn.net/m0_73457854/article/details/131228737

版权

遇到过几次这种情况了，虽然已经解决，但是并不明白失效的原理，有大佬懂的麻烦传授一下

代码示例如下框架是 mybatis

 SELECT
  k1.id,
  k1.postName,
  k1.types,
 FROM (
      SELECT
         t1.id AS id,
         t1.post_name AS postName,
         t1.types AS types,
            FROM upms_post t1
            INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id
            INNER JOIN upms_teacher_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0
      WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002'
      UNION ALL
      SELECT
         t1.id AS id,
         t1.post_name AS postName,
         t1.types AS types,
      FROM upms_post t1
         INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id
         INNER JOIN upms_student_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0
      WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002'
      UNION ALL
      ....后略
   ) AS k1
 WHERE k1.id IS NOT NULL
<if test="info.search != null and info.search !=''">
 AND k1.postName LIKE CONCAT('%',#{info.search,jdbcType=VARCHAR},'%')
</if>
....后略
 GROUP BY k1.id

上述代码执行过程中 当info.search为汉字时模糊查询失效，并且结果为null，若info.search为数字或英文字符时则输出正常的模糊查询结果，若info.search为空时则正常查询。


解决办法：
第一种
SELECT k1.id, k1.postName, k1.types 
FROM 
( SELECT t1.id AS id, t1.post_name AS postName, t1.types AS types 
FROM upms_post t1 
INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id 
INNER JOIN upms_teacher_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0 WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002' 
UNION ALL 
SELECT
t1.id AS id, t1.post_name AS postName, t1.types AS types 
FROM upms_post t1 
INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id 
INNER JOIN upms_student_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0 WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002' 
UNION ALL ....后略 ) AS k1
WHERE k1.id IS NOT NULL
GROUP BY k1.id
<trim prefix="HAVING" prefixOverrides="AND ">
   <if test="info.search != null and info.search !=''">
      AND k1.postName LIKE CONCAT('%',#{info.search,jdbcType=VARCHAR},'%')
   </if>
</trim>

将where后的条件查询放到group by having 语句中执行成功

第二种：

将where后的条件查询放入各个union的查询中

例如

SELECT t1.id AS id, t1.post_name AS postName, t1.types AS types
FROM upms_post t1
INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id
INNER JOIN upms_teacher_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0

WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002'

AND t1.postName LIKE CONCAT('%',#{info.search,jdbcType=VARCHAR},'%')

</if>

UNION ALL
SELECT
t1.id AS id, t1.post_name AS postName, t1.types AS types
FROM upms_post t1
INNER JOIN upms_post_user t2 ON t2.delete_flag = 0 AND t1.id = t2.post_id
INNER JOIN upms_student_account AS t3 ON t3.id = t2.user_id AND t3.delete_flag = 0

WHERE t1.delete_flag = 0 AND t1.id != 'd9258c001c4a11eda0f80242ac110002'

AND t1.postName LIKE CONCAT('%',#{info.search,jdbcType=VARCHAR},'%')

</if>

UNION ALL ....后略

实测可以解决模糊查询失效的问题