select * FROM your_table where date_format(FROM_UNIXTIME(tm),'%Y')>'1983';
select * FROM your_table where YEAR(FROM_UNIXTIME(tm))>'1983';
select * from table where tm > makedate(2006,1) 这样索引可以起作用
我也不知道,makedate是什么函数。
mysql 4.1.1 起有makedate: MAKEDATE(year,dayofyear) Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL. mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); -> '2001-01-31', '2001-02-01' mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365); -> '2001-12-31', '2004-12-30' mysql> SELECT MAKEDATE(2001,0); -> NULLMAKEDATE() is available as of MySQL 4.1.1.
这样索引可以起作用
MAKEDATE(year,dayofyear) Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL. mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
-> '2001-01-31', '2001-02-01'
mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
-> '2001-12-31', '2004-12-30'
mysql> SELECT MAKEDATE(2001,0);
-> NULLMAKEDATE() is available as of MySQL 4.1.1.