MYSQL:sql有坑

最新推荐文章于 2024-04-24 17:11:51 发布

Stack Piston

最新推荐文章于 2024-04-24 17:11:51 发布

阅读量259

点赞数

分类专栏：报错之路工作IT

本文链接：https://blog.csdn.net/CW_SZDX/article/details/109752710

版权

工作IT 同时被 2 个专栏收录

63 篇文章 1 订阅

订阅专栏

报错之路

29 篇文章 0 订阅

订阅专栏

XXX join 的【on】和where的区别： on作链接条件，where作筛选。尤其当你是left join table on的时候，尤为明显：on不成立也不妨碍连接起来（会导致数据看起来茫茫多）。where是必须成立的才显示出来。【有点left和Inner的味道】

on  
  DATE_FORMAT(vac.startTime,'%Y-%m-%d') <= DATE_FORMAT(d.work_day ,'%Y-%m-%d')
	and   DATE_FORMAT(CONCAT(d.work_day ,' 00:00:01'),'%Y-%m-%d %H:%i:%s')  <= vac.endTime

哦，还能这样on的啊(好比当时发现group by也可以多个条件，一样发现新大陆了的兴奋呢)。只要懂原理，知道mysql那些字的行为，反正弱类型，只要逻辑行的多，怎么搞都能连起来查出来有效果（多次需要换一个角度(子查询，左右连接)看sql看结果集，属实马列思想）。省了java/server层一大堆处理，真棒，真想给mysql打钱~
在这里插入图片描述

or

:
最外面别忘了()，否则这个or与你所想要的效果的就变味了
这样的语句or含义是有问题的（你想要的<---->实际释义）

条件123
	<if test="vo.key != null and vo.key != ''">
			and ( es.hr_id LIKE CONCAT(CONCAT('%',#{vo.key},'%')) or es.name_zh LIKE CONCAT(CONCAT('%',#{vo.key},'%')) )
	</if>
条件567

在这里插入图片描述
后果：查出来的结果（行），比你想要的，多得多。
解决：

<include refid="salary_change_examine"/>
		<choose>
			<when test="vo.planIdList != null and vo.planIdList.size()>0 " >
				and sc.hr_id in
				(select effective_job_num
				from ehr_salary_plan_staff esps
				inner join ehr_salary_plan esp on esp.id=esps.p_id
				where esp.id in
				<foreach item="pId" index="index" collection="vo.planIdList" open="(" separator="," close=")">
					#{pId}
				</foreach>
				)
			</when>
			<otherwise>
				and 1=0
			</otherwise>
		</choose>
		<if test="vo.examineStatusList != null and vo.examineStatusList.size()>0 ">
			and sc.examine_status in
			<foreach item="examineStatus" index="index" collection="vo.examineStatusList" open="(" separator="," close=")">
				#{examineStatus}
			</foreach>
		</if>
		<if test="vo.key != null and vo.key != ''">
			and ( es.hr_id LIKE CONCAT(CONCAT('%',#{vo.key},'%')) or es.name_zh LIKE CONCAT(CONCAT('%',#{vo.key},'%')) )
		</if>
        order by sc.examine_status asc,log.created_time desc

ifnull

：

可以理解成，sql查到的结果有数据但值为null时这是成exp2。如果sql查到的结果本就无数据，那效果无论如何都会是null的。
换句话说，ifnull成立的前提是“这行”数据得首先存在(！！！)，然后再说某字段if null如何如何。
在这里插入图片描述
问题句：

	select
                      ifnull(cha.changeTime,'1970-01-01') as effective_date
                 from
                      (select DATE_FORMAT(change_time,'%Y-%m-%d') as changeTime from ehr_salary_change where hr_id='CWA1897' and is_effective='Y' ) cha

在这里插入图片描述
解决：

select ifnull(  (select DATE_FORMAT(change_time,'%Y-%m-%d') as changeTime  from ehr_salary_change where hr_id='CWA1897' and is_effective='Y') ,'1970-01-01'  )

在这里插入图片描述

（子查询写法[有点函数X的味道]和表连接写法，都是好孩子，看实际情况更适合用哪个就用哪个吧，不存在绝对的优劣[这种短sql，子查询虽不利于优化但也慢不到哪去]）
2020年11月25日：发现子查询的一个好处：可以用来减少join表的数据长度/量，优化sql而加快查询效率

 select
        es.hr_id,
        date_format(es.entry_date,'%Y-%m-%d') as entryDateStr,
        date_format(ewsq.quit_actual_date,'%Y-%m-%d')  as quitActualDateStr,
        agwd.is_work,
        wd.work_day
        from ehr_staff es
        inner join ehr_checkwork_attendance_group ag on es.person_category=ag.person_category
        inner join ehr_checkwork_att_gro_work_day agwd on agwd.ag_id=ag.ag_id
        inner join ehr_checkwork_work_day wd on wd.id=agwd.day_id
        left join ehr_wf_staff_quit ewsq on ewsq.hr_id= es.hr_id
        where
            wd.work_month =  #{checkMonth}

后两个表的数据巨多，导致inner join时间巨长。
~~优化：~~ （并没有变快，经测试，inner join直接虚拟表/子表,和直接inner join实际表，一个鸟效果，不相伯仲，查询都要4s左右）

SELECT 
    es.hr_id, date_format(es.entry_date, '%Y-%m-%d') AS entryDateStr
	, date_format(ewsq.quit_actual_date, '%Y-%m-%d') AS quitActualDateStr, agwd.is_work
	, agwd.work_day
FROM 
    ehr_staff es
	LEFT JOIN ehr_wf_staff_quit ewsq ON ewsq.hr_id = es.hr_id
	INNER JOIN ehr_checkwork_attendance_group ag ON es.person_category = ag.person_category
	INNER JOIN (
		SELECT agwdd.ag_id, agwdd.is_work, wd.work_day
		FROM ehr_checkwork_att_gro_work_day agwdd
			INNER JOIN (
				SELECT *
				FROM ehr_checkwork_work_day wdd
				WHERE wdd.work_month = '201811'
			) wd
			ON wd.id = agwdd.day_id
	) agwd
	ON agwd.ag_id = ag.ag_id

当然，这里的子查询会用不到索引，弊端。
分析：并不是没有命中索引的原因（似乎explain一定会有一行是all，不用纠结，不是索引问题。然后，大小表前后顺序什么的mysql早优化了，也不用考虑），就是join表之后数据太多了而慢。此时发现inner join效率竟然还不如改成类似子查询那样联表查询呢（这叫“join”）3.9秒变成了3.0秒

SELECT
	*
FROM
	(
		SELECT
			es.hr_id,
			ag.ag_id,
			date_format(es.entry_date, '%Y-%m-%d') AS entryDateStr,
			date_format(
				ewsq.quit_actual_date,
				'%Y-%m-%d'
			) AS quitActualDateStr
		FROM
			ehr_staff es
		LEFT JOIN ehr_wf_staff_quit ewsq ON ewsq.hr_id = es.hr_id
		INNER JOIN ehr_checkwork_attendance_group ag ON es.person_category = ag.person_category
	) t,
	(
		SELECT
			agwdd.ag_id,
			agwdd.is_work,
			wd.work_day
		FROM
			ehr_checkwork_att_gro_work_day agwdd
		INNER JOIN (
			SELECT
				*
			FROM
				ehr_checkwork_work_day wdd
			WHERE
				wdd.work_month = '201811'
		) wd ON wd.id = agwdd.day_id
	) p
WHERE
	p.ag_id = t.ag_id