MySQL5.7: Paging using Mysql Stored Proc

-- 查询外键 涂聚文 (Geovin Du)
select
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

-- 查询外键    
select
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'geovindu';
    
--  table_name  查询表和视图
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu';
-- 表    
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu' and table_type='base table';

-- 视图
SELECT * FROM information_schema.tables
    WHERE table_schema = 'geovindu' and table_type='VIEW';
 -- 列   
 SELECT * FROM information_schema.COLUMNS;
 
 -- 主外键
 SELECT * FROM information_schema.KEY_COLUMN_USAGE;
 
 SELECT * FROM information_schema.PARAMETERS;
 
 -- 存储过程，自定义函数
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu';
-- 'PROCEDURE'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu' and routine_type='PROCEDURE';
select * from information_schema.ROUTINES where  ROUTINE_SCHEMA='geovindu' and routine_type='PROCEDURE';

--  'FUNCTION'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu'  and routine_type='FUNCTION';
select * from information_schema.ROUTINES where  ROUTINE_SCHEMA='geovindu' and routine_type='FUNCTION';


  
SELECT * FROM information_schema.PROCESSLIST;
  
 --
 SELECT * FROM information_schema.SCHEMATA;
 
 -- 表，视图
 SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'geovindu';
  
    
    
-- 主键
select * from information_schema.KEY_COLUMN_USAGE;

-- https://dev.mysql.com/doc/refman/8.0/en/keywords-table.html
select * from information_schema.KEYWORDS;

SELECT * FROM INFORMATION_SCHEMA.KEYWORDS;

select
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;
    
select `column_name`, `column_type`, `column_default`, `column_comment`
from `information_schema`.`COLUMNS` 
where `table_name` = 'customerlist' 
and `table_schema` = 'geovindu';

select *
from `information_schema`.`COLUMNS` 
where `table_name` = 'customerlist' 
and `table_schema` = 'geovindu';   
    
select *
from `information_schema`.`COLUMNS` 
where `table_schema` = 'geovindu';   
-- column_key  PRI,MUL，UNI  pri 主键，mul 外键
-- EXTRA  auto increment 自动增长
-- DATA_TYPE 数据类型

-- 外键表与主表关系
SELECT 
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`,                           -- Foreign key column
  `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema
  `REFERENCED_TABLE_NAME`,                 -- Origin key table
  `REFERENCED_COLUMN_NAME`                 -- Origin key column
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE 
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys
 
 --  
 SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_SCHEMA = 'geovindu'  AND REFERENCED_TABLE_NAME IS NOT NULL; 
  

  
--   
SELECT 
    count(1) totalrelationships ,
    c.table_name tablename,
    CONCAT(' ',GROUP_CONCAT(c.column_name ORDER BY ordinal_position SEPARATOR ', ')) columnname,
    CONCAT(' ',GROUP_CONCAT(c.column_type ORDER BY ordinal_position SEPARATOR ', ')) columntype    
FROM
    information_schema.columns c RIGHT JOIN
    (SELECT column_name , column_type FROM information_schema.columns WHERE 
    -- column_key in ('PRI','MUL') AND  -- uncomment this line if you want to see relations only with indexes
    table_schema = DATABASE() AND table_name = 'productitorderdetails') AS p
    USING (column_name,column_type)
WHERE
    c.table_schema = DATABASE()
    -- AND c.table_name != 'YourTableName'
    GROUP BY tablename
    -- HAVING (locate(' YourColumnName',columnname) > 0) -- uncomment this line to search for specific column 
    ORDER BY totalrelationships desc, columnname
;

-- 
SELECT i.TABLE_SCHEMA, i.TABLE_NAME, 
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, 
       k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
  FROM information_schema.TABLE_CONSTRAINTS i 
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
       ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 WHERE i.TABLE_SCHEMA = 'productitorderdetails' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
 ORDER BY i.TABLE_NAME;
 
 --  
 select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'geovindu';
    
 SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'geovindu'
AND REFERENCED_TABLE_NAME = 'productitorderdetails';

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
FROM information_schema.TABLE_CONSTRAINTS i 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
and i.table_schema = 'geovindu';
 
 SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
FROM information_schema.TABLE_CONSTRAINTS i 
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND i.TABLE_SCHEMA = DATABASE()
AND i.TABLE_NAME = 'productitorderdetails';
 
 
 SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS;


SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'geovindu' AND
  REFERENCED_TABLE_NAME = 'productitorderdetails';
  
  SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'geovindu' AND
  TABLE_NAME = 'productitorderdetails';
  
  
  
select * 
 from information_schema.TABLES where TABLE_SCHEMA='geovindu';
 
  
  
  -- 主键  
 select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL' 
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI'; 
 
 -- 主键 ，有注释
  select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT,b.TABLE_COMMENT 
 from information_schema.columns as a,information_schema.TABLES as b 
 where  a.table_schema='geovindu' and b.table_schema='geovindu' and column_key='PRI'
 and a.table_name=b.table_name;
  
  
  -- 外键 
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL' 
 from information_schema.columns where  table_schema='geovindu' and column_key='MUL';  
 
  SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE  TABLE_SCHEMA = 'geovindu'  AND REFERENCED_TABLE_NAME IS NOT NULL; 
 
 
 select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',  b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
 from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.TABLE_NAME=b.TABLE_NAME and a.table_schema='geovindu' and b.table_schema='geovindu' and a.column_key='MUL'
  AND b.REFERENCED_TABLE_NAME IS NOT NULL; 
  
  -- 自表外键  列有注释
select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT,b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
 from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.table_name=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.table_schema='geovindu' and b.table_schema='geovindu' 
 and a.column_key='MUL'
AND b.REFERENCED_TABLE_NAME IS NOT NULL and a.table_name='productorderdetails'; 
  
 -- 主表的主键作的外键表 列有注释
select a.table_name as 'TableName',a.column_name as 'FieldName',a.data_type as 'TypeName',ifnull(a.character_maximum_length,8) as 'Length',a.is_nullable as 'IS_NULL',a.COLUMN_COMMENT, b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME
from information_schema.columns as a,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b  where a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.table_schema='geovindu' and b.table_schema='geovindu' and a.column_key='MUL'
AND b.REFERENCED_TABLE_NAME IS NOT NULL and b.REFERENCED_TABLE_NAME='unitlist'; 
  
 
 -- 表
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL' 
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI' and table_name=('orderdetails'); 
 
 -- 表
 select column_name as 'FieldName',data_type as 'FieldType',ifnull(character_maximum_length,8) as 'FieldLength' from information_schema.columns where table_schema='geovindu' and table_name=('orderdetails');
 
 --  表,列表有注释
 select a.column_name as 'FieldName',a.data_type as 'FieldType',ifnull(a.character_maximum_length,8) as 'FieldLength',a.COLUMN_COMMENT,b.TABLE_COMMENT from information_schema.columns as a,information_schema.TABLES as b 
 where a.table_schema='geovindu' and b.table_schema='geovindu' and a.TABLE_NAME=b.TABLE_NAME  and a.table_name=('orderdetails');
 
 
 
 
   select * 
 from information_schema.columns where  table_schema='geovindu' and column_key='PRI' and table_name=('orderdetails'); 
 
  -- UNI
  select table_name as 'TableName',column_name as 'FieldName',data_type as 'TypeName',ifnull(character_maximum_length,8) as 'Length',is_nullable as 'IS_NULL' 
 from information_schema.columns where  table_schema='geovindu' and column_key='UNI';  
 
 -- 查表的描述
 
select  TABLE_COMMENT  from information_schema.TABLES where  table_schema='geovindu' and table_name=('orderdetails'); 
 
 select a.column_name as 'FieldName',a.data_type as 'FieldType',ifnull(a.character_maximum_length,8) as 'FieldLength',a.COLUMN_COMMENT,b.TABLE_COMMENT from information_schema.columns as a,information_schema.TABLES as b  where a.table_schema='geovindu' and b.table_schema='geovindu' and a.TABLE_NAME=b.TABLE_NAME  and a.table_name=('enterprisetype');
 

-- MySQL5.7 2018-09-28 
-- Geovin Du 涂聚文 edit

#查询函数，存储过程
SELECT * FROM mysql.proc WHERE db='geovindu';

SELECT * FROM information_schema.routines WHERE routine_schema='geovindu';

SHOW PROCEDURE STATUS WHERE db='geovindu';

#查看存储过程详细信息
SHOW CREATE PROCEDURE geovindu.DeleteBookKind;


 -- 存储过程，自定义函数
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu';
-- 'PROCEDURE'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu' and routine_type='PROCEDURE';
select * from information_schema.ROUTINES where  ROUTINE_SCHEMA='geovindu' and routine_type='PROCEDURE';

--  'FUNCTION'
SELECT * FROM information_schema.PARAMETERS where Specific_schema='geovindu'  and routine_type='FUNCTION';
select * from information_schema.ROUTINES where  ROUTINE_SCHEMA='geovindu' and routine_type='FUNCTION';



DROP PROCEDURE IF EXISTS `sp_splitpage`;

-- ok
 DELIMITER $$
CREATE PROCEDURE `sp_splitpage`(
 in _pagecurrent int,/*当前页*/
 in _pagesize int,/*每页的记录数*/
 in _ifelse varchar(1000),/*显示字段*/
 in _where varchar(1000),/*条件*/
 in _order varchar(1000) /*排序*/
)
COMMENT '分页存储过程'
BEGIN
declare strsql varchar(1000);
 if _pagesize<=1 then 
  set _pagesize=20;
end if;
 if _pagecurrent < 1 then 
  set _pagecurrent = 1;
end if;
 set @strsql = concat('select ',_ifelse,' from ',_where,' ',_order,' limit ',_pagecurrent*_pagesize-_pagesize,',',_pagesize); 
 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql; 
 set @strsqlcount=concat('select count(1) as count from ',_where);/*count(1) 这个字段最好是主键*/
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
END$$
DELIMITER ;



/*
--名称：MYSQL版查询分页存储过程 by peace 2013-8-14
--输入参数：@fields        -- 要查询的字段用逗号隔开
--输入参数：@tables        -- 要查询的表
--输入参数：@where        -- 查询条件
--输入参数：@orderby    -- 排序字段
--输出参数：@page        -- 当前页计数从1开始
--输出参数：@pagesize    -- 每页大小
--输出参数：@totalcount -- 总记录数
--输出参数：@pagecount  -- 总页数 
*/
-- ok
DROP PROCEDURE IF EXISTS `Query_Pagination`; 

 DELIMITER $$ 
CREATE PROCEDURE Query_Pagination
(
    in _fields varchar(2000),   
    in _tables text, 
    in _where varchar(2000),  
    in _orderby varchar(200),
    in _pageindex int,
    in _pagesize int,
    in _sumfields  varchar(200),/*增加统计字段2013-5-8 peaceli*/
    out _totalcount int ,
    out _pagecount int 
)COMMENT '分页存储过程'
begin
declare startRow int;
declare pageSize int;
declare rowindex int;
declare strsql varchar(1000);

   set startRow = _pagesize*(_pageindex-1);
   set pageSize = _pagesize;  
   set rowindex = 0;
     set strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',_fields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end,' order by ',_orderby,' limit ',@startRow,',',@pageSize);
     prepare strsql from @strsql;
     execute strsql;
   deallocate prepare strsql;
   set _totalcount = found_rows(); 

   if(_totalcount <= _pagesize) then
		set _pagecount = 1;
   else if(_totalcount % _pagesize > 0) then
		set _pagecount = _totalcount / _pagesize + 1;
   else
        set _pagecount = _totalcount / _pagesize;
   end if;

if(ifnull(_sumfields,'') <> '') then 
set @sumsql = contact('select ',_sumfields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end); 
prepare sumsql from @sumsql; 
execute sumsql; 
deallocate prepare sumsql; 
end if;
end if; 
end$$
DELIMITER ;





 
/*test"
CALL sp_viewPage( 
'*'#查询字段 
,'userupdatelog'#表名 
,'1=1'#条件 
,'Id desc'#排序 
,1 #页码 
,20 #每页记录数 
,@totalcount #输出总记录数 
,@pagecount #输出用页数 
); 
SELECT @totalcount,@pagecount; 
*/
DROP PROCEDURE IF EXISTS `sp_viewPage`;
-- OK
 DELIMITER $$
CREATE PROCEDURE sp_viewPage(
_fields VARCHAR(1000), #要查询的字段，用逗号(,)分隔 
_tables TEXT, #要查询的表 
_where VARCHAR(2000), #查询条件 
_orderby VARCHAR(200), #排序规则 
_pageindex INT, #查询页码 
_pageSize INT, #每页记录数 
/*_sumfields VARCHAR(200),#求和字段 */
#输出参数 
OUT _totalcount INT, #总记录数 
OUT _pagecount INT #总页数 
/* OUT _sumResult VARCHAR(2000)#求和结果 */
)COMMENT '分页存储过程'
BEGIN
#140529-xxj-分页存储过程 
#计算起始行号
declare strsql varchar(1000);
declare startRow int;
declare pageSize int;
declare rowindex int;
SET startRow = _pageSize * (_pageindex - 1); 
SET pageSize = _pageSize; 
SET rowindex = 0; #行号 

#合并字符串 
SET @strsql = CONCAT( 
#'select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,' #记录行号 
'select sql_calc_found_rows ' 
,_fields 
,' from ' 
,_tables 
,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END 
,CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END 
,' limit ' 
,startRow 
,',' 
,pageSize 
); 

PREPARE strsql FROM @strsql;#定义预处理语句 
EXECUTE strsql; #执行预处理语句 
DEALLOCATE PREPARE strsql; #删除定义 
#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数 
SET _totalcount = FOUND_ROWS(); 
#计算总页数 
IF (_totalcount <= _pageSize) THEN 
	SET _pagecount = 1;
ELSE IF (_totalcount % _pageSize > 0) THEN 
	SET _pagecount = _totalcount DIV _pageSize + 1; 
ELSE 
SET _pagecount = _totalcount DIV _pageSize; 
END IF; 
end if;

END$$
DELIMITER ;

-- Ok
DROP PROCEDURE IF EXISTS `GetRecordAsPage`;

DELIMITER $$
CREATE PROCEDURE `GetRecordAsPage` 
(in tbName varchar(800),  -- 表名
in fldName varchar(1000), -- 表的列名
in strWhere varchar(500),  -- 查询条件
in pageIndex int, -- 第几页 传入1就是显示第一页
in pageSize int,  -- 一页显示几条记录
in orderType int, -- 0是升序 非0是降序
in sortName varchar(50) -- 排序字段
)
COMMENT '分页存储过程'
BEGIN
declare startRow int;
declare sqlStr varchar(1000);
declare limitTemp varchar(1000);
declare orderTemp varchar(1000); 
set startRow = (pageIndex-1)*pageSize; 
set sqlStr = CONCAT('SELECT ',fldName,' from ',tbName);
set limitTemp = CONCAT(' limit ',startRow,',',pageSize);
set orderTemp = CONCAT(' order by ',sortName);
if orderType = 0 then
set orderTemp = CONCAT(orderTemp,' ASC ');
else
set orderTemp = CONCAT(orderTemp,' DESC ');
end if; 
set @sqlString = CONCAT(sqlStr,' ',strWhere,orderTemp,limitTemp); 
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt; 
END$$ 
DELIMITER ;

-- 
DELIMITER $$
DROP PROCEDURE IF EXISTS `GetRecordCount` $$
-- --CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecordCount`(in tbName varchar(800),in strWhere varchar(500))
CREATE  PROCEDURE `GetRecordCount`
(
in tbName varchar(800),
in strWhere varchar(500)
)COMMENT '获取条件下的总记录数据 存储过程'
BEGIN
set @strSQL=concat('select count(*) as countStr from ',tbName,strWhere);
prepare sqlstmt from @strSQL;
execute sqlstmt;
deallocate prepare sqlstmt;
END $$
DELIMITER ;

-- OK
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LazyLoadScope`
(
IN ClientId    INT,
IN StartIndex INT,
IN Count INT
)COMMENT '分页存储过程'
BEGIN
DECLARE LowerBound INT;
DECLARE UpperBound INT;
DECLARE rownum INT;
SET LowerBound = ((StartIndex - 1) * Count) + 1;
SET UpperBound = ((StartIndex - 1) * Count) + Count;

SELECT scopeid,scopename,clientid,scope,createddate,ViewDate,IsLocked
  from (SELECT *, @rownum := @rownum + 1 AS rank 
  from (SELECT   sm.scopeid,sm.scopename,sm.clientid,sm.scope,sm.createddate,sm.ViewDate,sm.Is     Locked
FROM scopemaster as sm
inner join clientmaster cm on cm.clientid=sm.clientid
where cm.userid=ClientId order by sm.ViewDate desc) d, (SELECT @rownum  := 0) r ) m
WHERE rank >= LowerBound and rank <= UpperBound;

END$$
DELIMITER ;




DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomers_Pager`(
   _PageIndex INT
   ,_PageSize INT
   ,OUT _RecordCount INT
)COMMENT '分页存储过程'
BEGIN
       SET @RowNumber:=0;
 
       CREATE TEMPORARY TABLE Results
       SELECT @RowNumber:=@RowNumber+1 RowNumber
              ,CustomerID
              ,ContactName
              ,CompanyName
       FROM Customers;
 
       SET _RecordCount =(SELECT COUNT(*) FROM Results);
 
       SELECT * FROM Results
       WHERE RowNumber BETWEEN(_PageIndex -1) * _PageSize + 1 AND(((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;
 
       DROP TEMPORARY TABLE Results;
END$$
DELIMITER ;

-- OK
 DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE ProcPage(
in tableName varchar(20), #表名
in showField varchar(100), #要显示的列名
in whereText varchar(500), #where条件(只需要写where后面的语句)
in orderText varchar(500), #排序条件（只需要写order by后面的语句）
in pageSize int, #每一页显示的记录数
in pageIndex int, #当前页
out dataCount int  #总记录数
)COMMENT '分页存储过程'
BEGIN
DECLARE f INT unsigned DEFAULT 0;  
set f=1;
if _pagesize<=100 then
  set f=200;
end if;
if(pageSzie<1) then
 set pagesize=20;
end if;
if(pageIdex<1) then
  set pageIndex=1;
end if;
if(length(whereText)>0) then
 set whereText=concat(' where 1=1 ',whereText);
end if; 
if(LENGTH(orderText)>0)then
	set orderText = CONCAT(' ORDER BY ',orderText);
end if; 
 /*
if (pageSize<1) then
set pageSize=20;
end if; 
if (pageIndex < 1)then
  set pageIndex = 1;
end if; 
if(LENGTH(whereText)>0)then
	set whereText=CONCAT(' where 1=1 ',whereText);
end if; 
*/
set @strsql = CONCAT('select ',showField,' from ',tableName,' ',whereText,' ',orderText,' limit ',pageIndex*pageSize-pageSize,',',pageSize); 
prepare stmtsql from @strsql;
execute stmtsql;
deallocate prepare stmtsql; 
set @strsqlcount=concat('select count(1) as count into @datacount from ',tableName,'',whereText);
prepare stmtsqlcount from @strsqlcount;
execute stmtsqlcount;
deallocate prepare stmtsqlcount;
set datacount=@datacount;
END$$
DELIMITER ;

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄

×

选择打赏方式：

微信 QQ钱包 支付宝

打赏

打赏

打赏

多少都是心意！谢谢大家！！！