我试图使用以下查询进行排序:
SELECT * FROM client
ORDER BY
CASE $desired_colum_to_order
WHEN 'id' then `id`
WHEN 'name' then `name`
ELSE `id`
END
ASC问题是,此查询将ID排序为字符串列,但它是一个整数主键。
结果依次为: 10,11,12,13,5,6,7,8,9
如果我执行以下查询,则MySQL命令是正确的:
SELECT * FROM client
ORDER BY
id
ASC现在,结果是正确的: 5,6,7,8,9,10,11,12,13。
如果两个查询中的列相同,那么谁能解释为什么MySQL排序是这样的呢?
提前感谢!
发布于 2014-07-17 14:27:15
问:谁能给我解释一下为什么MySQL是这样订货的?
MySQL是根据ORDER子句中表达式的结果“排序”行。
您在ORDER子句中提供了一个CASE表达式。这个表达式返回一个数据类型。在这种情况下,表达式似乎是根据VARCHAR,列的数据类型返回一个name。
优化器“不够聪明”,无法计算出CASE关键字后面的字符串文本'id'总是与第一个WHEN后面的字符串文本'id'匹配。你看到了,但优化器没有。
读取该表达式,您将看到第一个WHEN将与每一行匹配,表达式始终返回id,并且该表达式将永远不会返回name。
您认为这等同于指定ORDER BY id。
但是优化器不是这样看待它的。它将name视为一个可能的返回值,并对所有可能的返回值进行“工作”的数据类型设置。在本例中,它似乎确定VARCHAR是要返回的适当数据类型。
如果从CASE表达式返回的数据类型是整数,则行将按升序数值排序。
但是MySQL仍然不能按照顺序使用索引来返回行,它仍然将执行“使用文件短”操作,因为行仍然是根据表达式的结果而不是列排序的。
发布于 2014-07-17 14:07:54
您可以将order by拆分为不同的条件:
SELECT *
FROM client
ORDER BY (CASE WHEN $desired_colum_to_order = 'id' then id END) ASC,
(CASE WHEN $desired_colum_to_order = 'name' then name END) ASC,
id ASC这是因为不匹配所有返回NULL的NULL语句没有顺序。如果没有匹配,那么最终的id将是使用的。不同case语句的优点是每一列都是“类型”安全的。此外,您还可以修改逻辑,使一些DESC而不是ASC。
发布于 2014-07-17 14:22:29
问题是CASE表达式回避字符串,因为该数据类型是MySQL发现的最常见的类型。
一个选项是将case语句拆分为所有可用的列,如下所示:
ORDER BY
CASE $orderColumn = 'id' THEN id ELSE 0 END
, CASE $orderColumn = 'name' THEN name ELSE '' END另一个选项是将数字字段设为零,如下所示:
ORDER BY
CASE $desired_colum_to_order
WHEN 'id' then LPAD(CONVERT(`id`,VARCHAR(20)),20,'0')
WHEN 'name' then `name`
ELSE `id`
END或者(如果可用的话,我认为这是最好的选择):在应用程序中构建查询以动态添加所需的ORDER BY子句
https://stackoverflow.com/questions/24805776
复制相似问题