如何在nativeQuery中使用函数？MySQL日期格式化查询报错排查

我编写了一条原生SQL查询语句，期望返回特定格式的日期，代码如下：

@Query(value = "SELECT function('date_format', c.valid_to, '%Y-%m-%d') " +
        "FROM card c WHERE c.card_id= :card_id", nativeQuery = true)

执行时出现语法错误：

您的SQL语法有误；请查阅与您的MySQL服务器版本对应的手册，以获取在'function('date_format', c.valid_to, '%Y-%m-%d') FROM card c WHERE c.card_id= 3'附近使用的正确语法

不含函数的查询可以正常运行。

问题原因与解决方案 #

问题核心是**function()是JPAQL的语法，并非原生MySQL SQL的语法**。因为你设置了nativeQuery = true，SQL会直接发送给MySQL执行，必须严格遵循MySQL的函数调用规范，直接去掉function()包裹即可：

@Query(value = "SELECT DATE_FORMAT(c.valid_to, '%Y-%m-%d') " +
        "FROM card c WHERE c.card_id= :card_id", nativeQuery = true)

补充说明 #

• 当使用JPA默认的JPQL查询（nativeQuery = false）时，才需要用function('date_format', ...)这种方式，让JPA将其转换为对应数据库的原生函数调用
• 开启nativeQuery = true后，所有SQL语法必须匹配目标数据库的规范，直接使用数据库原生函数即可

内容的提问来源于stack exchange，提问作者SealAnia