MYSQL:sql有坑

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

有人也发现了Mysql对join优化不行,他的处理是:在java处理,用多线程[笑哭]确实减少了与硬盘交互的时间https://blog.csdn.net/chujuntu1211/article/details/100818680

Mysql left join慢的分析思路
https://www.cnblogs.com/guo0/articles/13202226.html
https://blog.csdn.net/god_is_gril/article/details/85855755

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值