MySQL TIMESTAMP bug that occurs when index type is indexed

Database: MySQL, Version: 5.1.45

Query Statement 1:

Select ID, Settlement_begin_time, settlement_end_time from Mkt_vendor_settlement_brief where settlement_begin_time >= ' 2017-09-01 00:00:00.0 ' and Settlement_end_time <= ' 2017-09-30 23:59:59.0 ';

Results:

Query Statement 2:

Select ID, Settlement_begin_time, settlement_end_time from Mkt_vendor_settlement_brief where settlement_begin_time >= ' 2017-09-01 00:00:00 ' and Settlement_end_time <= ' 2017-09-30 23:59:59.0 '

Results:

Add index to Settlement_begin_time, the difference of query statement: one has ". 0" one without ". 0"

". 0" can also be queried normally after the index is deleted.

After investigation, this is a MySQL bug, in the subsequent version has been repaired, I use mysql5.5 to test everything normal.

When this problem occurs, the solution:

1. Upgrade MySQL version, the most effective

2. Curve Salvation, Java code, the timestamp type of data is pre-formatted, and then put a string into the query statement, because the MYSQL-JDBC call "PreparedStatement" Settimestamp method will be added ". 0"

MySQL TIMESTAMP bug that occurs when index type is indexed