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
©2019-2020 Toolsou All rights reserved,
【C++ Must see for entry 】C++ from 0 reach 1 Introductory programming axios Interceptor packaging and use Spring Boot Interview must ask : Automatic configuration principle VMware 16 install centos 7 Detailed tutorial C Language data structure - Sequence table delete duplicates V2.0.0 The 12th Blue Bridge Cup c++b Group personal problem solving On sending data from serial port single chip microcomputer to upper computer centos7 install RabbitMqjava Polymorphic array of opencv-python Fourier transform and inverse transform