Как использовать общие табличные выражения MySQL – с примерами запросов

Содержание статьи

Выполняя повседневную работу инженера-программиста или администратора базы данных, вам, вероятно, придется писать длинные сложные запросы, часто с некоторыми подзапросами.

Эти запросы со временем становятся менее продуктивными, трудно читать и понимать, а управлять ими становится еще труднее. И никто не хочет выполнять тяжелую работу по их рефакторингу, потому они просто живут.

Или вам, вероятно, приходилось получать схожие данные на основе набора данных или параметров. Чтобы добиться этого, вы пишете много подобных, иногда абсолютно идентичных подзапросов и объединяете их с помощью ключевого слова UNION.

Что ж, вы можете облегчить жизнь и эффективно решить эти проблемы с помощью общего табличного выражения.

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

Используя общее выражение таблицы, вы можете легко писать более читаемые и более эффективные запросы. Это действительно проще, чем написать несколько подзапросов, которые могут сделать ваши запросы нечитаемыми и менее продуктивными.

В первую очередь вы будете использовать общее выражение таблицы по двум причинам:

Для написания запросов без использования подзапросов (или меньшего количества подзапросов)
Написать рекурсивные функции

В этом руководстве я покажу вам, как написать общие табличные выражения.

Как создать общее табличное выражение

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

Общая форма выражения общей таблицы такова:

WITH cte_name AS (query)

-- Multiple CTEs
WITH
    cte_name1 AS (
        -- Query here
    ),
    cte_name2 AS (
        -- Query here
    )

Структура базового КТР

The WITH за ключевым словом следует название CTE. После названия вы вводите запрос, который необходимо выполнить в CTE с помощью AS ключевое слово. Необходимо взять запрос в круглые скобки. После CTE не может стоять точка с запятой, как в других запросах SQL. За ним следует другой запрос, который его использует.

После создания CTE можно легко использовать результат запросов, выполненных в CTE, ссылаясь на CTE в других запросах, других CTE или даже в самом себе.

Пример CTE

Например, если у вас есть таблица игроков world_cup, вы можете создать CTE следующим образом:

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    *
FROM
    barca_players;

Здесь мы создали CTE под названием barca_players. Этот CTE возвращает имя, должность, возраст и национальность каждого игрока Барселоны, находившегося на чемпионате мира. Он содержит подзапрос:

SELECT
    id,
    player_name,
    nationality,
    position,
    TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
FROM
    wc_players
WHERE
    club = 'Barcelona';

Этот подзапрос дает результат CTE. Далее следует запрос, использующий этот результат. Вы можете увидеть результат выбора каждой записи в CTE ниже.

Вы также можете выбрать только определенные поля из CTE, например:

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    position
FROM
    barca_players;

Этот запрос почти такой же, как и первый, за исключением того, что он выбирает только имена и позиции игроков из списка.

Как использовать общие табличные выражения с параметрами

Вы также можете передавать доводы в CTE. Это псевдонимы, которые могут использоваться для ссылок на столбце результатов запроса. Количество параметров, передаваемых в CTE, должно совпадать с количеством столбцов, выбираемых в его подзапросе. Это потому, что столбцы сопоставляются с псевдонимами один за другим, один за другим.

Например, в barca_players CTE, созданный выше, вы можете решить обратиться к nationality колонка как countryи position как role:

WITH
    barca_players (id, player_name, country, role, age) AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    role
FROM
    barca_players;

Обратите внимание, что в подзапросе CTE вы все еще используете правильные названия столбцов. Но во внешнем SELECT запрос, вы используете новые псевдонимы, указанные как параметры CTE.

Рекурсивные общие табличные выражения

Когда вы ссылаетесь на общее табличное выражение в самом себе, оно становится рекурсивным общим табличным выражением.

Рекурсивное общее табличное выражение, как следует из названия, является общим табличным выражением, которое может запускать подзапрос несколько раз, пока выполняется условие. Он выполняет непрерывные итерации, пока не достигнет точки разрыва, когда условие перестает быть истинным.

Чтобы определить рекурсивный КТР, RECURSIVE ключевое слово должно быть в его названии. Без этого ключевого слова MySQL выдает ошибку.

Например, вы можете написать обычное табличное выражение, печатающее числа от 1 до 10 и их квадраты следующим образом:

WITH RECURSIVE
    numbers_list (n, square) AS (
        SELECT
            1,
            1
        UNION ALL
        SELECT
            n + 1,
            (n + 1) * (n + 1)
        FROM
            numbers_list
        WHERE
            n < 10
    )
SELECT
    *
FROM
    numbers_list;

Давайте рассмотрим, что здесь происходит:

В первых двух строчках выражение рекурсивной общей таблицы определяется двумя параметрами, один представляет столбец для числа, а другой представляет столбец для квадрата:

WITH RECURSIVE
    numbers_list (n, square) AS (

Дальше подзапрос. Подзапрос состоит из двух частей, объединенных UNION ALL ключевое слово для формирования одного. Вы также можете присоединиться к этим подзапросам с помощью UNION ключевое слово, если вам не требуются повторяющиеся записи.

Первая часть подзапроса является ключевой частью рекурсивных выражений общей таблицы. Это базовый запрос, первый набор результатов, исходная итерация. Этот запрос является отправной точкой всех итераций.

В этом примере он статический, поскольку никакие записи не загружаются.

SELECT
    1,
    1

После этого первого запроса таблица результатов имеет одну строчку и выглядит так:

Вторая часть подзапроса – это место, где действительно происходит итерация.

В этом запросе CTE ссылается на него самого и можно использовать его столбцы. Когда упоминается название столбца, берется последнее значение столбца.

Итак, в начале итерации, n равно 1 и square также равно 1. Это значит, n + 1 равно 2, и (n + 1) * (n + 1) равно 2*2, что равно 4. 2 и 4 прилагаются к таблице результатов, а затем становятся последними значениями в таблице. n становится 2, и square становится 4.

Это продолжается до состояния в WHERE ключевое слово перестает быть истинным.

The WHERE Ключевое слово в запросе определяет точку разрыва CTE. Пока указанное условие не будет выполнено, запрос продолжает выполняться. В этом случае после каждой итерации запрос проверяет if n менее 10

Если установлено условие, которое всегда будет оцениваться как истина, это создает бесконечный цикл и вы получаете ошибку типа. Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

SELECT
    n + 1,
    (n + 1) * (n + 1)
FROM
    numbers_list
WHERE
    n < 10

Теперь вы можете подумать: «Если условие проверяет n < 10 почему 10 все еще в финальной таблице?».

Ну, причина в том, что в SQL, WHERE Ключевое слово запроса оценивается сначала перед другими частями. Поэтому, когда n = 9 является последней строкой, запрос выполняется еще раз, и перед вставкой или любым другим он проверяет, 9 меньше 10. Поскольку 9 меньше 10, он добавляет n + 1 что 10 в списке. Тогда на следующей итерации 10 является новейшей записью, и она не меньше себя, поэтому цикл завершается.

Имейте в виду, что рекурсивное выражение общей таблицы состоит из рекурсивного SELECT запрос и нерекурсивный SELECT запрос.

Простые рекурсивные правила выражений общей таблицы

Вы не можете использовать GROUP BY ключевое слово. Это объясняется тем, что можно сгруппировать только коллекцию, но в рекурсивном выражении общей таблицы записи обрабатываются и оцениваются отдельно. Другие ключевые слова, например ORDER BY, DISTINCTи агрегатные функции, такие как SUM также нельзя использовать.
Вы не можете использовать функции окна.

Эти правила применяются к рекурсивной части рекурсивного выражения общей таблицы.

Случаи использования рекурсивных CTE

Последовательность Фибоначчи

Последовательность Фибоначчи – это последовательность, в которой каждое число является суммой двух предыдущих. Последовательность обычно начинается с 0 и 1, хотя некоторые авторы начинают последовательность с 1 и 1 или иногда с 1 и 2. (источник)

Можно легко создать последовательность Фибоначчи любой длины, используя рекурсивное выражение общей таблицы. К примеру, вот запрос, который получит первые 20 чисел последовательности Фибоначчи, начиная с 0 и 1.

WITH RECURSIVE
    fibonacci (n, fib_n, next_fib_n) AS (
        /*
        * n - Number of iterations
        * fib_n - Currennt Fibonnaci number. Starts at 0
        * next_fib_n - Next Fibonnaci number. Starts at 1
        */
        SELECT
            1,
            0,
            1
        UNION ALL
        SELECT
            n + 1,
            next_fib_n,
            fib_n + next_fib_n
        FROM
            fibonacci
        WHERE
            n < 20
    )
SELECT
    *
FROM
    fibonacci;

Иерархический обход данных

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

Например, a categories Таблица обычно содержит основные категории и подкатегории, ссылающиеся на их родительскую категорию. Ан employees стол будет содержать обычных сотрудников со своими manager_idа также их руководители или руководители, поскольку они тоже являются работниками.

Если у вас был a categories таблица с 4 записями, 1 основной категорией и цепочкой подкатегорий:

CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);

Вы можете получить каждую категорию, ее родительскую категорию легко прикрепить следующим образом:

WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;

В этом примере базовый запрос выбирает корневую категорию, где parent_category_id IS NULL. Затем он ищет категорию, где parent_category_id есть id текущей категории с помощью a JOIN. Он повторяет это, пока не дойдет до последней категории. Результат этого запроса таков:

Резюме

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

Если у вас есть какие-либо вопросы или советы, свяжитесь со мной, чтобы поделиться ими.

Чтобы прочитать больше моих статей или следить за моей работой, вы можете связаться со мной на LinkedIn, Twitter и Github. Это быстро, легко и бесплатно!