Релиз MySQL 8.0 Labs: [Рекурсивные] Обобщенные Табличные Выражения в MySQL
Дата: 22.09.2016
Данная статья является переводом статьи Гильема Бишота. Курсивом даны комментарии переводчика.
Только что команда разработчиков опубликовала Labs релиз MySQL сервера. Главной особенностью этого релиза, которую я разработал, являются [Рекурсивные] Обобщенные Табличные Выражения, также известные как:
- [рекурсивные] ОТВ, (на английском Обобщенные Табличные Выражения это Common Table Expressions, поэтому в англоязычной литературе используется абревиатура CTE)
- [рекурсивные] factoring подзапросы,
- оператор WITH [RECURSIVE].
3 года назад я показал как можно эмулировать ОТВ, но сейчас в MySQL реализованы настоящие ОТВ, не эрзац!
Это первая статья из цикла, в котором будут освещены все подробности этой новой функциональности.
Производные таблицы это подзапрос в части FROM, выделено жирным ниже:
SELECT … FROM (SELECT …) AS derived_table;
Производные таблицы существуют в MySQL давно. Можно сказать, что нерекурсивные ОТВ являются "улучшенной версией производных таблиц". Вот первый пример:
WITH cte1(txt) AS (SELECT "This "),
cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
cte3(txt) AS (SELECT "nice query" UNION
SELECT "query that rocks" UNION
SELECT "query"),
cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
+----------------------------+----------------------+
| MAX(txt) | MIN(txt) |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)
Здесь ОТВ cte4 построена на основании других ОТВ cte3 и cte2, которые в свою очередь построены на основе cte1. Как прокомментировал один из читателей этого поста (Василий), то же самое может быть достигнуто с помощью производных таблиц:
SELECT MAX(txt), MIN(txt)
FROM
(
SELECT concat(cte2.txt, cte3.txt) as txt
FROM
(
SELECT CONCAT(cte1.txt,'is a ') as txt
FROM
(
SELECT 'This ' as txt
) as cte1
) as cte2,
(
SELECT 'nice query' as txt
UNION
SELECT 'query that rocks'
UNION
SELECT 'query'
) as cte3
) as cte4;
Однако, если вы сравните эти запросы, то заметите, что первый проще для восприятия, так как таблицы определяются последовательно одна за другой, и мы, читая сверху вниз, видим как формируются предложения; второй же имеет древовидную структуру вывернутую наизнанку (самое первое слово "This" вложено глубже всего). ОТВ сокращают код и делают его интуитивно понятным, что упрощает его дальнейшее сопровождение.
Из-за ограничений на размер статьи, я подробно рассмотрю нерекурсивные ОТВ в следующем материале - надеюсь, что пример выше разогрел ваш интерес к ним. Сейчас я сосредоточусь на рекурсивных ОТВ, которые, по моему скромному мнению, предоставляют гораздо больше новых возможностей по сравнению с нерекурсивными ОТВ.
Рекурсивные ОТВ - это набор строк, которые получаются путем последовательных итераций: на основе начального набора строк вычисляются новые строки, которые добавляются к первоначальному набору, и новые строки вновь вовлекаются в процесс вычисления последующих строк до тех пор, пока на очередном этапе не будет произведено нулевое количество строк. (Обратите внимание, в процессе вычисления новых строк участвуют только строки, полученные на предыдущем этапе, а не весь объединенный набор.)
Максимально простой синтаксис для рекурсивных ОТВ имеет вид:
WITH RECURSIVE cte_name AS
(
SELECT ... <-- определяет начальный набор строк
UNION ALL
SELECT ... <-- определяет как вычисляются новые строки
)
с последующим оператором SELECT, INSERT, UPDATE, DELETE или внутри любого SELECT подзапроса.
Рассмотрим первый пример, производящий целые числа от 1 до 10:
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0,00 sec)
Сравнив его с правилом синтаксиса, увидим, что:
- ОТВ называется my_cte
- определение ОТВ является префиксом оператора SELECT: SELECT * FROM my_cte
- начальный набор определен запросом SELECT 1 AS n, т.е. это строка, содержащая "1". Метаданные этой строки определяют столбцы ОТВ, таким образом my_cte имеет одну колонку типа INT с именем n в следствии использования алиаса AS n.
- процесс, который вычисляет новые строки, определен как
SELECT 1+n FROM my_cte WHERE n<10, что означает взять строки из my_cte (речь идет только о тех строках my_cte, которые были получены на предыдущем этапе рекурсии), для которых n<10, и для каждой из них увеличить n на единицу.
- my_cte очевидно определена как рекурсивная, так как содержит ссылку на саму себя (в части FROM второго оператора SELECT). По этому признаку легко можно отличать рекурсивные ОТВ от нерекурсивных.
Таком образом MySQL выполнит следующие шаги:
- создаст исходный набор строк, назовем его S0: S0={1},
- на основании S0 создаст новый набор строк S1={2},
- на основании S1 создаст S2={3},
- и так далее,
- на основании S8 создаст S9={10},
- на следующем шаге для S9 нет строк, удовлетворяющих условию n<10, поэтому новых строк получено не будет, что вызовет прекращение рекурсии,
- на последнем этапе происходит слияние промежуточных наборов S0, S1, … и S9: {1,2,…,9,10}. Этот результат мы и видим, когда обращаемся к my_cte во внешнем SELECT запросе.
Исходный набор строк иногда называют "нерекурсивный SELECT", "якорь рекурсии" или "источник рекурсии". SELECT описывающий процесс получения новых строк - "рекурсивный SELECT" (в русскоязычной литературе также используется "элемент рекурсии"); "рекурсивный" потому что ссылается на my_cte.
В общем случае синтаксис для рекурсивных ОТВ имеет вид:
WITH RECURSIVE cte_name [список имен столбцов] AS
(
SELECT ... <-- определяет начальный набор строк
UNION ALL
SELECT ... <-- определяет начальный набор строк
UNION ALL
...
SELECT ... <-- определяет как вычисляются новые строки
UNION ALL
SELECT ... <-- определяет как вычисляются новые строки
...
)
[, определения других ОТВ в любом количестве ]
Таким образом:
- можно определить первоначальный набор как результат объединения нескольких операторов SELECT
- процесс получения новых строк можно определить с помощью нескольких операторов SELECT, чьи результаты объединяются на каждой итерации
- за этим определением ОТВ могут следовать определения других ОТВ, которые могут ссылаться на ранее определенные ОТВ
- в одной конструкции WITH можно смешивать нерекурсивные и рекурсивные ОТВ (обратите внимание, что если присутствует хоть одно рекурсивное ОТВ, то вся конструкция должна начинаться с ключевых слов WITH RECURSIVE)
- вместо того, чтобы определять имена колонок ОТВ с помощью псевдонимов в первом операторе SELECT, их можно указать сразу после имени ОТВ: cte_name(n)
Давайте для экономии места на экране изменим наш пример так, чтобы он производил целые числа от 1 до 6, имя колонки определим с помощью синтаксиса my_cte(n), и используем результат my_cte для создания таблицы:
USE test;
CREATE TABLE numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0,00 sec)
ОТВ также могут быть использованы с операторами INSERT (и REPLACE):
INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
12 rows in set (0,00 sec)
С оператором UPDATE (в том числе и в многотабличном варианте):
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
# Change to 0...
SET numbers.n=0
# ... the numbers which are squares, i.e. 1 and 4
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
+------+
12 rows in set (0,00 sec)
И с оператором DELETE (в том числе и в многотабличной форме); обратите внимание, что ОТВ используется внутри подзапроса:
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
DELETE FROM numbers
# delete the numbers greater than the average of 1,...,6 (=3.5)
WHERE numbers.n > (SELECT AVG(n) FROM my_cte);
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 0 |
| 2 |
| 3 |
| 0 |
+------+
8 rows in set (0,00 sec)
Похожий пример, где ОТВ целиком определен внутри подзапроса, а не перед ключевым словом DELETE:
DELETE FROM numbers
WHERE numbers.n >
(
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
# Half the average is 3.5/2=1.75
SELECT AVG(n)/2 FROM my_cte
);
Query OK, 4 rows affected (0,07 sec)
+------+
| n |
+------+
| 0 |
| 0 |
| 0 |
| 0 |
+------+
4 rows in set (0,00 sec)
Довольно гибкие, не так ли?
Обратите внимание: что будет если я забуду указать условие WHERE n<6? Запрос будет работать вечно, так как он всегда будет производить новые строки (больше нет причин остановиться на 6). Если используется mysql command-line клиент, я мог бы прервать выполнение нажатием Control-C; в другом клиенте я мог бы открыть новую сессию и использовать оператор KILL. Но гораздо проще при экспериментах с рекурсивными ОТВ указать в начале сессии:
SET max_execution_time = 10000;
в этом случае запрос, выполняющийся больше 10 секунд, будет автоматически прерван.
Вот и все на сегодня. В следующих постах я продолжу эту тему, но здесь уже есть достаточно материала, чтобы вы могли начать экспериментировать с ОТВ. Прежде чем вы займетесь этим, пожалуйста, примите во внимание два момента, которые могут сэкономить ваше время:
- Если вы используете рекурсивные ОТВ для создания все более длинных и длинных строк (например, с помощью CONCAT() в рекурсивном SELECT), то учтите, что типы столбцов ОТВ определяются только на основании нерекурсивного SELECT: определите ваши колонки достаточно широкими в нерекурсивном SELECT с помощью функции CAST (… AS CHAR()), иначе ваши данные могут не поместиться.
- Внутри определения рекурсивного ОТВ (то, что находится в AS (…)) существуют некоторые ограничения синтаксиса (причины я раскрою в следующих материалах):
- все операторы SELECT должны объединяться с помощью UNION ALL
- рекурсивный SELECT не может содержать GROUP BY, агрегатные функции (например, SUM), ORDER BY, LIMIT, DISTINCT (это ограничение не распростаняется на нерекурсивный SELECT)
- рекурсивный SELECT должен ссылаться на ОТВ, в котором определен, только один раз и только в части FROM, а не в любом подзапросе. Конечно он может ссылаться также и на другие таблицы и объединять их со своим ОТВ с помощью join, что очень удобно для построения иерархий (например, если у нас есть таблица боссов и сотрудников, и мы хотим узнать "кто подчиняется прямо или косвенно миссис Х?"). Если соединение происходит с помощью LEFT JOIN, то ОТВ не должно быть на правой стороне.
И, как всегда:
select unhex("5468616E6B20796F7520666F722063686F6F73696E67204D7953514C21") as final_words;
+-------------------------------+
| final_words |
+-------------------------------+
| Thank you for choosing MySQL! |
+-------------------------------+
Дата публикации: 22.09.2016
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
