The year function in MySQL

It is generally assumed that the year type occupies 1 bytes, and you can specify that the display width is year (4) or year (2) when defined,but starting with MySQL5.6.6 , year (2) type is automatically swapped for year (4) and year (2) type is disabled.

(i) year (2) type is automatically replaced as 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>

(ii) the Year (2) type that exists in the current table is still present and processed as year (2), but the following methods are automatically converted to year (4):

(1) ALTER TABLE

ALTER TABLE statements with the resulting rebuild tables:

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

If the database finds a table that contains the year (2) column when the check table is found, it is recommended that you make use of this (4).

(3) Mysql_upgrade

This is used for repair table cases.

(4) When dump files and reload dump files, the affected data values will have a potential impact on dump and load.

The year function in MySQL