测试过程中偶然发现的Mysql中str_to_date()与date_format()的一点差异

前言

       今天在测试过程中发现str_to_date()与date_format()有点不同：在Java程序中使用str_to_date()和date_format()都可以查出结果，但是在Navicat中使用str_to_date()时竟然查不到结果，而date_format()可以。让人有点困惑，特此一记。

SQL(一)与SQL(二)语句唯一不同就在于where条件:

    AND a.ALARM_TIME >= DATE_FORMAT(now(),'%Y-%m-%d 00:00:00')
    AND a.ALARM_TIME <= DATE_FORMAT(now(),'%Y-%m-%d 23:59:59')

和

    AND a.ALARM_TIME >= str_to_date('2020-07-21 00:00:00','#Y-#m-#d #H:#i:#s')
    AND a.ALARM_TIME <= str_to_date('2020-07-21 23:59:59','#Y-#m-#d #H:#i:#s')

其他条件均一致。

SQL(一).

SELECT
	a.run_org_code AS orgCode,
	CONCAT( a.run_org_name, '(', a.run_org_code, ')' ) AS runOrgName,
	a.USER_ORG_CODE,
	a.CAR_NUM,
	a.ROUTE_CODE,
	a.ROUTE_NAME,
	a.BILL_NUM,
	a.BILL_NO,
	a.CAR_ID,
	a.ALARM_TIME,
	date_format( a.ALARM_TIME, '#Y-#m-#d #H:#i:#s' ) AS alarmTimeStr,
	a.ALARM_FLAG,
	a.ALARM_DETIL,
	a.ALARM_RELEASE_TIME,
	date_format( a.ALARM_RELEASE_TIME, '#Y-#m-#d #H:#i:#s' ) AS alarmReleaseTimeStr,
CASE
	
	WHEN a.alarm_flag = '1' THEN
	'执行邮路异常停驻' 
	WHEN a.alarm_flag = '2' THEN
	'邮路偏离报警' 
	WHEN a.alarm_flag = '3' THEN
	'厢门开启报警' 
	WHEN a.alarm_flag = '4' THEN
	'车辆超速报警' 
	WHEN a.alarm_flag = '6' THEN
	'疲劳驾驶报警' 
	WHEN a.alarm_flag = '8' THEN
	'邮路运行期设备失步' 
	WHEN a.alarm_flag = '11' THEN
	'烟雾报警' 
	WHEN a.alarm_flag = '13' THEN
	'干线车辆早发异常' ELSE '其他' 
	END AS alarmFlagStr,
CASE
		
		WHEN a.del_flag = '0' THEN
		'未处理' 
		WHEN a.del_flag = '1' THEN
		'已处理' 
	END AS delFlagStr,
	a.DEL_FLAG,
	a.DRIVER_NAME,
	a.DRIVER_NO,
	a.SPEED,
	a.ALARM_IS,
	a.ALARM_CONN,
	a.ALARM_ID,
	a.PC_NO,
	a.GMT_MODIFIED,
	date_format( a.GMT_MODIFIED, '#Y-#m-#d #H:#i:#s' ) AS gmtModifiedStr,
	a.veh_company 
FROM
	mpm_gps_caralarm_detail a,
	ods_bic_org_relationship b,
	ods_bic_org_relationship c,
	ods_bic_org_relationship d 
WHERE
	1 = 1 
	AND a.run_org_code = b.org_code 
	AND a.org_code_origin = c.org_code 
	AND a.org_code_terminal = d.org_code 
	AND a.ALARM_LEVEL = '1' 
	AND a.ALARM_TIME >= DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') 
	AND a.ALARM_TIME <= DATE_FORMAT(now(),'%Y-%m-%d 23:59:59') 
	AND a.LINE_TYPE IN ( '1' ) 
	AND a.DEL_FLAG IN ( '0' ) 
	AND a.ALARM_FLAG IN ( '13' ) 
	AND a.is_delete = '0' 
ORDER BY
a.run_org_code;

 

这个语句在Java程序和Navicat中执行都可以查找数据。

 

SQL(二).

SELECT
    a.run_org_code AS orgCode,
    CONCAT( a.run_org_name, '(', a.run_org_code, ')' ) AS runOrgName,
    a.USER_ORG_CODE,
    a.CAR_NUM,
    a.ROUTE_CODE,
    a.ROUTE_NAME,
    a.BILL_NUM,
    a.BILL_NO,
    a.CAR_ID,
    a.ALARM_TIME,
    date_format( a.ALARM_TIME, '#Y-#m-#d #H:#i:#s' ) AS alarmTimeStr,
    a.ALARM_FLAG,
    a.ALARM_DETIL,
    a.ALARM_RELEASE_TIME,
    date_format( a.ALARM_RELEASE_TIME, '#Y-#m-#d #H:#i:#s' ) AS alarmReleaseTimeStr,
CASE
    
    WHEN a.alarm_flag = '1' THEN
    '执行邮路异常停驻'
    WHEN a.alarm_flag = '2' THEN
    '邮路偏离报警'
    WHEN a.alarm_flag = '3' THEN
    '厢门开启报警'
    WHEN a.alarm_flag = '4' THEN
    '车辆超速报警'
    WHEN a.alarm_flag = '6' THEN
    '疲劳驾驶报警'
    WHEN a.alarm_flag = '8' THEN
    '邮路运行期设备失步'
    WHEN a.alarm_flag = '11' THEN
    '烟雾报警'
    WHEN a.alarm_flag = '13' THEN
    '干线车辆早发异常' ELSE '其他'
    END AS alarmFlagStr,
CASE
        
        WHEN a.del_flag = '0' THEN
        '未处理'
        WHEN a.del_flag = '1' THEN
        '已处理'
    END AS delFlagStr,
    a.DEL_FLAG,
    a.DRIVER_NAME,
    a.DRIVER_NO,
    a.SPEED,
    a.ALARM_IS,
    a.ALARM_CONN,
    a.ALARM_ID,
    a.PC_NO,
    a.GMT_MODIFIED,
    date_format( a.GMT_MODIFIED, '#Y-#m-#d #H:#i:#s' ) AS gmtModifiedStr,
    a.veh_company
FROM
    mpm_gps_caralarm_detail a,
    ods_bic_org_relationship b,
    ods_bic_org_relationship c,
    ods_bic_org_relationship d
WHERE
    1 = 1
    AND a.run_org_code = b.org_code
    AND a.org_code_origin = c.org_code
    AND a.org_code_terminal = d.org_code
    AND a.ALARM_LEVEL = '1'
    AND a.ALARM_TIME >= str_to_date('2020-07-21 00:00:00','#Y-#m-#d #H:#i:#s')
    AND a.ALARM_TIME <= str_to_date('2020-07-21 23:59:59','#Y-#m-#d #H:#i:#s')
    AND a.LINE_TYPE IN ( '1' )
    AND a.DEL_FLAG IN ( '0' )
    AND a.ALARM_FLAG IN ( '13' )
    AND a.is_delete = '0'
ORDER BY
a.run_org_code;

 

这个语句在库中没法查到数据，只因为 select str_to_date('2020-07-21 23:59:59','#Y-#m-#d #H:#i:#s') 执行结果就是null。

原因：猜想是项目使用的封装好的数据访问层框架做了一层转换,导致(二)语句在程序中可以查到结果，但是在Navicat中执行时查找不到。