Tabelas Temporárias MySQL

Resumo : neste tutorial, discutiremos tabelas temporárias do MySQL e mostraremos como criar, usar e eliminar tabelas temporárias.

Introdução às tabelas temporárias MySQL

No MySQL, uma tabela temporária é um tipo especial de tabela que permite armazenar um conjunto de resultados temporário, que pode ser reutilizado várias vezes em uma única sessão.

Uma tabela temporária é útil quando é impossível ou caro consultar dados que requerem uma única SELECTinstrução. Nesses casos, você pode usar uma tabela temporária para armazenar o resultado imediato e usar outra consulta para processá-lo.

Uma tabela temporária MySQL possui os seguintes recursos:

• Uma tabela temporária é criada usando CREATE TEMPORARY TABLEa instrução. Observe que a palavra-chave TEMPORARYé adicionada entre as  palavras-chave CREATEe TABLE.
• O MySQL remove a tabela temporária automaticamente quando a sessão termina ou a conexão é encerrada. Além disso, você pode usar a   DROP TABLEinstrução para remover explicitamente uma tabela temporária quando não a estiver mais usando.
• Uma tabela temporária só está disponível e acessível ao cliente que a cria. Diferentes clientes podem criar tabelas temporárias com o mesmo nome sem causar erros porque somente o cliente que cria a tabela temporária pode vê-la. Porém, na mesma sessão, duas tabelas temporárias não podem compartilhar o mesmo nome.
• Uma tabela temporária pode ter o mesmo nome de uma tabela normal em um banco de dados. Por exemplo, se você criar uma tabela temporária nomeada employeesno banco de dados de amostra , a tabela existente employeesficará inacessível. Cada consulta que você emite na employeestabela agora se refere à tabela temporária  employees. Quando você elimina a employeestabela temporária, a tabela normal employeesfica disponível e acessível.

Embora uma tabela temporária possa ter o mesmo nome de uma tabela normal, isso não é recomendado. Porque isso pode causar confusão e potencialmente causar perda inesperada de dados.

Por exemplo, se a conexão com o servidor de banco de dados for perdida e você se reconectar automaticamente ao servidor, não será possível diferenciar entre a tabela temporária e a tabela normal.

Em seguida, você poderá emitir uma DROP TABLE  instrução para remover a tabela permanente em vez da tabela temporária, o que não é esperado.

Para evitar esse problema, você pode usar a DROP TEMPORARY TABLEinstrução para eliminar uma tabela temporária em vez da DROP TABLEinstrução

Instrução MySQL CREATE TEMPORARY TABLE

A sintaxe da CREATE TEMPORARY TABLEinstrução é semelhante à sintaxe da CREATE TABLEinstrução, exceto pela TEMPORARYpalavra-chave:

CREATE TEMPORARY TABLE table_name(
   column1 datatype constraints,
   column1 datatype constraints,
   ...,
   table_constraints
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para criar uma tabela temporária cuja estrutura seja baseada em uma tabela existente, não é possível usar a CREATE TEMPORARY TABLE ... LIKEinstrução. Em vez disso, você usa a seguinte sintaxe:

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

1) Criando um exemplo de tabela temporária

Primeiro, crie uma nova tabela temporária chamada creditsque armazena os créditos dos clientes:

CREATE TEMPORARY TABLE credits(
  customerNumber INT PRIMARY KEY, 
  creditLimit DEC(10, 2)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, insira as linhas da customerstabela na tabela temporária credits:

INSERT INTO credits(customerNumber, creditLimit)
SELECT 
  customerNumber, 
  creditLimit 
FROM 
  customers 
WHERE 
  creditLimit > 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) Criação de uma tabela temporária cuja estrutura é baseada em um exemplo de consulta

O exemplo a seguir cria uma tabela temporária que armazena os 10 principais clientes por receita. A estrutura da tabela temporária é derivada de uma SELECTinstrução:

CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Agora, você pode consultar dados da top_customerstabela temporária como consultar uma tabela permanente:

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Descartando uma tabela temporária

Você pode usar a DROP TABLEinstrução para remover tabelas temporárias, mas é uma boa prática adicionar a TEMPORARYpalavra-chave da seguinte forma:

DROP TEMPORARY TABLE table_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A DROP TEMPORARY TABLE  instrução remove apenas uma tabela temporária, não uma tabela normal. Isso ajuda a evitar o risco de eliminar por engano uma tabela normal com o mesmo nome da tabela temporária.

Por exemplo, para remover a top_customerstabela temporária, use a seguinte instrução:

DROP TEMPORARY TABLE top_customers;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que se você tentar remover uma tabela normal com a DROP TEMPORARY TABLEinstrução, receberá uma mensagem de erro informando que a tabela que você está tentando eliminar é desconhecida.

Se você desenvolver um aplicativo que use pool de conexões ou conexões persistentes, não será garantido que as tabelas temporárias sejam removidas automaticamente quando seu aplicativo for encerrado.

Porque a conexão com o banco de dados que o aplicativo usa ainda pode estar aberta e colocada em um pool de conexões para outros clientes reutilizarem posteriormente.

Portanto, é uma boa prática sempre remover as tabelas temporárias sempre que não as utilizar mais.

Verificando se existe uma tabela temporária

O MySQL não fornece uma função ou instrução para verificar diretamente se existe uma tabela temporária.

No entanto, você pode criar um procedimento armazenado que verifique se existe ou não uma tabela temporária da seguinte maneira:

DELIMITER //
CREATE PROCEDURE check_table_exists(table_name VARCHAR(100)) 
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @err = 1;
    SET @err = 0;
    SET @table_name = table_name;
    SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
    PREPARE stmt1 FROM @sql_query;
    IF (@err = 1) THEN
        SET @table_exists = 0;
    ELSE
        SET @table_exists = 1;
        DEALLOCATE PREPARE stmt1;
    END IF;
END //
DELIMITER ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste procedimento, tentamos selecionar dados de uma tabela temporária. Se a tabela temporária existir, a @table_existsvariável será definida como 1, caso contrário, será definida como 0.

Esta instrução chama o check_table_existsprocedimento armazenado para verificar se a tabela temporária credits existe:

CALL check_table_exists('credits');
SELECT @table_exists;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Resumo

• O MySQL exclui automaticamente todas as tabelas temporárias assim que a sessão termina.
• Use a CREATE TEMPORARY TABLEinstrução para criar uma tabela temporária.
• Use a DROP TEMPORARY TABLEinstrução para eliminar uma tabela temporária.