MySQL 带有 order by 子句的准备语句

Question

我在 MySQL 中使用带有“order by”条件子句的准备好的语句。使用 '？'并且变量不适用于诸如排序之类的事情，因此我决定以其他方式进行处理。我设置了 if 条件，但代码要多得多。也许还有其他选项可以减少代码并仅更改“order by”参数？

IF sorting_column_index = 1 and sorting_column_mode = 0
        THEN PREPARE STMT FROM 'SELECT a.oid as \'oid\', 
        ...

        FROM table as a

        ...
        order by numero_annee desc LIMIT ?, ?';
        EXECUTE STMT USING @skip, @ROWS;
END IF;

IF sorting_column_index = 2 and sorting_column_mode = 1
        THEN PREPARE STMT FROM 'SELECT a.oid as \'oid\', 
        ...

        FROM table as a

        ...
        order by numero_ordre asc LIMIT ?, ?';
        EXECUTE STMT USING @skip, @ROWS;
END IF;
...

Answer 1

尝试这样的操作，不使用 asc/desc 参数，而是构建查询字符串:

SET @sort_order = 'desc';
SET @my_limit = 5;
SET @sql = CONCAT('SELECT whatever FROM whatever ORDER BY col1 ', @sort_order, ' LIMIT ?;');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @my_limit;
DEALLOCATE PREPARE stmt;