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和装载时存在潜在的影响。