I have a table in MySQL 4.0 which currently has a year field as a smallint(6) type. What will happen if I convert it directly to a Year type with a query like the following:
ALTER TABLE t MODIFY y YEAR(4) NOT NULL DEFAULT CURRENT_TIMESTAMP;
When the current members of column y have va开发者_StackOverflow社区lues like 2010? I assume that because the year type is technically values from 1-255, values above that will be truncated or broken.
So if MySQL isn't smart enough to realize that 2010(int) = 110(year), what would be the simplest query or queries to convert the values?
Thanks for your help!
From MySQL Docs: 10.3.3 The year
type
For four-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000. For two-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).
For years out of that range:
Illegal YEAR values are converted to 0000.
A quick test
alter table foo add b year(4) not null default current_timestamp;
update foo set b=a;
a b
1900 0000
1901 1901
1950 1950
2000 2000
2002 2002
2121 2121
2155 2155
2156 0000
精彩评论