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函數