1、问题:
搭建了一个新的项目,启动时报了个数据库有关的异常:
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_101]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_101]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_101]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_101]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:132) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2241) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2265) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1319) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:966) ~[mysql-connector-java-8.0.15.jar:8.0.15]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825) ~[mysql-connector-java-8.0.15.jar:8.0.15]
... 88 common frames omitted
2、解决:
根据阅读这个异常信息,可以知道:server time zone value (服务器时区的值)不是一个或者无法识别,需要给它指定一个时区。(mysql8.0及以上需要考虑时区问题)
所以直接yml配置文件里的url这里加上时区,这里其实随便指定一个时区就可以了,但是避免项目后面出现sql时间对不上的问题,还是将它设置为东八区的北京时区,有几种写法。yml配置文件里:
2.1、&serverTimezone=Hongkong
2.2 &serverTimezone=GMT%2B8 注意:这里不能直接写GMT+8 需要转义一下
3、拓展
注意我上面配置文件 url 那栏里面有个 &zeroDateTimeBehavior=convertToNull 配置,简单说一下作用:
JAVA连接MySQL数据库时,在操作值为0的timestamp类型时不能正确的处理,会默认抛出一个异常:java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP。
&zeroDateTimeBehavior 就是用来处理这种情况的,它有3个三个属性值:
3.1exception:默认值,即抛出SQL state [S1009]. Cannot convert value....的异常;
3.2convertToNull:将日期转换成NULL值;
3.3round:替换成最近的日期即0001-01-01;
关于mysql时区方面的知识参见:https://blog.csdn.net/dafei93/article/details/83307396#GMTGreenwich_Mean_Time_179