如何在 Spring Boot 配置中设置正确的 MySQL JDBC 时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/53993181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set correct MySQL JDBC timezone in Spring Boot configuration
提问by Michael Coxon
DB:
D B：
$ mysql --version
mysql Ver 14.14 Distrib 5.6.27, for osx10.10 (x86_64) using EditLine wrapper
Spring Boot: 2.1.1.RELEASE
Spring Boot：2.1.1.RELEASE
The error:
错误：
2019-01-01 15:56:25.849 ERROR 39957 --- [ restartedMain] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Exception during pool initialization.
> :bootRun
java.sql.SQLException: The server time zone value 'AEDT' 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.
Relevant parts of my properties file:
我的属性文件的相关部分：
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&serverTimezone=UTC
spring.datasource.username=#####
spring.datasource.password=########
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
What I find odd about this is that the error indicates that the timezone being used it AEDT, and yet I specified UTC in the spring.datasource.url. Does Hikari read something else when it initializes?
我对此感到奇怪的是，该错误表明正在使用它的时区是 AEDT，但我在spring.datasource.url. Hikari 在初始化时是否读取其他内容？
It does look very much like Hikari ignores the server timezone setting in the database url in favour of using my own machine's timezone which happens to be 'AEDT' (Melbourne, Australia) - This is unwanted behaviour. I would like Hikari to ignore my own machine's timezone. Does anyone know how to make it do that?
它看起来很像 Hikari 忽略了数据库 url 中的服务器时区设置，转而使用我自己机器的时区，它恰好是“AEDT”（澳大利亚墨尔本）——这是不受欢迎的行为。我希望 Hikari 忽略我自己机器的时区。有谁知道如何让它做到这一点？
采纳答案by Michael Coxon
Thanks for your answers, but I have found the solution.
感谢您的回答，但我已经找到了解决方案。
As I suspected, Hikari ignores whatever you put in the datasource url (so sorry guys, it doesn't matter what you chuck in there), essentially, it reads the timezone setting from MySQL itself, i.e., whatever the result you see when issuing the command
正如我所怀疑的那样，Hikari 会忽略您在数据源 url 中放入的任何内容（对不起，伙计们，您在那里放什么并不重要），本质上，它从 MySQL 本身读取时区设置，即，无论您在发出时看到什么结果命令
SELECT @@GLOBAL.time_zone;
SELECT @@GLOBAL.time_zone;
in MySQL. In my case, the result was "SYSTEM", which is whatever my local machine it set at. This was AEDT, which is not supported by the MySQL driver and hence my exception.
在 MySQL 中。在我的情况下，结果是“SYSTEM”，这是它设置的本地机器。这是 AEDT，它不受 MySQL 驱动程序支持，因此是我的例外。
Running this same query in AWS yielded the value "UTC", which is supported (and, actually what I wanted).
在 AWS 中运行相同的查询产生了值“UTC”，这是受支持的（实际上是我想要的）。
Therefore, I had to set the timezone in my local MySQL server.
因此，我必须在本地 MySQL 服务器中设置时区。
Firstly, I had to load the available timezones from my host (Mac OS X) into MySQL. I had to find out where the zoneinfo file was (/usr/share/zoneinfo in my case) then find out out where the `mysql_tzinfo_to_sql' utility was (bin directory of the MySQL installation) and use it to load my local machine's supported timezones. In Mac OS X, I ended up running the command:
首先，我必须将主机（Mac OS X）中的可用时区加载到 MySQL 中。我必须找出 zoneinfo 文件的位置（在我的情况下为 /usr/share/zoneinfo），然后找出“mysql_tzinfo_to_sql”实用程序的位置（MySQL 安装的 bin 目录）并使用它来加载本地机器支持的时区. 在 Mac OS X 中，我最终运行了以下命令：
/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Then in MySQL I could run the command
然后在 MySQL 中我可以运行命令
SET GLOBAL time_zone = UTC;
SET GLOBAL time_zone = UTC;
this isa valid timezone, and is synchronized with the cloud based instances.
这是一个有效的时区，并与基于云的实例同步。
I think this is a real trap for a lot of people using MySQL with Spring Boot. It will work while people are in supported timezones, but if your development machine should switch to an unsupported timezone, it will quite mysteriously break, I'm surprised that it isn't documented anywhere. The source code of the MySQL Connector/J makes it obvious, but you wouldn't know it otherwise.
我认为这对于很多在 Spring Boot 中使用 MySQL 的人来说是一个真正的陷阱。当人们处于受支持的时区时它会工作，但是如果您的开发机器应该切换到不受支持的时区，它会非常神秘地中断，我很惊讶它没有在任何地方记录。MySQL Connector/J 的源代码很明显，但你不会知道它。
Maybe its because MySQL is just so 5 years ago, and I'm an old fossil and, and, well, just get off my lawn!
也许是因为 MySQL 是 5 年前的事，而我是一个古老的化石，而且，好吧，离开我的草坪！
回答by GolamMazid Sajib
Set useLegacyDatetimeCodefalse and set ServerTimezone.
设置useLegacyDatetimeCodefalse 并设置 ServerTimezone。
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false
回答by Shaun Dashjian
This worked for me. I set the variable in the db URL as such in application.properties:
这对我有用。我在 db URL 中设置变量，如下所示application.properties：
spring.datasource.url=jdbc:mysql://localhost:3306/db_name?serverTimezone=America/Los_Angeles
回答by M. Lopez
I added the following variable in the db url under application.properties:
我在 db url 下添加了以下变量application.properties：
spring.datasource.url=jdbc:mysql://localhost:3306/db_name?serverTimezone=GMT-6
and Spring Boot now runs ok.
现在 Spring Boot 运行正常。
回答by Dimitri Flores
Following Shaun it was enough for me with this: spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/DEMOS?serverTimezone=UTC
跟随肖恩这对我来说就足够了： spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/DEMOS? 服务器时区=UTC
回答by Numery
Set useLegacyDatetimeCode false is working for me. Thanks..
设置 useLegacyDatetimeCode false 对我有用。谢谢..
spring.datasource.url: jdbc:mysql://localhost:3306/employee_directory?
useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false
回答by Marsh Rangel
Another way is to put into the file application.propertiesthe next line:
另一种方法是将application.properties下一行放入文件中：
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
It comes as an option in: https://www.baeldung.com/mysql-jdbc-timezone-spring-boot
它作为一个选项出现在：https: //www.baeldung.com/mysql-jdbc-timezone-spring-boot
回答by lordgasmic
Using jdk 14, I had to do this to my application.properties
使用 jdk 14，我不得不对我的 application.properties
spring.jpa.properties.hibername.jdbc.time_zone=US/Michigan
edit: formatting
编辑：格式化
回答by user7294900
Add useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=falseto your connection string:
添加useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false到您的连接字符串：
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
