bitscn.com
一个mysql存储记录
01begin02#-------------------------tbsysinfo03prepare tempsql from alter table `tbsysinfo` add column `szclass` varchar(2) null default '00' after `ntype`;04execute tempsql; 0506#-------------------------netcard07prepare tempsql from create table `tbnetcfg` ( `nid` int(10) null auto_increment, `szcapture` varchar(100) not null default '0', `szservicename` varchar(100) not null default '0', `szserviceip` varchar(100) not null default '0', primary key (`nid`))collate='utf8_general_ci' engine=innodb;;08execute tempsql;09select szadminip into @szadminip from `tbsyscfg`;10select szname into @card1 from tbnetcards where ntype = 1 limit 1;11select szname into @card2 from tbnetcards where ntype = 2 limit 1;12if @card2 is null then13 set @card2 = @card1;14end if;15insert into tbnetcfg values(null,@card1,@card2,@szadminip);1617#-------------------------limitspeed18prepare tmpsqltorun from alter table `tblimitspeed` add column `nweekday` smallint(1) not null default 1 after `ntimezdef`,add column `group` smallint(1) null default 1 after `nweekday`,add primary key (`nweekday`);19execute tmpsqltorun;20select nweekday,`group`,nbandwidth,ntimezdef,ntime0,ntime1,ntime2,ntime3,ntime4,ntime5,ntime6,ntime7,ntime8,ntime9,ntime10,ntime11,ntime12,ntime13,ntime14,ntime15,ntime16,ntime17,ntime18,ntime19,ntime20,ntime21,ntime22,ntime2321into @nweekday,@groupp,@bandwidth,@ntimezdef,@ntime0,@ntime1,@ntime2,@ntime3,@ntime4,@ntime5,@ntime6,@ntime7,@ntime8,@ntime9,@ntime10,@ntime11,@ntime12,@ntime13,@ntime14,@ntime15,@ntime16,@ntime17,@ntime18,@ntime19,@ntime20,@ntime21,@ntime22,@ntime2322from tblimitspeed;23insert into tblimitspeed values(@ntime0,@ntime1,@ntime2,@ntime3,@ntime4,@ntime5,@ntime6,@ntime7,@ntime8,@ntime9,@ntime10,@ntime11,@ntime12,@ntime13,@ntime14,@ntime15,@ntime16,@ntime17,@ntime18,@ntime19,@ntime20,@ntime21,@ntime22,@ntime23,@bandwidth,@ntimezdef,2,@groupp); 24end
说明
数据库升级写的一个存储过程，记录一下，以前每次写存储过程都需要全网找资料,很烦。
执行修改表结构语句：
prepare tempsql from alter table tbsysinfo add column szclass varchar(2) null default '00' after ntype; execute tempsql;
结果赋值：
select szname into @card1 from tbnetcards where ntype = 1 limit 1; set @card2 = @card1; 未使用的一种 declare cnt int default 0; select count(*) into cnt from test_tbl; select cnt;
if语法：
if @card2 is null then set @card2 = @card1; end if;
最后limitspeed 部分，20多个字段，没找到简单的多列赋值的方法，蠢办法一个个赋值，真是--!!
bitscn.com