MySQL temp table

When querying large tables, we can avoid frequent traversal of large tables by constructing temporary tables. Temporary tables are only visible to the current connection, and are automatically dropped when the connection is broken, and examples of application of temporary tables are given below.

Premise:

1. Construct the table t_demo_test1 and T_demo_test2 respectively, and enter the data:

DROP TABLE IF EXISTST_demo_test1;CREATE TABLE IF  not EXISTST_demo_test1 (Tidint( -) not NULLauto_increment, UserIDint( -),     Day datetime  not NULL, titlevarchar( -), Contentvarchar( $),    PRIMARY KEY(TID)) ENGINE=InnoDBDEFAULTCHARSET=GBK auto_increment=1 ;DROP TABLE IF EXISTST_demo_test2;CREATE TABLE IF  not EXISTST_demo_test2 (Tidint( -) not NULLauto_increment, UserIDint( -), namevarchar( -), Classvarchar( -),    PRIMARY KEY(TID)) ENGINE=InnoDBDEFAULTCHARSET=GBK auto_increment=1 ;Insert  intoT_demo_test2 (userid, Name, Class)Values( One,'Fred','Important Class');Insert  intoT_demo_test1 (UserID, Day, title, content)Values(Ten,'2015-04-05 22:10:30','Title1','content Sinny Write test'),( One,'2015-04-05 22:10:30','Title1','One content 1'),( A,'2015-04-06 22:10:30','Title1','One content 2'),( -,'2015-04-07 22:10:30','Title1','One content 3'),( -,'2015-04-08 22:10:30','Title1','One content 4');

2, directly constructs the temporary table

CREATE Temporary TABLE SELECT  from T_demo_test2;

3. Using temporary tables in stored procedures

DELIMITER//DROP PROCEDURE IF EXISTSp_modify_content;CREATE PROCEDUREP_modify_content (inchV_mcontentvarchar(Ten), out V_outputvarchar( -), out V_countint)BEGIN    DECLARETmpidint( -); DECLARETnamevarchar( -); DECLARETuseridint( -); DECLAREDoneINT DEFAULTFALSE; DECLARECurCURSOR  for SELECTT_demo_test1.userid, T_demo_test1.tid fromT_demo_test1; DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE; OPENcur; DROP Temporary TABLE IF EXISTSusertmp; CREATE Temporary TABLEusertmp (useridint( -), namevarchar( -) ) ENGINE=MEMORY; Myloop:loopFETCHCur intoTuserid, Tmpid; IFDone ThenLEAVE Myloop; END IF; SELECTT_demo_test2.name fromT_demo_test2WHEREUserid=Tuserid intoTname; SelectTname; IF!IsNull(Tname) Then                INSERT  intoUsertmpVALUES(Tuserid, tname); END IF; UPDATET_demo_test1SETContent=Concat (Content,v_mcontent, Tname)WHERETid=Tmpid; SETDone=FALSE;/*SELECT t_demo_test2.name from t_demo_test2 WHERE userid = Tuserid into tname; also triggers not FOUND*/        SETTname= NULL; ENDLOOP; SELECT COUNT(*) fromUsertmp intoV_count; SETV_output= 'finished'; CLOSEcur; DROP Temporary TABLE IF EXISTSusertmp;END//Call p_modify_content ("Hello",@content,@count);Select @content;//Output finishedSelect @count;//Output 1

4, the difference between the temporary table and the memory table

Temporary table, schema and data are all in memory, after creating the table, can not see the table through show tables, re-entry without table, no data;

Memory table, schema on the hard disk, data in memory, create a table after the show tables can see the table, re-enter the table, no data;

as follows, create the memory table:

Create Table xxtable (    = HEAP;

MySQL temp table