Let's talk about a simple topic today , This is what a little partner asked me on wechat , For beginners, I can understand the troubles caused by such problems , Various attempts , Various searches , What others say is right , But it just can't solve its own problems , Today, I will talk to you about this problem from two aspects , If the partners have other solutions , You can also leave a message to share .
We can analyze this problem from two aspects ：
* MySQL Its own problems .
* Java Code problems .
<>1. MySQL Own problem
MySQL Own problem , This is actually very easy to verify , Isn't that time , Our implementation is as follows SQL have a look MySQL Is the time on the computer consistent with my computer time ：
select now();
Can see ,MySQL This time is actually different from the time of my system 8 hour ,MySQL The time itself is wrong , Then you'll insert it in the future / The query time must be wrong .
Attention to this query , Or use the command line , Either use Sqlyog,Navicat perhaps Sequel Pro And other database tools , Do not use JDBC
To query , The specific reason will be clear after reading the second section .
This problem occurs , Mostly MySQL Your time zone is not quite right , Let's reset the time zone .
First, let's take a look through the following instructions MySQL Current time zone ：
show variables like '%time_zone%';
Can see ,MySQL Say its time zone is SYSTEM, that SYSTEM What is it ? The first one says SYSTEM yes
UTC（ Coordinated universal time , Also known as world standard time or world coordinated time ）. And our Beijing time is better than UTC soon 8 hour , Namely UTC+8.
So now we're going to MySQL Change your time zone first , This can be achieved by modifying the configuration file （/etc/mysql/mysql.conf.d/mysqld.cnf）, as follows ：
After modification , restart MySQL, Check again MySQL Time zone ：
Can see , At this time MySQL The time zone is normal .
Then execute again at this time select now(); There will be no problem ：
Some small partners may find it too troublesome to modify the configuration file , Then you can also pass SQL To modify the time zone ：
set global time_zone = Asia/Shanghai
Note that our time zone is Asia/Shanghai, Little friends, don't play freely and write about other cities .
First, we need to confirm MySQL no problem .
<>2. JDBC Connection problem
When confirmed MySQL After no problem , If your MySQL The time is still wrong , Then it could be JDBC Connection problem .
Here I use the common JdbcTemplate Let's take an example , The operation of other database frameworks is the same , I'm here mainly to demonstrate the time zone problem , The details of data operation are no longer shown .
First, let's prepare a table , as follows ：
CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `createTime` datetime
DEFAULT NULL, `updateTime` timestamp NULL DEFAULT NULL, `username` varchar(255)
DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Very simple fields ,createTime yes datetime type ,updateTime yes Timestamp type .
Then add a record to the table ：
And the time zone of this database is Asia/Shanghai
Next, we create a Spring Boot project , introduce Web,JDBC API Dependence and MySQL drive , as follows ：
Then let's configure it MySQL Connection information for , as follows ：
spring.datasource.username=root spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///test01?serverTimezone=UTC
Guys, take a look , In the database connection address , I specially set the time zone as UTC, This time zone is slower than our current time zone 8
hour , Let's look at using such a wrong time zone , What is the result of the operation .
@Autowired JdbcTemplate jdbcTemplate; @Test void contextLoads() { List<User>
list= jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.
class)); System.out.println("list = " + list); }
You see , The time when this query result was found is 21 spot , Follow 13 It's faster than that 8 hour .
Why ?
Because we added serverTimezone=UTC parameter , At this time , The system will treat the data queried from the database as UTC Time zone , Immediately 13 Point as UTC
Time zone , But what is my current device Asia/Shanghai time zone ,UTC Time zone 13 Point conversion Asia/Shanghai After the time zone 21 Yes .
Same reason , You can also try to set it yourself serverTimezone=Asia/Tokyo, Set time zone to Tokyo , Tokyo is an hour ahead of us , Tokyo 13 The point is ours 12
spot , Then the final query result is 12 spot .
From this case, we can see ,jdbc The time zone in the connection parameters takes precedence over MySQL Time zone parameters for the server , Therefore, you should pay special attention to this connection parameter .
<>3. Digression
The time zone problem encountered by some small partners is another kind , return JSON At the wrong time .
If used in the project jackson, And use @JsonFormat Annotation to format the date , There may be a time zone problem , as follows ：
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
You see , If this code is not set timezone attribute , Then the default time zone is UTC, It will also lead to the final time difference 8 hour .
<>4. Summary
All right , These are several cases of the database summarized by SongGe , If you have any additional information, please leave a message for discussion .
Technology
Daily Recommendation