mysql - 为什么我的日期排序不正确？

Question

不知道有没有人遇到过这个问题。
我有一个字符串转换为日期并按升序排序。日期按数字排序，但不按月份排序。我想知道是否有人遇到过这个问题，并且可以就如何正确排序日期发表一些见解。

SELECT
  u.url_id, 
  url, 
  title, 
  description, 
  pub_date, 
  DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y') AS pub_date,
  pub_date AS sortdate 
FROM 
  urls AS u, 
  url_associations AS ua 
WHERE
  u.url_id = ua.url_id 
  AND ua.url_category_id=$type
  AND ua.approved = 'Y'
ORDER BY
  sortdate DESC

上面是代码，它可以工作，但日期没有排序，例如它排序如下:

 29-may-2009
 28-may-2009
 27-may-2009
 02-june-2009
 01-june-2009

Answer 1

好吧，我之前有点懵。您的原始查询有些困惑，因为您在列列表中选择 pub_date，然后也转换为 pub_date。但是，您随后按 pub_date 列排序(实际上，给定 sortdate as pub_date)- 这似乎是一个字符串列。

您的排序应该在列之后转换为日期，但之前转换为字符串:

SELECT
  u.url_id, 
  url, 
  title, 
  description, 
  pub_date,
  STR_TO_DATE(pub_date, '%d-%b-%Y') AS sortdate,
  DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y') AS formatted_date
FROM 
  urls AS u, 
  url_associations AS ua 
WHERE
  u.url_id = ua.url_id 
  AND ua.url_category_id=$type
  AND ua.approved = 'Y'
ORDER BY
  sortdate DESC

请注意，我已将“格式化”版本重命名为 formatted_date。不清楚您是否仍需要选择原始的 pub_date。 formatted_date 位可能是:

DATE_FORMAT(sortdate, '%d.%b.%Y') AS formatted_date

但我不完全确定。我希望查询优化器无论如何都能解决这个问题。

pub_date 列真的必须是字符串吗？为什么不将其作为数据库中更合适的类型一开始就保留下来，以避免所有解析？