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 ;