Lição 9.4 · Tempo de leitura: ~7 min

Uma tabela temporária SQL é uma tabela que existe apenas na sessão (conexão) atual e é removida automaticamente quando ela termina. Nesta lição, você vai aprender a criar tabelas temporárias, a preenchê-las a partir de uma consulta, como diferem das tabelas normais e das CTEs, e que limitações têm no MySQL.

Tabelas Temporárias em SQL

Uma análise complexa costuma ser feita por etapas: selecionar os clientes de interesse e depois calcular vários relatórios sobre eles. O resultado intermédio pode ser repetido como subconsulta ou CTE em cada relatório, ou guardado uma vez numa tabela temporária e reutilizado por todas as consultas seguintes.

Os bancos de exemplo do SQLtest são somente leitura, por isso CREATE TEMPORARY TABLE devolve ali um erro de permissão. Os exemplos usam o esquema Sakila.

O Que É uma Tabela Temporária

Uma tabela temporária tem o aspeto e o funcionamento de uma tabela normal: pode inserir, selecionar, atualizar e remover linhas. As diferenças estão no tempo de vida e na visibilidade:

• é visível apenas na sessão que a criou; as outras conexões não a veem, mesmo que criem uma tabela temporária com o mesmo nome;
• é removida automaticamente quando a sessão termina;
• destina-se a dados intermédios, não a armazenamento permanente.

Sintaxe

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
);

Na maioria das vezes, uma tabela temporária é criada diretamente a partir de uma consulta:

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

Exemplo: Reutilizar um Resultado Intermédio

Guarde os clientes que fizeram mais de 30 pagamentos:

CREATE TEMPORARY TABLE tmp_active_customers AS
SELECT customer_id, COUNT(*) AS payment_count
FROM payment
GROUP BY customer_id
HAVING COUNT(*) > 30;

Agora várias consultas independentes podem usá-la sem repetir a agregação:

SELECT ac.customer_id, c.first_name, c.last_name, ac.payment_count
FROM tmp_active_customers AS ac
INNER JOIN customer AS c ON c.customer_id = ac.customer_id
ORDER BY ac.payment_count DESC;

SELECT COUNT(*) AS customers,
       AVG(payment_count) AS avg_payments,
       MAX(payment_count) AS max_payments
FROM tmp_active_customers;

Resultado: a lista dos clientes mais ativos e números de resumo do mesmo grupo, calculado uma única vez.

Quando já não precisar da tabela numa sessão longa, remova-a explicitamente:

DROP TEMPORARY TABLE IF EXISTS tmp_active_customers;

No MySQL, DROP TEMPORARY TABLE remove apenas uma tabela temporária, por isso não pode remover por engano uma tabela normal com o mesmo nome.

Tabela Temporária vs Tabela Normal

A última linha é uma armadilha: se uma tabela temporária tiver o nome de uma tabela existente, esconde essa tabela durante o resto da sessão. Use um prefixo como tmp_.

Tabela Temporária vs CTE

Use uma CTE (Lição 6.4) quando o resultado intermédio é necessário numa só consulta. Use uma tabela temporária quando é necessário em várias consultas, quando é caro de calcular ou quando é grande e beneficia de um índice:

CREATE TEMPORARY TABLE tmp_action_customers AS
SELECT DISTINCT r.customer_id
FROM rental AS r
INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
INNER JOIN film_category AS fc ON fc.film_id = i.film_id
INNER JOIN category AS c ON c.category_id = fc.category_id
WHERE c.name = 'Action';

ALTER TABLE tmp_action_customers ADD PRIMARY KEY (customer_id);

Limitações no MySQL

• Uma referência por consulta. O MySQL não permite que uma tabela temporária apareça duas vezes na mesma consulta, por exemplo num self-join ou simultaneamente na consulta externa e numa subconsulta: o erro é "Can't reopen table". Nesses casos, use uma CTE ou uma tabela normal.
• Replicação e pools de conexões. Em aplicações que reutilizam conexões, uma tabela temporária pode sobreviver até ao pedido seguinte na mesma conexão. Remova-a explicitamente quando terminar.
• Motor de armazenamento. As tabelas temporárias ficam em memória ou em disco, conforme o tamanho. ENGINE = MEMORY mantém a tabela na RAM, mas não suporta colunas TEXT/BLOB e é limitado por max_heap_table_size.

Principais conclusões:

• Uma tabela temporária vive até o fim da sessão e só é visível nessa sessão.
• CREATE TEMPORARY TABLE ... AS SELECT guarda o resultado de uma consulta.
• Uma tabela temporária pode ser reutilizada por muitas consultas e indexada; uma CTE só existe dentro de uma instrução.
• Use um prefixo tmp_: uma tabela temporária esconde uma tabela normal com o mesmo nome.
• No MySQL, uma tabela temporária só pode ser referenciada uma vez por consulta.

Perguntas Frequentes

Quando é que uma tabela temporária é removida?

Quando termina a sessão que a criou. Pode removê-la antes com DROP TEMPORARY TABLE table_name no MySQL ou DROP TABLE table_name noutros bancos de dados.

Qual é a diferença entre uma tabela temporária e uma CTE?

Uma CTE existe apenas dentro de uma instrução SQL. Uma tabela temporária é uma tabela real que existe até o fim da sessão, por isso várias consultas separadas podem usá-la, e pode ter índices.

Outros utilizadores podem ver a minha tabela temporária?

Não. Uma tabela temporária só é visível na sessão que a criou. Duas sessões podem até ter tabelas temporárias com o mesmo nome sem qualquer conflito.