mysql - 如何按 ASC 对 var char time 列进行排序？

Question

我有一个名为“order_details_child”的表，其描述如下所示

mysql> desc order_details_child;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| vendor_id        | varchar(50) | YES  |     | NULL    |       |
| item_id          | varchar(20) | YES  |     | NULL    |       |
| date_of_order    | varchar(10) | YES  |     | NULL    |       |
| time_of_order    | varchar(10) | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

我有一个要求，我需要根据time_of_order以升序顺序检索记录？

order by time_of_order asc ?? (First ordered should be shown first)

这是示例记录

+----------------------+-----------+---------+---------------+---------------+--------+
| order_child_id       | vendor_id | item_id | date_of_order | time_of_order | status |
+----------------------+-----------+---------+---------------+---------------+--------+
| 1410070300301030O1O1 | 1         | 5       | 2014-10-07    | 15:10:30      | NEW    |
+----------------------+-----------+---------+---------------+---------------+--------+
1 row in set (0.00 sec)

有人可以帮助我吗？

Answer 1

ORDER BY STR_TO_DATE(time_of_order, '%T') ASC

%T = 24hr hh:mm:ss - 您指的是时间还是也指日期？

参见http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format