MySQL数据类型仅存储月份和年份？
您需要将日期存储为完整的日期时间，而不是仅存储月份和年份。如果声明为日期时间，则可以使用MySQL中的MONTH()和YEAR()函数提取月份和年份。
语法如下-
select MONTH(yourDateTimeColumnName) as anyVariableName1, YEAR(yourDateTimeColumnName) as anyVariableName2 from yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table OnlyMonthandYear
-> (
-> DueDateTime datetime
-> );使用insert命令在表中插入日期。插入记录的查询如下-
mysql> insert into OnlyMonthandYear values('2016-12-10');
mysql> insert into OnlyMonthandYear values('2017-10-21');
mysql> insert into OnlyMonthandYear values('2018-03-25');
mysql> insert into OnlyMonthandYear values('2018-12-18');使用select命令显示表中的所有记录。查询如下-
mysql> select *from OnlyMonthandYear;
+---------------------+ | DueDateTime | +---------------------+ | 2016-12-10 00:00:00 | | 2017-10-21 00:00:00 | | 2018-03-25 00:00:00 | | 2018-12-18 00:00:00 | +---------------------+ 4 rows in set (0.00 sec)
这是仅从datetime数据类型列中提取月份和年份的查询。
查询如下-
mysql> select Month(DueDateTime) as OnlyMonth,Year(DueDateTime) as OnlyYear from OnlyMonthandYear;
输出结果
+-----------+----------+ | OnlyMonth | OnlyYear | +-----------+----------+ | 12 | 2016 | | 10 | 2017 | | 3 | 2018 | | 12 | 2018 | +-----------+----------+ 4 rows in set (0.02 sec)