mysql临时表的使用

一、脚本

use edisondb;

drop procedure if exists query_performance_test;

DELIMITER //  
create procedure query_performance_test()
begin
    declare begintime time;
    declare endtime time;

    set begintime=curtime();

    DROP TEMPORARY TABLE IF EXISTS userinfo_tmp;

    CREATE TEMPORARY TABLE userinfo_tmp(
        i_userid int,
        v_username varchar(30)
    ) ENGINE = MEMORY;

    insert into userinfo_tmp(i_userid,v_username) 
    select i_userid,v_username
    from userinfo
    where i_userid>1000 and i_userid<8000;

    select * from userinfo_tmp;
    
    DROP TEMPORARY TABLE IF EXISTS userinfo_tmp;

    set endtime=curtime();

    select endtime-begintime;
end
//
DELIMITER  ; 

call query_performance_test();

二、执行