程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql存儲過程加速

mysql存儲過程加速

編輯:MySQL綜合教程

首先看一個存儲過程,這個存儲過程是為了構造數據使用的。當然貼出來的存儲過程簡化了一些不必要的表結構。

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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved