mysql 合法日期_关于Mysql日期合法问题

最新推荐文章于 2023-03-29 14:52:36 发布

喵在乎

最新推荐文章于 2023-03-29 14:52:36 发布

阅读量718

点赞数

文章标签： mysql 合法日期

本文链接：https://blog.csdn.net/weixin_36253081/article/details/113214220

版权

关于Mysql日期合法问题

在Mysql数据库, 设置了一个date属性来存储日期.

在查询日期的时候, 如下:

> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-31';

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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;

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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;

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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;

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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;

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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';

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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';

+------+-----------+---------+------------+

|------+-----------+---------+------------|

| 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';

+------+--------+---------+--------+

|------+--------+---------+--------|

+------+--------+---------+--------+

0 rows in set

喵在乎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 合法日期_关于Mysql日期合法问题

关于Mysql日期合法问题在Mysql数据库, 设置了一个date属性来存储日期.在查询日期的时候, 如下:> select * from stock_test.cal_index_price_daily where `date` >= '2017-02-01' and `date` <= '2017-02-31';+------+-----------+---------+--...
复制链接

扫一扫