MySQL 动态 ORDER BY

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 ..您可以简单地使用动态 sql(但要注意对 var 内容进行适当的清理，以避免 SQLinjection 风险)

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

或

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