歡迎進(jìn)入Linux社區(qū)論壇，與200萬(wàn)技術(shù)人員互動(dòng)交流 >>進(jìn)入 首先看一個(gè)存儲(chǔ)過(guò)程，這個(gè)存儲(chǔ)過(guò)程是為了構(gòu)造數(shù)據(jù)使用的。當(dāng)然貼出來(lái)的存儲(chǔ)過(guò)程簡(jiǎn)化了一些不必要的表結(jié)構(gòu)。 CREATE PROCEDURE modifyRootEntry() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE u

歡迎進(jìn)入Linux社區(qū)論壇，與200萬(wàn)技術(shù)人員互動(dòng)交流 >>進(jìn)入

首先看一個(gè)存儲(chǔ)過(guò)程，這個(gè)存儲(chǔ)過(guò)程是為了構(gòu)造數(shù)據(jù)使用的。當(dāng)然貼出來(lái)的存儲(chǔ)過(guò)程簡(jiǎn)化了一些不必要的表結(jié)構(gòu)。

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;

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請(qǐng)注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的mysql存储加速_mysql存储过程加速的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。