DROP temporary TABLE IF EXISTS tmp_record_t2;
CREATE Temporary TABLE tmp_record_t2
(
consumption_id INT (one) UNSIGNED not NULL,--Store ID
Created_org INT,
Return_money DECIMAL (11,2),--withdrawal amount
Cash DECIMAL (11,2),--cash
Member_card DECIMAL (11,2),--membership card
Bank_card DECIMAL (11,2),--Bank card
Credit DECIMAL (11,2),--Ledger
Free DECIMAL (11,2),--exempt?
Ticket DECIMAL (11,2),--Tickets
Foregift DECIMAL (11,2),--Deposit
Wxzf DECIMAL (11,2),--pay
Key PK (CONSUMPTION_ID)--Creates an index, which is very important when associated with a temporary table and can be increased 10 times times faster
) engine=MEMORY DEFAULT Charset=utf8;
TABLESTR2 is a dynamic statement that inserts the result into a temporary table
SET @exe_sql = CONCAT (' INSERT into Tmp_record_t2 ', @tablestr2);
PREPARE stmt from @exe_sql;
EXECUTE stmt;
The default method for temporary tables is MyISAM
But memory is about 20% faster than MyISAM. So specify the memory mode
Reference: http://wenku.baidu.com/link?url=h1rkLZdfavLLboTr9jph_l5MGcxyj5_ E9f2txss-l8zwcvlvhiouqu5n1qlvfwwdctdyq7fqy9g7izwqxv78ok0rore7dtxiewna3dp5aqk
MySQL Temp table creation Index