mysql WHERE BETWEEN by date 返回错误结果？

Question

我有一个如下所示的表格:

match_name   match_formatted_date
A vs B       28.10.2014
b vs c       26.10.2014
c vs d       18.10.2014
d vs e       30.09.2014
e vs f       27.09.2014
g vs h       19.09.2014
h vs j       28.08.2014
x vs y       26.08.2014
y vs z       07.08.2014

mysql:

SELECT `m`.* FROM `match` `m` 
WHERE (m.match_formatted_date BETWEEN "24.10.2014" AND "31.10.2014")  
ORDER BY `m`.`match_id` DESC

但是 mysql 返回值是这样的:

A vs B       28.10.2014
b vs c       26.10.2014
d vs e       30.09.2014
e vs f       27.09.2014
h vs j       28.08.2014
x vs y       26.08.2014

我只想得到这个:

A vs B       28.10.2014
b vs c       26.10.2014

注意:match_formatted_date 是 varchar，而不是 datetime。到底有没有问题？

Answer 1

您需要将列和搜索字符串都转换为日期类型:

SELECT `m`.* `match` `m` 
WHERE (str_to_date(m.match_formatted_date, '%d.%m.%Y') BETWEEN str_to_date("24.10.2014", '%d.%m.%Y') AND str_to_date("31.10.2014", '%d.%m.%Y'))  
ORDER BY `m`.`match_id` DESC