用于分页的MySQL存储过程

 

CALL `dbpassport`.`get_page`('表名','字段名列表（多字段用逗号隔开）',当前页号码,每页记录数,'排序字段（多字段用逗号隔开）','筛选条件（没有则留空）')

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
    /**//*Table name*/
    tableName varchar(100),
    /**//*Fileds to display*/
    fieldsNames varchar(100),
    /**//*Page index*/
    pageIndex int,
    /**//*Page Size*/
    pageSize int,
    /**//*Field to sort*/
    sortName varchar(500),
    /**//*Condition*/
    strWhere varchar(500)
  )
BEGIN
    DECLARE fieldlist varchar(200);
    if fieldsNames=''||fieldsNames=null THEN
        set fieldlist='*';
    else
        set fieldlist=fieldsNames;
    end if;

    if strWhere=''||strWhere=null then
        if sortName=''||sortName=null then
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        else
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        end if;
    else
        if sortName=''||sortName=null then
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        else
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        end if;
    end if;
    PREPARE stmt1 FROM @strSQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END

 

在Mysql上的例子说明一下PREPARE的功能!

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+