首先看一个存储过程,这个存储过程是为了构造数据使用的。当然贴出来的存储过程简化了一些不必要的表结构。
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;查看更多关于mysql存储过程加速 - mysql数据库栏目 - 自学php的详细内容...
声明:本文来自网络,不代表【好得很程序员自学网】立场,转载请注明出处:http://www.haodehen.cn/did4145