Blog
Blog copied to clipboard
Published
20 hours ago •
codcodog
codcodog
关于Mysql日期合法问题
关于Mysql日期合法问题
在Mysql数据库, 设置了一个date属性来存储日期.
在查询日期的时候, 如下:
> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-31';
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 190 | HSI INDEX | -0.77 | 2017-02-28 |
| 191 | HSI INDEX | -0.17 | 2017-02-27 |
| 192 | HSI INDEX | -0.62 | 2017-02-24 |
| 193 | HSI INDEX | -0.36 | 2017-02-23 |
| 194 | HSI INDEX | 0.99 | 2017-02-22 |
| 195 | HSI INDEX | -0.76 | 2017-02-21 |
| 196 | HSI INDEX | 0.47 | 2017-02-20 |
| 197 | HSI INDEX | -0.31 | 2017-02-17 |
| 198 | HSI INDEX | 0.47 | 2017-02-16 |
| 199 | HSI INDEX | 1.23 | 2017-02-15 |
| 200 | HSI INDEX | -0.03 | 2017-02-14 |
| 201 | HSI INDEX | 0.58 | 2017-02-13 |
| 202 | HSI INDEX | 0.21 | 2017-02-10 |
| 203 | HSI INDEX | 0.17 | 2017-02-09 |
| 204 | HSI INDEX | 0.66 | 2017-02-08 |
| 205 | HSI INDEX | -0.07 | 2017-02-07 |
| 206 | HSI INDEX | 0.95 | 2017-02-06 |
| 207 | HSI INDEX | -0.24 | 2017-02-03 |
| 208 | HSI INDEX | -0.57 | 2017-02-02 |
| 209 | HSI INDEX | -0.18 | 2017-02-01 |
+------+-----------+---------+------------+
注意这里, 使用了 02-31, 但是可以看到, 数据是可以正常出来的, 并没有发生什么报错和警告.
因为, 在 Mysql:
为了使日期检查更“快”，MySQL仅检查月份在0-12的范围，天在0-31的范围。
~~而且,Mysql允许你存储某个“不严格的”合法日期值, 例如: 2017-02-31~~
但是, 如果超出了非法值的话, Mysql 会自动转化为 0 值. 例如:
> UPDATE `stock_test`.`cal_index_price_daily` SET `date`='2017-02-32' WHERE `id`=190;
> select * from `stock_test`.`cal_index_price_daily` where `id`=190;
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 190 | HSI INDEX | -0.77 | 0000-00-00 |
+------+-----------+---------+------------+
> UPDATE `stock_test`.`cal_index_price_daily` SET `date`='2017-02-31' WHERE `id`=190;
> select * from `stock_test`.`cal_index_price_daily` where `id`=190;
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 190 | HSI INDEX | -0.77 | 0000-00-00 |
+------+-----------+---------+------------+
> UPDATE `stock_test`.`cal_index_price_daily` SET `date`='2017-02-29' WHERE `id`=190;
> select * from `stock_test`.`cal_index_price_daily` where `id`=190;
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 190 | HSI INDEX | -0.77 | 0000-00-00 |
+------+-----------+---------+------------+
> UPDATE `stock_test`.`cal_index_price_daily` SET `date`='2017-02-28' WHERE `id`=190;
> select * from `stock_test`.`cal_index_price_daily` where `id`=190;
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 190 | HSI INDEX | -0.77 | 2017-02-28 |
+------+-----------+---------+------------+
通过以上例子, 可以看出, Mysql 不允许存储非法的日期, 并且会把非法日期转化为 0.
但是要注意, 查询的时候, 还是可以查询一些非法的日期的, 但是要符合 月份在0-12的范围，天在0-31的范围 (这应该算是 Mysql 的一个 hack 吧), 例如:
> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-28';
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 1 | HSI INDEX | -0.35 | 2017-02-28 |
| 190 | HSI INDEX | -0.77 | 2017-02-28 |
| 191 | HSI INDEX | -0.17 | 2017-02-27 |
| 192 | HSI INDEX | -0.62 | 2017-02-24 |
| 193 | HSI INDEX | -0.36 | 2017-02-23 |
| 194 | HSI INDEX | 0.99 | 2017-02-22 |
| 195 | HSI INDEX | -0.76 | 2017-02-21 |
| 196 | HSI INDEX | 0.47 | 2017-02-20 |
| 197 | HSI INDEX | -0.31 | 2017-02-17 |
| 198 | HSI INDEX | 0.47 | 2017-02-16 |
| 199 | HSI INDEX | 1.23 | 2017-02-15 |
| 200 | HSI INDEX | -0.03 | 2017-02-14 |
| 201 | HSI INDEX | 0.58 | 2017-02-13 |
| 202 | HSI INDEX | 0.21 | 2017-02-10 |
| 203 | HSI INDEX | 0.17 | 2017-02-09 |
| 204 | HSI INDEX | 0.66 | 2017-02-08 |
| 205 | HSI INDEX | -0.07 | 2017-02-07 |
| 206 | HSI INDEX | 0.95 | 2017-02-06 |
| 207 | HSI INDEX | -0.24 | 2017-02-03 |
| 208 | HSI INDEX | -0.57 | 2017-02-02 |
| 209 | HSI INDEX | -0.18 | 2017-02-01 |
+------+-----------+---------+------------+
> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-31';
+------+-----------+---------+------------+
| id | code | price | date |
|------+-----------+---------+------------|
| 1 | HSI INDEX | -0.35 | 2017-02-28 |
| 190 | HSI INDEX | -0.77 | 2017-02-28 |
| 191 | HSI INDEX | -0.17 | 2017-02-27 |
| 192 | HSI INDEX | -0.62 | 2017-02-24 |
| 193 | HSI INDEX | -0.36 | 2017-02-23 |
| 194 | HSI INDEX | 0.99 | 2017-02-22 |
| 195 | HSI INDEX | -0.76 | 2017-02-21 |
| 196 | HSI INDEX | 0.47 | 2017-02-20 |
| 197 | HSI INDEX | -0.31 | 2017-02-17 |
| 198 | HSI INDEX | 0.47 | 2017-02-16 |
| 199 | HSI INDEX | 1.23 | 2017-02-15 |
| 200 | HSI INDEX | -0.03 | 2017-02-14 |
| 201 | HSI INDEX | 0.58 | 2017-02-13 |
| 202 | HSI INDEX | 0.21 | 2017-02-10 |
| 203 | HSI INDEX | 0.17 | 2017-02-09 |
| 204 | HSI INDEX | 0.66 | 2017-02-08 |
| 205 | HSI INDEX | -0.07 | 2017-02-07 |
| 206 | HSI INDEX | 0.95 | 2017-02-06 |
| 207 | HSI INDEX | -0.24 | 2017-02-03 |
| 208 | HSI INDEX | -0.57 | 2017-02-02 |
| 209 | HSI INDEX | -0.18 | 2017-02-01 |
+------+-----------+---------+------------+
> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-32';
+------+--------+---------+--------+
| id | code | price | date |
|------+--------+---------+--------|
+------+--------+---------+--------+
0 rows in set
