MySQL shell中奇怪的时间戳输出

Question

mysql-sql> select time_stamp, unix_timestamp(time_stamp) from historical_data 
where time_stamp > '2014-07-31' and time_stamp < '2014-10-05';
+---------------------+----------------------------+
| time_stamp          | unix_timestamp(time_stamp) |
+---------------------+----------------------------+
| 2014-07-31 19:04:00 |                 1406826240 |
| 2014-00-00 0:00:00  |                 1406913840 |
| 2014-00-00 0:00:00  |                 1407002040 |
| 2014-00-00 0:00:00  |                 1407088440 |
| 2014-00-00 0:00:00  |                 1407174840 |

我有一张带有时间戳列的桌子。我注意到，当我从mysql查询表时，它会显示很多日期为2014-00-000:00:00。但是，当我在查询中转换为unix时间戳时，数据似乎被正确保存了。就好像它只是输出格式一样.(时间戳也是主键的一部分)有问题。

这种情况发生在2014年8月1日至2014年10月1日，然后恢复正常产出。

不确定以下内容是否相关，但由于我不知道是什么原因造成了这种情况:我有一个使用gson处理来自web.Amongst的json数据的java应用程序--数据是unix时间戳。它们被转换为即时对象，然后使用MyBatis将其写入数据库。

现在，当从数据库中读取数据时，一切似乎都正常，我得到了正确的即时对象。它只是在mysql外壳中，这似乎是一个问题。但这很奇怪也很不舒服。

下面是MySQL shell中从头到尾重现的问题：

mysql-sql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.00 sec)
mysql-sql> create table test(`time_stamp` TIMESTAMP NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)
mysql-sql> insert into test (time_stamp) values ('2014-07-31');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-08-15');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-09-15');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-10-01');
Query OK, 1 row affected (0.00 sec)
mysql-sql> select * from test;
+--------------------+
| time_stamp         |
+--------------------+
| 2014-07-31 0:00:00 |
| 2014-00-00 0:00:00 |
| 2014-00-00 0:00:00 |
| 2014-10-01 0:00:00 |
+--------------------+
4 rows in set (0.00 sec)
mysql-sql> select time_stamp, unix_timestamp(time_stamp) from test;
+--------------------+----------------------------+
| time_stamp         | unix_timestamp(time_stamp) |
+--------------------+----------------------------+
| 2014-07-31 0:00:00 |                 1406757600 |
| 2014-00-00 0:00:00 |                 1408053600 |
| 2014-00-00 0:00:00 |                 1410732000 |
| 2014-10-01 0:00:00 |                 1412114400 |
+--------------------+----------------------------+
4 rows in set (0.00 sec)

所以至少我知道这不是应用层的问题。

Answer 1

虽然我无法重现这个问题，但显然MySQL Shell中似乎存在一个bug (请参阅INSERT中的日期和SELECT中的结果)：

Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in SQL mode. Use \js or \py to switch the shell to a scripting language.
mysql-sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql-sql> DROP TABLE IF EXISTS `_`.`historical_data`;
Query OK, 0 rows affected (0.03 sec)

mysql-sql> CREATE TABLE IF NOT EXISTS `_`.`historical_data` (
       ...   `time_stamp` TIMESTAMP NOT NULL PRIMARY KEY
       ... );
Query OK, 0 rows affected (0.00 sec)

mysql-sql> INSERT INTO `_`.`historical_data`
       ...   (`time_stamp`)
       ... VALUES
       ...   ('2014-07-31 19:04:00'),
       ...   ('2014-07-31 17:04:00'),
       ...   ('2014-08-01 17:24:00'),
       ...   ('2014-08-02 17:54:00'),
       ...   ('2014-08-03 17:54:00'),
       ...   ('2014-08-04 17:54:00'); 
Query OK, 6 rows affected (0.00 sec)

mysql-sql> SELECT
       ...   `time_stamp`
       ... FROM
       ...   `_`.`historical_data`;
+---------------------+
| time_stamp          |
+---------------------+
| 2014-08-31 17:04:00 |
| 2014-08-31 19:04:00 |
| 2014-09-01 17:24:00 |
| 2014-09-02 17:54:00 |
| 2014-09-03 17:54:00 |
| 2014-09-04 17:54:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql-sql> SELECT
       ...   `der`.`time_stamp`,
       ...   `der`.`ut`,
       ...   FROM_UNIXTIME(`der`.`ut`)
       ... FROM (
       ...   SELECT
       ...     `time_stamp`,
       ...     UNIX_TIMESTAMP(`time_stamp`) `ut`
       ...   FROM
       ...     `_`.`historical_data`
       ...   WHERE
       ...     `time_stamp` > '2014-07-31' AND
       ...     `time_stamp` < '2014-10-05'
       ... ) `der`;
+---------------------+------------+---------------------------+
| time_stamp          | ut         | FROM_UNIXTIME(`der`.`ut`) |
+---------------------+------------+---------------------------+
| 2014-08-31 17:04:00 | 1406826240 | 2014-08-31 17:04:00       |
| 2014-08-31 19:04:00 | 1406833440 | 2014-08-31 19:04:00       |
| 2014-09-01 17:24:00 | 1406913840 | 2014-09-01 17:24:00       |
| 2014-09-02 17:54:00 | 1407002040 | 2014-09-02 17:54:00       |
| 2014-09-03 17:54:00 | 1407088440 | 2014-09-03 17:54:00       |
| 2014-09-04 17:54:00 | 1407174840 | 2014-09-04 17:54:00       |
+---------------------+------------+---------------------------+
6 rows in set (0.00 sec)

MySQL Shell

mysql-sql> SELECT FROM_UNIXTIME('1406826240'),
       ...        FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S');
+-----------------------------+--------------------------------------------------+
| FROM_UNIXTIME('1406826240') | FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S') |
+-----------------------------+--------------------------------------------------+
| 2014-08-31 17:04:00         | 2014-07-31 17:04:00                              |
+-----------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

MySQL命令-Line

mysql> SELECT FROM_UNIXTIME('1406826240'),
    ->        FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S');
+-----------------------------+--------------------------------------------------+
| FROM_UNIXTIME('1406826240') | FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S') |
+-----------------------------+--------------------------------------------------+
| 2014-07-31 17:04:00.000000  | 2014-07-31 17:04:00                              |
+-----------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

一个可能的解决办法，直到漏洞解决为止：

MySQL Shell

mysql-sql> SELECT
       ...   CONCAT(`time_stamp`) `time_stamp`
       ... FROM
       ...   `_`.`historical_data`;
+---------------------+
| time_stamp          |
+---------------------+
| 2014-07-31 17:04:00 |
| 2014-07-31 19:04:00 |
| 2014-08-01 17:24:00 |
| 2014-08-02 17:54:00 |
| 2014-08-03 17:54:00 |
| 2014-08-04 17:54:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql-sql> SELECT
       ...   `der`.`time_stamp`,
       ...   `der`.`ut`,
       ...   CONCAT(FROM_UNIXTIME(`der`.`ut`))
       ... FROM (
       ...   SELECT
       ...     CONCAT(`time_stamp`) `time_stamp`,
       ...     UNIX_TIMESTAMP(`time_stamp`) `ut`
       ...   FROM
       ...     `_`.`historical_data`
       ...   WHERE
       ...     `time_stamp` > '2014-07-31' AND
       ...     `time_stamp` < '2014-10-05'
       ... ) `der`;
+---------------------+------------+-----------------------------------+
| time_stamp          | ut         | CONCAT(FROM_UNIXTIME(`der`.`ut`)) |
+---------------------+------------+-----------------------------------+
| 2014-07-31 17:04:00 | 1406826240 | 2014-07-31 17:04:00               |
| 2014-07-31 19:04:00 | 1406833440 | 2014-07-31 19:04:00               |
| 2014-08-01 17:24:00 | 1406913840 | 2014-08-01 17:24:00               |
| 2014-08-02 17:54:00 | 1407002040 | 2014-08-02 17:54:00               |
| 2014-08-03 17:54:00 | 1407088440 | 2014-08-03 17:54:00               |
| 2014-08-04 17:54:00 | 1407174840 | 2014-08-04 17:54:00               |
+---------------------+------------+-----------------------------------+
6 rows in set (0.01 sec)