MySQL temporary vs memory table in stored procedures
What's is better to use in a stored procedure: a temporary 开发者_C百科table or a memory table?
The table is used to stored summary data for reports.
Are there any trade offs that developers should be aware off?
CREATE TEMPORARY TABLE t (avg (double));
or
CREATE TABLE t (avg (double)) ENGINE=MEMORY;
Why is this restricted to just the two options? You can do:
CREATE TEMPORARY TABLE t (avg double) ENGINE=MEMORY;
Which works, although I'm not sure how to check if the memory engine is actually being used here.
Of the two, I'd use a temporary table for report.
A memory table holds data across user sessions & connections, so you'd have to truncate it every time to make sure you wouldn't be using data from someone else. Assuming you put in whats necessary to maintain a memory table depending on your needs, it's fine - the temp table is a little safer from a maintenance perspective.
A temporary table will only exist for the duration of your session. A table declared with Engine=Memory will persist across user sessions / connections but will only exist in the lifetime of the MySQL instance. So if MySQL gets restarted the table goes away.
In MySQL, temporary tables are seriously crippled:
http://dev.mysql.com/doc/refman/5.6/en/temporary-table-problems.html
You cannot refer to a TEMPORARY table more than once in the same query.
For example, the following does not work:
mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
I Just wanted to point out that, in 2021 using MariaDB-10.3.27, the code @biziclop said doesn't work, is not the case any more, this is possible:
CREATE TEMPORARY TABLE tmp1 AS
SELECT * FROM products LIMIT 10;
SELECT * FROM tmp1, tmp1 AS t2;
(I just tested it)
精彩评论