Mysql使用replace代替CONCAT 速度提高十几倍

之前的存储过程:

CREATE DEFINER=`root`@`%` PROCEDURE `load_nb`( v int,p2 int)
BEGIN
   DECLARE rand_num INT;
   DECLARE fri1 int(11) DEFAULT '';
   DECLARE fri2 int(11) DEFAULT '';
   DECLARE fri3 int(11) DEFAULT '';
   DECLARE fri4 int(11) DEFAULT '';
   DECLARE fri5 int(11) DEFAULT '';
   DECLARE fri6 int(11) DEFAULT '';
   DECLARE fri7 int(11) DEFAULT '';
   DECLARE fri8 int(11) DEFAULT '';
   DECLARE fri9 int(11) DEFAULT '';
   DECLARE fri10 int(11) DEFAULT '';
   DECLARE fri11 int(11) DEFAULT '';
   DECLARE fri12 int(11) DEFAULT '';
   DECLARE fri13 int(11) DEFAULT '';
   DECLARE fri14 int(11) DEFAULT '';
   DECLARE fri15 int(11) DEFAULT '';
   while v < p2
      do


        set fri1 =  v-1;
        set fri2 =  v-2;
        set fri3 =  v-3;
        set fri4 =  v-4;
        set fri14 =  v-14;
        INSERT INTO  `neighbor` (`ng_uid`, `ng_friends`) VALUES (
                                  v ,   '{"0",":{"status":2,"tag":0,"mogic":1,"time":0}}');

                                    /*
                                    CONCAT( '{"',
                                                  fri1,'":{"status":2,"tag":0,"mogic":1,"time":1312870111,"help":1,"help_t":1312870118},"',
                                                  fri2,'":{"status":2,"tag":0,"mogic":1,"time":0},"',
                                                  fri3,'":{"status":1,"tag":0,"mogic":1,"time":1313137589,"help":0,"help_t":0},"',
                                                  fri4,'":{"status":2,"tag":0,"mogic":1,"time":0},"',
                                                  fri14,'":{"status":2,"tag":0,"mogic":1,"time":1313137566,"level":1}}')

                                 );
                                 */


      set v = v + 1;
      end while;


END



改进后的存储过程:


CREATE DEFINER=`root`@`localhost` PROCEDURE `load_nb`( v int,p2 int)

BEGIN
   DECLARE rand_num INT;
   DECLARE fri1 int(11) DEFAULT '';
   DECLARE fri2 int(11) DEFAULT '';
   DECLARE fri3 int(11) DEFAULT '';
   DECLARE fri4 int(11) DEFAULT '';
 
   DECLARE sqlnb varchar(1000) DEFAULT '';

   while v < p2
      do


        set fri1 =  Cast(v-1 as char);
        set fri2 =  Cast(v-2 as char);
        set fri3 =  Cast(v-3 as char);
        set fri4 =  Cast(v-4 as char);  

        set sqlnb =  '{"0":{"status":2,"tag":0,"mogic":1,"time":0},"@1@":{"status":2,"tag":0,"mogic":1,"time":0},"@2@":{"status":2,"tag":0,"mogic":1,"time":0},"@3@":{"status":2,"tag":0,"mogic":1,"time":0},"@4@":{"status":2,"tag":0,"mogic":1,"time":0}}';
        set sqlnb =  REPLACE(sqlnb,'@1@',fri1);
        set sqlnb =  REPLACE(sqlnb,'@2@',fri2);
        set sqlnb =  REPLACE(sqlnb,'@3@',fri3);
        set sqlnb =  REPLACE(sqlnb,'@4@',fri4);
        INSERT INTO  `neighbor` (`ng_uid`, `ng_friends`) VALUES (
                                  v ,     sqlnb );


      set v = v + 1;
      end while;


END
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值