I was working on my personal project when I needed a field that would store a year input. In the past, I always used DATE or DATETIME datatypes for date-related input. But for this project, all I needed was to store the year. So I did my research, and in this article, I will answer which datatype is best for year input in MySQL.
-
The YEAR Datatype
In MySQL, there is a datatype specifically for the year. This is called the YEAR datatype. The YEAR datatype only uses 1-byte storage space. However, it only accepts years from 1901 to 2155. So if the years you expect will be in the range of 1901 to 2155, then you should use the YEAR datatype.
The syntax for year is YEAR(4), where 4 is the character width. It can also be declared with just YEAR equivalent to YEAR(4).
-
The DATE Datatype is another option
We can always use the DATE datatype. However, it will be a waste of space since we will only need to use the year part. But it is still worth noting that DATE can also be an option if we can't use other datatypes.
-
How about VARCHAR?
We can also use VARCHAR, but just like DATE, it is also a waste of space. Plus, if we use VARCHAR, there is a chance that we get a false input.
-
Finally, The SMALLINT
Since a year is just a number, the best datatype would be a datatype that only accepts a number. For that, we should use SMALLINT.
-
But why not TINYINT?
The reason we don't use the TINYINT is because it is too small. The maximum unsigned value of TINYINT is 255.
Conclusion
If you expect the year input to be around 1901 to 2155, then use YEAR. Otherwise, use SMALLINT.
