首页 > 代码库 > MySQL中的YEAR函数
MySQL中的YEAR函数
一般这样认为 YEAR类型占用1个字节，并且在定义时可以指定显示的宽度为YEAR(4)或者YEAR(2)，不过从MySQL5.6.6开始，YEAR(2)类型被自动装换为YEAR(4)，YEAR(2)类型被禁用。
（一）YEAR(2)类型被自动装换为YEAR(4)
mysql>drop table if exists t;
Query OK,0 rows affected (0.01 sec)
mysql>create table t(a year(2));
Query OK,0 rows affected, 1 warning (0.00 sec)
mysql>show warnings\G;
***************************1. row ***************************
Level: Warning
Code: 1818
Message:YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
1 row inset (0.00 sec)
ERROR:
No queryspecified
mysql>explain t;
+-------+---------+------+-----+---------+-------+
| Field |Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | year(4) | YES | |NULL | |
+-------+---------+------+-----+---------+-------+
1 row inset (0.00 sec)
mysql>insert into t select ‘14‘;
Query OK,1 row affected (0.00 sec)
Records:1 Duplicates: 0 Warnings: 0
mysql>select * from t;
+------+
| a |
+------+
| 2014 |
+------+
1 row inset (0.00 sec)
mysql>
（二）存在当前表中的YEAR(2)类型仍旧作为YEAR(2)存在和处理，但是下面几种方式会自动转换为YEAR(4):
（1）alter表
alter table语句与导致重建表：
mysql> alter table t modify a year(2);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> explain t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> alter table t modify a year(2);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 1818 | YEAR(2) column type is deprecated. Creating YEAR(4) column instead. |
+---------+------+---------------------------------------------------------------------+
1 row in set (0.01 sec)
（2）PEAIR TABLE
如果check table时，数据库发现一张表中包含YEAR(2)列，就推荐使用YEAR(4)。
（3）mysql_upgrade
这个用于REPAIR TABLE的情况。
（4）在dump文件和重新装载dump文件时，被影响的数据值都会在dump和装载时存在潜在的影响。
MySQL中的YEAR函数