一个mysql存储记录

2019独角兽企业重金招聘Python工程师标准>>>

代码

<!-- lang: sql -->
BEGIN
#-------------------------tbsysinfo
prepare tempSql from "ALTER TABLE `tbsysinfo` ADD COLUMN `szClass`  varchar(2) NULL  DEFAULT '00'  AFTER `nType`";
execute tempSql;  

#-------------------------netCard
prepare 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;";
execute tempSql;
SELECT szAdminIP into @szAdminIp FROM `tbsyscfg`;
Select szName into @card1 from  tbnetcards where nType = 1 limit 1;
Select szName into @card2 from  tbnetcards where nType = 2 limit 1;
if @card2 is NULL THEN
	set @card2 = @card1;
end if;
insert into tbNetCfg values(NULL,@card1,@card2,@szAdminIp);

#-------------------------limitSpeed
prepare 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`)";
execute tmpSqlToRun;
select 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,nTime23 
into @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,@nTime23
from tblimitspeed;
insert 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);   
END

说明

数据库升级写的一个存储过程，记录一下，以前每次写存储过程都需要全网找资料,很烦。

1. 执行修改表结构语句：

   prepare tempSql from "ALTER TABLE tbsysinfo ADD COLUMN szClass varchar(2) NULL DEFAULT '00' AFTER nType";
   execute tempSql;

2. 结果赋值：

   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;

3. IF语法：

   if @card2 is NULL THEN
   set @card2 = @card1;
   end if;

最后limitSpeed 部分，20多个字段，没找到简单的多列赋值的方法，蠢办法一个个赋值，真是--!!

参考资料

1.修改表结构
2.存储过程变量赋值
3.游标使用 （当然此存储过程未使用）
4.存储过程事务管理
5.MyISAM InnoDB 区别

转载于:https://my.oschina.net/yanhuu/blog/159125