如何使'datetime'列正确排序?
问题描述:
我有一个专栏,时间是'DATETIME'。我想按时间顺序输出,但是当我使用ORDER BY create_time DESC
时,'2011-09-10 16:16:04'在'2011-07-16 03:20:01'后面。
如何使'datetime'列正确排序?
似乎它将datatime列当作字符串处理。
如何在不更改列类型的情况下处理此问题?
UPDATE:
这是我的查询的一部分:输出
SELECT 'bookcomment' AS type
,b.book_id
,b.name
,c.book_id
,c.author_id
,c.content
,c.create_time as create_time
,u.id
,u.name
FROM tbl_book AS b,
tbl_book_comment AS c,
tbl_user AS u
WHERE u.id=c.author_id in (1,2) AND b.book_id=c.book_id
UNION ALL
SELECT 'bookreview' AS type
,b.book_id
,b.name
,re.book_id
,re.author_id
,re.content
,re.create_time as create_time
,u.id
,u.name
FROM tbl_book AS b,
tbl_book_review AS re,
tbl_user AS u
WHERE u.id=re.author_id in (1,2) AND b.book_id=re.book_id
UNION ALL
...
ORDER BY create_time DESC
而且部分:
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "Whatever" ["author_id"]=> string(4) "test" ["content"]=> string(19) "2011-07-16 03:20:01" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(13) "sdfwefwaefwea" ["content"]=> string(19) "2011-05-11 03:33:33" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(5) "test0" ["content"]=> string(19) "2011-09-10 16:16:04" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(5) "test1" ["content"]=> string(19) "2011-09-10 16:16:04" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(5) "test2" ["content"]=> string(19) "2011-09-10 16:16:04" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(5) "test3" ["content"]=> string(19) "2011-09-10 16:16:04" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
array(7) { ["type"]=> string(16) "new post comment" ["book_id"]=> string(1) "1" ["name"]=> string(9) "whatever" ["author_id"]=> string(5) "test4" ["content"]=> string(19) "2011-09-10 16:16:04" ["create_time"]=> string(1) "3" ["id"]=> string(1) "1" }
答
您似乎有错误的列中的数据。在您的输出示例中,create_time
对于它们都是3
,所以订单未定义。仔细检查数据是否在正确的列?
+0
现在我发现每一列都不对应其数据...怎么了! – LotusH
+0
发生在我身上的一次......在800万行的桌子上,不下于! –
您为什么不想将列类型更改为日期时间?这显然是数据。 –
我假设你有datetime列的数据类型为datetime? –
即使将该列视为字符串,顺序也是相同的。 “2011-09”>“2011-07” –