mysql sku如何存储_mysql之存储过程(一)

今天开发一个需求，需要在一个旧表中增加一列并且对已经的表中记录初始化新列的值，

由于是一次性的工作，故写了个存储过程来代替代码程序初始化

创建及执行过程记录如下：

MySQL [XXX_YYY]> delimiter $$

MySQL [XXX_YYY]>CREATE  PROCEDURE `updateShopIXXXXXSkuIdV2`()

->  BEGIN

->   #Routine body goes here...

->  declare i int;

->  set i=0;

->   set @sum=(select count(1) from shopxxx_info where xx_sku_id is null and yn = 1);

->  set @log = "log query ....";

->   select CONCAT(@log,@sum," 条");

->  while i

->   set @itemId =( select item_id from shopxxx_info where xx_sku_id is null and yn = 1 limit i,1);

->    set @jdSkuId =( select jd_sku_id from item_yyyy_table where item_id = @itemId  limit 1);

->    #set @itemId = 4499991;

->    #set @xxSkuId =29825634;

->    #select @itemId ,  @jdSkuId;

->    if @itemId is NULL THEN

->     select @itemId ;

->    elseif @xxSkuId is NULL THEN

->     select @xxSkuId;

->    else

->       update shopxxxx_info set xx_sku_id = @xxSkuId ,modified = now() where item_id = @itemId and yn = 1;

->    end if;

->       set i=i+1;

->    end while;

->

-> END$$

Query OK, 0 rows affected (0.01 sec)

MySQL [XXX_YYY]> call updateShopIXXXXXSkuIdV2()$$

+--------------------------+

| CONCAT(@log,@sum," 条")  |

+--------------------------+

| log query ....126 条     |

+--------------------------+

MySQL [XXX_YYY]> delimiter ;

ps：创建存储过程可以用navicate等客户端工具生成调试，然后把调试好的存储过程脚本，在生产环境中重写一下就好。