MySQL与Kingbase8/PostgreSQL日期函数差异及解决方案

问题背景

在将MySQL数据库迁移到Kingbase8（PostgreSQL兼容数据库）过程中，发现原有SQL查询中的日期计算函数DATE_SUB()无法正常工作，报错信息为：ERROR: syntax error at or near "7"。

错误分析

原始MySQL语法

DATE_SUB(CURDATE(), INTERVAL 7 DAY) < Date(a.create_time)

Kingbase8/PostgreSQL不兼容原因

1. Kingbase8/PostgreSQL不支持MySQL的DATE_SUB()函数

2. CURDATE()函数在PostgreSQL中应为CURRENT_DATE

3. 日期计算语法有显著差异

解决方案

修改后的Kingbase8/PostgreSQL语法

a.create_time > (CURRENT_DATE - INTERVAL '7 days')

关键修改点

1. 使用CURRENT_DATE替代CURDATE()

2. 使用- INTERVAL '7 days'替代DATE_SUB(..., INTERVAL 7 DAY)

3. 调整比较方向，更符合PostgreSQL习惯

完整SQL示例

SELECT COUNT(1) FROM t_user AS u 
LEFT JOIN t_auth_log AS a ON u.idcard = a.idcard
<where>
    a.auth_type = 'verify'
    AND u.is_deleted = 0
    AND a.is_deleted = 0
    AND u.product_id = #{productId,jdbcType = BIGINT}
    AND a.product_id = #{productId,jdbcType = BIGINT}
    <if test="code != null and code != '' ">
        AND u.code_path LIKE CONCAT(#{code},'%')
    </if>
    AND a.create_time > (CURRENT_DATE - INTERVAL '7 days')
</where>

常见日期函数对照表

功能	MySQL语法	PostgreSQL/Kingbase8语法
当前日期	CURDATE()	CURRENT_DATE
日期减法	DATE_SUB(date, INTERVAL n DAY)	date - INTERVAL 'n days'
日期加法	DATE_ADD(date, INTERVAL n DAY)	date + INTERVAL 'n days'
当前时间	NOW()	CURRENT_TIMESTAMP

最佳实践建议

1. 在编写跨数据库SQL时，尽量使用标准SQL语法

2. 对于日期计算，PostgreSQL的INTERVAL语法更为灵活

3. 迁移时注意比较操作符的方向调整

4. 考虑使用ORM框架的数据库方言转换功能

总结

MySQL与Kingbase8/PostgreSQL在日期处理函数上存在语法差异，了解这些差异对于数据库迁移和跨平台开发非常重要。通过使用标准的INTERVAL语法和正确的日期函数，可以确保SQL在两种数据库上都能正常工作。