MySQL报错：MySQL server version for the right syntax to use near ‘nulls last‘ at line 1

配置order_by.default_null_ordering: last后报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘nulls last’ at line 1
2023-07-31 17:32:15.460 [http-nio-8180-exec-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:142) - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘nulls last’ at line 1

记录下出这个问题的原因

我们公司用的ORM框架是Hibenrnate，数据库主要用的是Postgre SQL，也关联的有个MySQL。（涉及使用MySQL数据源查询的需求很少）
因为有些日期为空的特殊数据，同事在yml配置文件里加入了下面这个配置：

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect # 指定了使用的数据库方言，这里是PostgreSQL数据库的方言。
        order_by.default_null_ordering: last # last 是一个Hibernate属性，它指定了在排序时如何处理空值。在这种情况下，空值将被放在排序结果的最后。

因为涉及使用MySQL数据源查询的需求很少，上线的时候测试同事也没测到，上线两天后，运营那边反馈新建XXX报系统异常。我查看生产日志发现：

13:26:13 [http-nio-8180-exec-10] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
13:26:13 [http-nio-8180-exec-10] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nulls last' at line 1
13:26:13 [http-nio-8180-exec-10] ERROR c.ccm.amarth.GlobalExceptionHandler - 运行时异常
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy250.findAll(Unknown Source)
	# 可以看到错误代码在这里（这里我改下包路径和方法名，涉及到源码担心泄露）
	at com.demo.test.service.UserServiceImpl.findFirstByUserIdOrderByDate(UserServiceImpl.java:117)

因为代码不是我写的，而且这段逻辑长时间都没有动过。起初也没往yml配置那里想，因为代码也不是我加的，当时我初步判断应该是数据库的问题，以为是数据库里这条记录有空日期，所以查询报错了。
发现数据没有问题后，我在本地配置了SQL打印日志：

logging.level.org.hibernate.SQL: DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder: TRACE

查看执行SQL日志：

13:26:13 [http-nio-8180-exec-10] DEBUG org.hibernate.SQL - select enterprise0_.id as id1_2_, enterprise0_.name as nam6_2_ from users enterprise0_ where enterprise0_.org_data_id=? order by enterprise0_.date desc nulls last
13:26:13 [http-nio-8180-exec-10] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [45642005]

# 发现后尾巴 竟然拼接了个
order by enterprise0_.date desc nulls last

排查到是yml配置的问题后，我把这段代码注释掉后：

spring:
  jpa:
    open-in-view: true # 在view层打开数据库session
#     properties:
#       hibernate:
#         dialect: org.hibernate.dialect.PostgreSQLDialect # 指定了使用的数据库方言，这里是PostgreSQL数据库的方言。
#         order_by.default_null_ordering: last # last 是一个Hibernate属性，它指定了在排序时如何处理空值。在这种情况下，空值将被放在排序结果的最后。

发现程序可以正常进行了。

总结：

这个错误是由于在MySQL数据库中使用了不支持的语法导致的。具体来说，order_by.default_null_ordering: last 这个属性是针对PostgreSQL数据库的，用于指定在排序时如何处理空值。然而，MySQL数据库不支持这个语法。
我查了一下：MySQL 8.0版本及以上支持使用NULLS LAST语法来处理空值的排序。

因为是生产环境，公司不可能会因为这个bug而升级数据库版本，所以只能自行解决。

解决方案：

因为项目使用的是 JPA，所以涉及到查询的方法采用 Sort.Order.nullsLast()方法将空值放到排序结果的最后。
代码示例：

String sysUserId = sysUser.getId();
PageRequest page = PageRequest.of(dto.getPage(), dto.getSize());
Specification<CourseEntity> specification =  (root, criteriaQuery, criteriaBuilder) -> {
    var list = new ArrayList<Predicate>();
    if(StrUtil.isNotBlank(userId)){
        list.add(criteriaBuilder.equal(root.get("userId"),userId));
    }
    // 将createDate = null 的数据，设置个默认最小时间，然后倒叙排列
    Expression<LocalDateTime> beginTimeExpression = root.get("createDate");
    Expression<LocalDateTime> nullLiteral = criteriaBuilder.literal(LocalDateTime.of(1999, 1,1,23,59, 12));
    Order orderByBeginTime = criteriaBuilder.desc(criteriaBuilder.selectCase()
            .when(criteriaBuilder.isNull(beginTimeExpression), nullLiteral)
            .otherwise(beginTimeExpression));
    criteriaQuery.orderBy(orderByBeginTime);
    Predicate[] predicates = new Predicate[list.size()];
    return criteriaBuilder.and(list.toArray(predicates));
};