当前位置:Gxlcms > mysql > mysql存储过程加速

mysql存储过程加速

时间:2021-07-01 10:21:17 帮助过:8人阅读

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入 首先看一个存储过程,这个存储过程是为了构造数据使用的。当然贴出来的存储过程简化了一些不必要的表结构。 CREATE PROCEDURE modifyRootEntry() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE u

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入

  首先看一个存储过程,这个存储过程是为了构造数据使用的。当然贴出来的存储过程简化了一些不必要的表结构。

  CREATE PROCEDURE modifyRootEntry()

  BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE userId INT;

  DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;

  OPEN userIdIter;

  read_loop: LOOP

  FETCH userIdIter INTO userId;

  IF done THEN

  LEAVE read_loop;

  END IF;

  INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);

  UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;

  END LOOP;

  CLOSE userIdIter;

  END;

  CREATE PROCEDURE modifyRootEntry()

  BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE userId INT;

  DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;

  START TRANSACTION; // here !

  OPEN userIdIter;

  read_loop: LOOP

  FETCH userIdIter INTO userId;

  IF done THEN

  LEAVE read_loop;

  END IF;

  INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);

  UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;

  END LOOP;

  CLOSE userIdIter;

  COMMIT;// here !

  END;

  UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;

  CREATE PROCEDURE modifyRootEntry()

  BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE userId INT;

  DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;

  START TRANSACTION; // here !

  OPEN userIdIter;

  read_loop: LOOP

  FETCH userIdIter INTO userId;

  IF done THEN

  LEAVE read_loop;

  END IF;

  INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);

  END LOOP;

  UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL; // here!

  CLOSE userIdIter;

  COMMIT;// here !

  END;

  CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED)

  BEGIN

  DECLARE s INT UNSIGNED DEFAULT 1;

  TRUNCATE TABLE Nums;

  INSERT INTO Nums SELECT s;

  WHILE s*2 <= cnt DO

  BEGIN

  INSERT INTO Nums SELECT a+s FROM Nums;

  SET s = s*2;

  END;

  END WHILE;

  END;

人气教程排行