MySql 常用用法

CREATE DEFINER=`root`@`%` PROCEDURE `autoNewTable`()
begin
-- 初始化时使用，平时填表用netTable过程
declare tn varchar(50) default '';

declare done int default 0;
declare cur cursor for select f_table from cnf_tablelist;
declare continue handler for not found set done = true;

open cur;
read_loop:loop
    fetch cur into tn;
    if done then
        leave read_loop;
    end if;
    call newTable(tn);
end loop;
close cur;

end

===============================================
CREATE DEFINER=`root`@`%` PROCEDURE `autoDropTable`()
begin
-- 初始化时使用，平时填表用netTable过程
declare tn varchar(50) default '';

declare done int default 0;
declare cur cursor for select f_table from cnf_tablelist;
declare continue handler for not found set done = true;

open cur;
read_loop:loop
    fetch cur into tn;
    if done then
        leave read_loop;
    end if;
    call dropTable(tn);
end loop;
close cur;

end
=============================================================

CREATE DEFINER=`root`@`%` PROCEDURE `autoNewPartition`()
begin
-- 每月1日05：00 自动执行，为每张表创建新的分区表（必须是每月第一天的凌晨，才能保证不与newTable()冲突
declare tn varchar(50) default '';
declare tcount int default 0;
declare done int default 0;
declare pbegin varchar(50) default '';
declare pend varchar(50) default '';

declare cur cursor for select f_table from cnf_autopartitiontable;
declare continue handler for not found set done = true;

select now() into pbegin;

open cur;
read_loop:loop
    fetch cur into tn;
    if done then
        leave read_loop;
    end if;
    call newPartition(tn);
end loop;
close cur;

select now() into pend;

insert into log_event (f_eventName,f_begin,f_end) values ('autoNewPartition',pbegin,pend);

end

=================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `newPartition`(tableName varchar(50))
begin
-- 计算下下个月1日的日期表达式（每个月1日做下下个月的分区）
set @pName = concat(year(adddate(now(),interval 2 month)),'-',right(concat('0',month(adddate(now(),interval 2 month))),2),'-01 08:00:00');
set @pMonth = concat(year(adddate(now(),interval 2 month)),right(concat('0',month(adddate(now(),interval 2 month))),2));

set @sqlStr = concat("alter table ",tableName," add partition ( partition ",tableName,"_",@pMonth," values less than('",@pName,"'));");

 PREPARE create_stmt from @sqlStr;

 EXECUTE create_stmt;

-- select @sqlStr;

end
======================================================================
CREATE DEFINER=`root`@`%` FUNCTION `fv`(pv varchar(50)) RETURNS varchar(50) CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
begin
declare p int default  0;
declare q varchar(50) DEFAULT 0;

select right(pv,length(pv)- INSTR(pv,'|')) into q;

if q = '192' then
 select left(pv,INSTR(pv,'|')-1)+0 into p;
else 
 select 0 into p;
end if;

return p;

end
===========================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `newTable`(tableName varchar(20))
begin
-- 计算下个月1日的日期表达式
set @pName = concat(year(adddate(now(),interval 1 month)),'-',right(concat('0',month(adddate(now(),interval 1 month))),2),'-01 08:00:00');
set @pMonth = concat(year(adddate(now(),interval 1 month)),right(concat('0',month(adddate(now(),interval 1 month))),2));

set @pName1 = concat(year(adddate(now(),interval 2 month)),'-',right(concat('0',month(adddate(now(),interval 2 month))),2),'-01 08:00:00');
set @pMonth1 = concat(year(adddate(now(),interval 2 month)),right(concat('0',month(adddate(now(),interval 2 month))),2));

set @sqlStr = concat("create table ",tableName," (
  `f_sn` bigint(255) NOT NULL AUTO_INCREMENT,
  `f_creatortime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`f_sn`,`f_creatortime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
partition by range columns(f_creatortime)(
partition ",tableName,"_",@pMonth," values less than('",@pName,"'),
partition ",tableName,"_",@pMonth1," values less than('",@pName1,"'));
");

PREPARE create_stmt from @sqlStr;
EXECUTE create_stmt;

-- insert into cnf_tablelist (f_table,f_desp,f_creatortime) values (tableName,desp,now());
-- select @sqlStr;

end
=========================================================================
CREATE DEFINER=`root`@`%` FUNCTION `ccDay`(t datetime) RETURNS varchar(50) CHARSET utf8
    DETERMINISTIC
begin
declare d varchar(50) DEFAULT '';
select if(hour(t) < 8 or (hour(t)<9 and MINUTE(t)<30) ,DATE_FORMAT(date_add(t,interval -1 DAY),'%Y-%m-%d') ,DATE_FORMAT(t,'%Y-%m-%d')) into d;
return d;
end
===========================================================================
CREATE DEFINER=`root`@`%` PROCEDURE `showPartition`(ptablename varchar(50))
begin
set @sqlStr = concat("
select
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where
  table_schema = schema()  
  and table_name  = '",ptablename,"'");
    
PREPARE create_stmt from @sqlStr;
EXECUTE create_stmt;
end
=========================================================================
CREATE DEFINER=`root`@`%` FUNCTION `yesterday`() RETURNS varchar(50) CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
begin

declare d varchar(50) DEFAULT 0;

select date_format(DATE_ADD(now(),interval -1 day) ,'%Y-%m-%d') into d;

return d;

end
=============================================================================