实际上,即使是
documentation的澄清,您也可以在MySQL的1000年以前存储日期:
mysql> describe test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
– 您仍然需要以YYYY格式输入年份:
mysql> insert into test values (1, '0995-03-05');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+------+------------+
| id | birth |
+------+------------+
| 1 | 0995-03-05 |
+------+------------+
1 row in set (0.00 sec)
– 您将能够以此作为日期操作:
mysql> select birth + interval 5 day from test;
+------------------------+
| birth + interval 5 day |
+------------------------+
| 0995-03-10 |
+------------------------+
1 row in set (0.03 sec)
至于安全。我从来没有遇到这样的情况,当这不能在MySQL 5.x(这是因为,并不意味着它将100%工作,但至少它是可靠的一定的概率)
关于BC日期(在基督之下)。我认为这很简单 – 在MySQL中没有办法存储负日期。即您将需要将年份分开存储为有符号整数字段:
mysql> select '0001-05-04' - interval 1 year as above_bc, '0001-05-04' - interval 2 year as below_bc;
+------------+----------+
| above_bc | below_bc |
+------------+----------+
| 0000-05-04 | NULL |
+------------+----------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1441 | Datetime function: datetime field overflow |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
但是我认为,在任何情况下(0/0以下),最好在这种情况下将日期部分存储为整数 – 这不会依赖于未记录的功能。但是,您将需要使用这3个字段作为日期(所以在某种意义上说,这不是您的问题的解决方案)