MySQL установил переменную внутри CTE: "select" - это нормально, но "set" не работает

#mysql #sql #common-table-expression #local-variables

#mysql #sql #общее-табличное-выражение #локальные переменные

Вопрос:

Люди говорят, что CTE — это представление, и вы не можете установить переменную в CTE. Но я успешно установил переменную @ttl в CTE, выполнив запрос ниже:

 with CTE as (select @ttl:=(select sum(num) from test))

И когда я запускаю это, что я считал просто другим способом установки переменной, я получил сообщение об ошибке:

 with CTE as (set @ttl=(select sum(num) from test))

Я очень смущен. Что именно выполнимо, а что нет с точки зрения установки переменной в CTE?

1. Не могли бы вы сказать, почему вы пытаетесь установить переменную в предложении with? Показать больше того, что вы на самом деле пытаетесь сделать? Мне кажется очень вероятным, что вы получите хороший совет для лучшего подхода к тому, что это такое.

Ответ №1:

В скобках указан подзапрос; подзапросы всегда являются операторами SELECT.

https://dev.mysql.com/doc/refman/8.0/en/with.html:

 with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

https://dev.mysql.com/doc/refman/8.0/en/subqueries.html:

Подзапрос — это оператор SELECT внутри другого оператора.

Обратите внимание, что mysql устарел устанавливать переменные где угодно, кроме как в инструкции SET, поэтому в будущей версии ожидайте, что даже ваш первый CTE выдаст ошибку.

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html:

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

Ответ №2:

Добавление к ответу от @ysth, вы можете использовать INTO для присвоения переменной выражения в списке выбора:

 mysql> insert into test set num=42;

mysql> with cte as (select sum(num) as num from test) 
       select num into @ttl from cte;

mysql> select @ttl;
 ------ 
| @ttl |
 ------ 
|   42 |
 ------

Обратите внимание, что INTO должно быть во внешнем запросе. Если вы попытаетесь использовать его внутри подзапроса, вы получите эту ошибку:

 mysql> with cte as (select sum(num) into @ttl from test) 
       select * from cte;
ERROR 3954 (HY000): Misplaced INTO clause, INTO is not allowed inside subqueries, 
and must be placed at end of UNION clauses.

Я предполагаю INTO , что синтаксис будет оставаться законным даже в будущих версиях MySQL, после того, как назначение побочного эффекта с := вызывает ошибку.