mysql动态顺序

Question

我有一个相当复杂的MySQL查询，其中三个工会最终需要进行分类和分页。用例是一个带有多列数据的网页。用户可以一次单击列的标题，然后浏览结果，一次选择一个列进行排序。第二键单击标头将切换排序顺序。我的策略是通过该字段($ sortfield(来指定字段和标志($ sortorder(，以指定上升或降序订单。基于我的网络研究，我将其与下面的语句一起使用，但是当我添加更多排序选项并担心此解决方案最终不会扩展时，我会达到sort_buffer限制。有人建议更好的方法吗？似乎应该有一个相当直接的解决方案，但我似乎找不到。

ORDER BY 
CASE WHEN $sortField = 'fo_cd' AND $sortOrder = -1 THEN fo_cd END DESC,
CASE WHEN $sortField = 'fo_cd' and $sortOrder = 1 THEN fo_cd END,
CASE WHEN $sortField = 'fm_name' AND $sortOrder = -1 THEN fm_name END DESC,
CASE WHEN $sortField = 'fm_name' and $sortOrder = 1 THEN fm_name END,
CASE WHEN $sortField = 'fo_ld' AND $sortOrder = -1 THEN fo_ld END DESC,
CASE WHEN $sortField = 'fo_ld' and $sortOrder = 1 THEN fo_ld END,
CASE WHEN $sortField = 'us_creator' AND $sortOrder = -1 THEN us_creator END DESC,
CASE WHEN $sortField = 'us_creator' and $sortOrder = 1 THEN us_creator END,
CASE WHEN $sortField = 'us_assignedTo' AND $sortOrder = -1 THEN us_assignedto END DESC,
CASE WHEN $sortField = 'us_assignedTo' and $sortOrder = 1 THEN us_assignedto END,
CASE WHEN $sortField = 'fo_currentStatus' AND $sortOrder = -1 THEN fo_CurrentStatus END DESC,
CASE WHEN $sortField = 'fo_currentStatus' and $sortOrder = 1 THEN fo_CurrentStatus END,
CASE WHEN $sortField = 'fd_name1' AND $sortOrder = -1 THEN fd_name1 END DESC,
CASE WHEN $sortField = 'fd_name1' and $sortOrder = 1 THEN fd_name1 END,
CASE WHEN $sortField = 'fd_name2' AND $sortOrder = -1 THEN fd_name2 END DESC,
CASE WHEN $sortField = 'fd_name2' and $sortOrder = 1 THEN fd_name2 END,
CASE WHEN $sortField = 'fd_name3' AND $sortOrder = -1 THEN fd_name3 END DESC,
CASE WHEN $sortField = 'fd_name3' and $sortOrder = 1 THEN fd_name3 END,
CASE WHEN $sortField = 'fd_name4' AND $sortOrder = -1 THEN fd_name4 END DESC,
CASE WHEN $sortField = 'fd_name4' and $sortOrder = 1 THEN fd_name4 END

Answer 1

寻找您的代码

做一个事实，您在代码中使用var。您可以简单地使用dinamic sql(但是请注意，请注意var含量的适当消毒，以避免使用SQLINPOCTion风险(

"  ORDER BY " . $sortField . " CASE WHEN $sortOrder = -1  THEN 'DESC' ELSE ASC 'END' ";  

或

$asc_desc =  ($sortOrder == -1) ? 'DESC' : ASC 
"  ORDER BY " . $sortField . " " .  $asc_desc;