Урок 9.4 · Время чтения: ~7 мин

Временная таблица в SQL существует только в текущей сессии (соединении) и автоматически удаляется, когда сессия заканчивается. В этом уроке вы узнаете, как создавать временные таблицы, как заполнять их результатом запроса, чем они отличаются от обычных таблиц и CTE и какие ограничения у них есть в MySQL.

Временные таблицы в SQL

Сложный анализ часто выполняется по шагам: сначала отбираются нужные клиенты, затем по ним строится несколько отчётов. Промежуточный результат можно повторять в каждом отчёте в виде подзапроса или CTE, а можно один раз сохранить во временную таблицу и использовать во всех следующих запросах.

Учебные базы данных на SQLtest доступны только для чтения, поэтому CREATE TEMPORARY TABLE вернёт там ошибку доступа. Примеры используют схему Sakila.

Что такое временная таблица

Временная таблица выглядит и работает как обычная: в неё можно вставлять строки, выбирать, изменять и удалять их. Отличия — во времени жизни и видимости:

• она видна только в той сессии, которая её создала; другие соединения её не видят, даже если создадут временную таблицу с тем же именем;
• она удаляется автоматически, когда сессия заканчивается;
• она предназначена для промежуточных данных, а не для постоянного хранения.

Синтаксис

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

Чаще всего временную таблицу создают сразу из запроса:

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

Пример: повторное использование промежуточного результата

Сохраним клиентов, сделавших больше 30 платежей:

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

Теперь несколько независимых запросов могут использовать её без повторной агрегации:

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;

Результат: список самых активных клиентов и сводные показатели по той же группе, рассчитанной один раз.

Если таблица больше не нужна, а сессия продолжается, удалите её явно:

DROP TEMPORARY TABLE IF EXISTS tmp_active_customers;

В MySQL DROP TEMPORARY TABLE удаляет только временную таблицу, поэтому по ошибке не удалит обычную таблицу с тем же именем.

Временная таблица и обычная таблица

Последняя строка — ловушка: если временная таблица названа так же, как существующая, она скрывает ту таблицу до конца сессии. Используйте префикс, например tmp_.

Временная таблица и CTE

Используйте CTE (урок 6.4), когда промежуточный результат нужен в одном запросе. Используйте временную таблицу, когда он нужен в нескольких запросах, его дорого вычислять или он большой и выигрывает от индекса:

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);

Ограничения в MySQL

• Одно упоминание на запрос. MySQL не позволяет использовать временную таблицу в одном запросе дважды, например в self-join или одновременно во внешнем запросе и подзапросе: возникает ошибка «Can't reopen table». В таких случаях используйте CTE или обычную таблицу.
• Репликация и пулы соединений. В приложениях, которые переиспользуют соединения, временная таблица может дожить до следующего запроса на том же соединении. Удаляйте её явно, когда она больше не нужна.
• Движок хранения. Временные таблицы хранятся в памяти или на диске в зависимости от размера. ENGINE = MEMORY держит таблицу в оперативной памяти, но не поддерживает столбцы TEXT/BLOB и ограничен max_heap_table_size.

Ключевые выводы:

• Временная таблица живёт до конца сессии и видна только этой сессии.
• CREATE TEMPORARY TABLE ... AS SELECT сохраняет результат запроса.
• Временную таблицу можно использовать во многих запросах и индексировать; CTE существует только внутри одного оператора.
• Используйте префикс tmp_: временная таблица скрывает обычную таблицу с тем же именем.
• В MySQL на временную таблицу можно сослаться в запросе только один раз.

Часто задаваемые вопросы

Когда удаляется временная таблица?

Когда заканчивается создавшая её сессия. Удалить её раньше можно командой DROP TEMPORARY TABLE table_name в MySQL или DROP TABLE table_name в других СУБД.

Чем временная таблица отличается от CTE?

CTE существует только внутри одного SQL-оператора. Временная таблица — настоящая таблица, которая существует до конца сессии, поэтому ею могут пользоваться несколько отдельных запросов, и у неё могут быть индексы.

Видят ли другие пользователи мою временную таблицу?

Нет. Временная таблица видна только в создавшей её сессии. В двух сессиях могут даже существовать временные таблицы с одинаковым именем без всякого конфликта.