mysql分页存储过程.docVIP

mysql分页存储过程 鎬绘槸涔犳儻浜唌ssql涓婇潰鐨勫瓨鍌ㄨ繃绋嬪垎椤?鐒跺悗缁撳悎AspNetPager鍒嗛〉鎺т欢(/AspNetPager/default.aspx),杩欐牱灏卞畬鎴愪簡璁板綍鐨勫垎椤垫樉绀猴紱 鐢变簬鏈€杩戠敤鍒癿ysql,鐢变簬mssql,mysql涓よ€呯殑琛ㄨ揪璇硶鍜岃娉曠害鏉熶笂鐨勫尯鍒紝瀵艰嚧鍐欒捣mysql涓婄殑鍒嗛〉鐗堟湰鏈夌偣鈥滃鏉傗€濄€傜粡杩囨煡鎵句竴鐣祫鏂欏悗缁堜簬鎶婂畠鈥滃鍒垛€濅簡銆? 瀛樺偍杩囩▼鍒嗛〉鐨勫熀鏈師鐞?鎴戜滑鍏堝鏌ユ壘鍒扮殑璁板綍闆?鏀寔杈撳叆鏌ユ壘鏉′欢_WhereClause鍜屾帓鍒楁潯浠禵OrderBy)鐨刱ey瀛楁涓存椂瀛樻斁鍒颁复鏃惰〃,鐒跺悗鏋勫缓鐪熸鐨勮褰曢泦杈撳嚭銆? CREATE PROCEDURE `mysqltestuser_SELECT_PageAble`( _WhereClause VARCHAR(2000), -- 鏌ユ壘鏉′欢 _OrderBy VARCHAR(2000), -- 鎺掑簭鏉′欢 _PageSize INT , -- 姣忛〉璁板綍鏁? _PageIndex INT , -- 褰撳墠椤电爜 _DoCount BIT -- 鏍囧織:缁熻鏁版嵁/杈撳嚭鏁版嵁 ) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT BEGIN -- 瀹氫箟key瀛楁涓存椂琛? DROP TABLE IF EXISTS _TempTable_KeyID; -- 鍒犻櫎涓存椂琛?濡傛灉瀛樺湪 CREATE TEMPORARY TABLE _TempTable_KeyID ( userid INT )TYPE=HEAP; -- 鏋勫缓鍔ㄦ€佺殑sql锛岃緭鍑哄叧閿瓧key鐨刬d闆嗗悎 -- 鏌ユ壘鏉′欢 SET @sql = SELECT userid FROM mysqltestuser ; IF (_WhereClause is NOT NULL) AND (_WhereClause ) THEN SET @sql= concat(@sql, WHERE ,_WhereClause); END if; IF (_OrderBy is NOT NULL) AND (_OrderBy ) THEN SET @sql= concat( @sql , ORDER BY , _OrderBy); END IF; -- 鍑嗗id璁板綍鎻掑叆鍒颁复鏃惰〃 set @sql=concat( insert into _TempTable_KeyID(userid) , @sql); PREPARE stmt FROM @sql; EXECUTE stmt ; DEALLOCATE PREPARE stmt; -- key鐨刬d闆嗗悎 [end] -- 涓嬮潰鏄緭鍑? IF (_DoCount=1) then -- 缁熻 BEGIN SELECT COUNT(*) AS RecordCount FROM _TempTable_KeyID; END; ELSE -- 杈撳嚭璁板綍闆? BEGIN -- 璁＄畻璁板綍鐨勮捣鐐逛綅缃? SET @startPoint = ifnull((_PageIndex-1)*_PageSize,0); SET @sql= SELECT A.* FROM mysqltestu