Heim >Datenbank >MySQL-Tutorial >mysql存储过程加速

mysql存储过程加速

WBOY
WBOYOriginal
2016-06-07 15:08:501315Durchsuche

欢迎进入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

  BEGIN

  INSERT INTO Nums SELECT a+s FROM Nums;

  SET s = s*2;

  END;

  END WHILE;

  END;

mysql存储过程加速

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:ORACLE导入导出Nächster Artikel:MySQL的外键约束级联删除