存储过程中的倒叙查询兑现不了么？desc

存储过程中的倒叙查询实现不了么？？desc
看代码

SQL code


drop procedure if exists query_news;
delimiter $$ 
create procedure query_news(IN page_size int,IN page_num int,IN order_key varchar(30),IN order_flag int,OUT num int)
begin
    DECLARE start_pos int;
    DECLARE seq varchar(5);
    DECLARE ps int;
    DECLARE ok varchar(30);
    
    set @seq=order_flag;
    set @ps=page_size;
    set @ok=order_key;    
    set num=(select count(id) from news);
    set @start_pos=page_size*(page_num-1);
    
    if @seq=0 then
    PREPARE STMT FROM 'select * from news  order by ?  desc limit ?,?;'; 
    end if;
    
    if @seq=1 then 
        PREPARE STMT FROM 'select* from news order by ? asc limit ?,?;'; 
    end if;
    
    EXECUTE STMT USING @ok,@start_pos,@ps;
end
$$
delimiter ;

1.这是一个分页的查询存储过程
2.用变量标识排序列和分页参数
3.磁存储过程的int类型的参数1表示升序，0表示降序
4.调用 call query_news(10,1,'id',1,@out);
5.问题是无论第四个int类型的参数怎么变都只能升序排列
6.sql语句经过测试可以显示降序
7.news的建表语句如下

SQL code


create table news
(
   id                   bigint not null auto_increment,
   catagory             char(3) not null,
   pub_time             datetime not null,
   publisher_id         tinyint not null,
   title                char(50) not null,
   content              text,
   primary key (id)
);

------解决方案--------------------
set @asql=concat('select * from news order by ',@ok,' desc limit ',@start_pos,',',@ps);
PREPARE STMT FROM @asql;
EXECUTE STMT;

存储过程中的倒叙查询兑现不了么？desc

相关推荐