mysql 暫存資料表

標籤：

當查詢大表時，我們可以通過構造暫存資料表來避免頻繁的遍曆大表。暫存資料表只有當前串連可見，當串連斷開時會被自動drop，下文給出暫存資料表的應用例子。

前提：

1、分別構造表t_demo_test1和t_demo_test2，並輸入資料：

DROP TABLE IF EXISTS t_demo_test1;CREATE TABLE IF NOT EXISTS t_demo_test1(    tid int(20) NOT NULL AUTO_INCREMENT,    userid int(20),    day datetime NOT NULL,    title varchar(50),    content varchar(200),    PRIMARY KEY(tid))ENGINE=InnoDB  DEFAULT CHARSET=gbk AUTO_INCREMENT=1 ;DROP TABLE IF EXISTS t_demo_test2;CREATE TABLE IF NOT EXISTS t_demo_test2(    tid int(20) NOT NULL AUTO_INCREMENT,    userid int(20),    name varchar(50),    class varchar(50),    PRIMARY KEY(tid))ENGINE=InnoDB  DEFAULT CHARSET=gbk AUTO_INCREMENT=1 ;insert into t_demo_test2(userid, name, class) values(11, ‘fred‘, ‘important class‘);insert into t_demo_test1(userid, day, title, content) values(10,‘2015-04-05 22:10:30‘,‘title1‘,‘content sinny write test‘),(11,‘2015-04-05 22:10:30‘,‘title1‘,‘一種內容1‘),(12,‘2015-04-06 22:10:30‘,‘title1‘,‘一種內容2‘),(13,‘2015-04-07 22:10:30‘,‘title1‘,‘一種內容3‘),(14,‘2015-04-08 22:10:30‘,‘title1‘,‘一種內容4‘);

2、直接構造暫存資料表

CREATE TEMPORARY TABLE tmp_table SELECT userid, name FROM t_demo_test2;

3、在預存程序中使用暫存資料表

DELIMITER //DROP PROCEDURE IF EXISTS p_modify_content;CREATE PROCEDURE p_modify_content(    IN v_mcontent varchar(10),    OUT v_output varchar(100),    OUT v_count int)BEGIN    DECLARE tmpid int(20);    DECLARE tname varchar(50);    DECLARE tuserid int(20);    DECLARE done INT DEFAULT FALSE;    DECLARE cur CURSOR FOR SELECT t_demo_test1.userid, t_demo_test1.tid FROM t_demo_test1;        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    OPEN cur;        DROP TEMPORARY TABLE IF EXISTS usertmp;     CREATE TEMPORARY TABLE usertmp(        userid int(20),        name varchar(50)    ) ENGINE = MEMORY;        myloop: LOOP        FETCH cur INTO tuserid, tmpid;        IF done THEN            LEAVE myloop;        END IF;                SELECT t_demo_test2.name from t_demo_test2 WHERE userid = tuserid INTO tname;                select tname;        IF !isNull(tname) THEN                INSERT INTO usertmp VALUES(tuserid, tname);        END IF;                UPDATE t_demo_test1 SET content = concat(content,v_mcontent, tname) WHERE tid = tmpid;            SET done = FALSE;/* SELECT t_demo_test2.name from t_demo_test2 WHERE userid = tuserid INTO tname; 也會觸發NOT FOUND */        SET tname = null;    END LOOP;    SELECT COUNT(*) FROM usertmp INTO v_count;    SET v_output = ‘finished‘;    CLOSE cur;    DROP TEMPORARY TABLE IF EXISTS usertmp;END//call p_modify_content("hello", @content, @count);select @content;//輸出finishedselect @count;//輸出1

4、暫存資料表與記憶體表的區別

暫存資料表，schema和資料都在記憶體中，建立表後，無法通過show tables看到該表，重新進入後無表、無資料；

記憶體表，schema在硬碟中，資料在記憶體中，建立表後可以在show tables看到該表，重新進入後有表，無資料； 

如下，建立記憶體表：

create table XXtable(    。。。)engine = HEAP;

 

mysql 暫存資料表