Support temporary table

[zimulala]

1. What did you do?
   create temporary table t1( n int);

2.What did you expect to see?
Query OK, 0 rows affected (0.00 sec)

3.What did you see instead?
ERROR 1105 (HY000): line 1 column 8 near "temporary"

It can refer to MySQL Doc

[zimulala]

Currently, we only support syntax, there is no real support for temporary table.

[zz-jason]

One use case in the batch process:

create a temporary table in each session:

create temporary table tmp(a bigint, b bigint);

concurrently execute the following transaction:

begin;

-- clear the tmp table.
truncate table tmp;

-- fill the tmp table.
insert into tmp select a, b from t where <condition> order by <primary_key> limit <batch_size> for update;

-- some select queries on the tmp table.
select ...

-- update the base table based on the same condition, the order by clause ensures the same batch.
update t set <change_some_status_field> where <condition> order by <primary_key> limit <batch_size>;

commit; -- or rollback.

[zz-jason]

another use case for the temporary table feature is to improve the performance of pagination:

• store the result in the session temporary table
• retrieve data from the session temporary table of each page

It avoids scanning the underlying table for each page, which can both improve the performance of pagination and reduce the resource utilization in the whole TiDB cluster.

[djshow832]

Duplicate with #24169