MySQL: просмотр с подзапросом в ограничении предложения FROM

Почему в MySQL 5.0 возникает следующая ошибка при попытке создать представление с подзапросом в предложении FROM?

ОШИБКА 1349 (HY000): SELECT представления содержит подзапрос в предложении FROM

Если это ограничение движка MySQL, то почему они еще не реализовали эту функцию?

Кроме того, как можно обойти это ограничение?

Существуют ли какие-либо обходные пути, которые работают для любого подзапроса в предложении FROM, или есть некоторые запросы, которые нельзя выразить без использования подзапроса в предложении FROM?

Пример запроса (похоронен в комментарии):

SELECT temp.UserName 
FROM (SELECT u1.name as UserName, COUNT(m1.UserFromId) as SentCount 
      FROM Message m1, User u1 
      WHERE u1.uid = m1.UserFromId 
      Group BY u1.name HAVING SentCount > 3 ) as temp

mysql sql view mysql-error-1349
person Daniel    schedule 15.10.2008    source источник
comment
Спасибо тебе за этот пост. Я обычно избегаю представлений в mysql. Работаем над крайним случаем, пытаемся объединить таблицы fk - впихиваем их в представление. Теперь это. Какая боль - все эти ограничения на подзапросы? Готов перейти на другой двигатель.   -  person eggmatters    schedule 26.02.2016
comment
Это был известный проектный недостаток MySQL, на который потребовалось ~ 10 лет, прежде чем он привлек некоторое внимание. Это было исправлено в MySQL 5.7.7, и я могу подтвердить, что это случай из 5.7.24. Просто к вашему сведению.   -  person code_dredd    schedule 17.01.2019

Ответы (5)


arrow_upward
18
arrow_downward

Разве ваш запрос нельзя было просто записать как:

SELECT u1.name as UserName from Message m1, User u1 
  WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3

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

person Grant Limberg    schedule 15.10.2008
comment
Спасибо, я не понимал, что вы можете выполнить GROUP BY, не имея агрегатной функции в SELECT. Итак, одна из причин, по которой они не разрешают подзапросы в предложении FROM в MySQL, - это проблемы со скоростью? - person Daniel; 16.10.2008
comment
В данном конкретном случае это не обязательно из-за скорости. На данный момент оптимизатор вообще не работает с подзапросами. По возможности держитесь от них подальше. Это исправлено в 6.0, и в этом деле был достигнут большой прогресс, но это в 6.0, и вы используете 5.0. - person Grant Limberg; 16.10.2008
comment
Пожалуйста, не используйте неявный синтаксис. Это антипаттерн SQL, который 20 лет назад был заменен улучшенным синтаксисом. - person HLGEM; 17.02.2012
comment
HLGEM, не могли бы вы показать нам пример лучшего синтаксиса? Я предполагаю, что вы говорите, что мы должны использовать подзапросы, но, поскольку MySQL не допускает их в представлении, мне любопытно, какова наилучшая практика в этом случае. - person Code Commander; 28.02.2012
comment
@CodeCommander, HLGEM означает, что вы должны использовать синтаксис соединения ANSI: ... FROM Message m1 JOIN User u1 ON u1.uid = m1.UserFromID ... - person qu1j0t3; 25.05.2013
comment
Разве запрос в вашем комментарии нельзя было записать просто как ... К какому комментарию относится этот комментарий? - person Joshua Taylor; 26.11.2014
comment
@JoshuaTaylor Я только что отредактировал исходное сообщение с запросом, на который он ссылается. - person agent provocateur; 03.06.2015
comment
@agentprovocateur, какой пост вы редактировали? Я не вижу изменений к ответу, а последнее изменение вопроса было 7 августа 2013 года, 14:21. - person Joshua Taylor; 04.06.2015
comment
@JoshuaTaylor: правка ожидает утверждения ... ха-ха. я отредактировал исходный пост / вопрос. Я не уверен, как работает процесс экспертной оценки, но stackoverflow.com/review/suggested-edits/8313127 - person agent provocateur; 04.06.2015
comment
Думаю, я не могу добавить их исходный запрос к их вопросу, потому что они должны сделать это сами (?). Какой-то парень отверг меня, сказав, что это не относящаяся к делу информация LOL. Разве запрос в вашем комментарии не может быть просто записан как: кажется довольно очевидным, что ВЫБРАННЫЙ ОТВЕТ относится к запросу и без запроса, отправленного по исходному вопросу ... это в значительной степени бесполезно. похоже, они предпочитают, чтобы этот вопрос оставался расплывчатым. Я собираюсь предположить, что OP никогда не обновит это самостоятельно (изначально опубликовано в 2008 году). НУ ЧТО Ж. Я ПЫТАЛСЯ. знак равно - person agent provocateur; 04.06.2015

arrow_upward
83
arrow_downward

У меня такая же проблема. Я хотел создать представление для отображения информации за последний год из таблицы с записями с 2009 по 2011 год. Вот исходный запрос:

SELECT a.* 
FROM a 
JOIN ( 
  SELECT a.alias, MAX(a.year) as max_year 
  FROM a 
  GROUP BY a.alias
) b 
ON a.alias=b.alias and a.year=b.max_year

Схема решения:

  1. создать представление для каждого подзапроса
  2. заменить подзапросы этими представлениями

Вот запрос решения:

CREATE VIEW v_max_year AS 
  SELECT alias, MAX(year) as max_year 
  FROM a 
  GROUP BY a.alias;

CREATE VIEW v_latest_info AS 
  SELECT a.* 
  FROM a 
  JOIN v_max_year b 
  ON a.alias=b.alias and a.year=b.max_year;

Он отлично работает с mysql 5.0.45 без особого снижения скорости (по сравнению с выполнением исходного подзапроса select без каких-либо представлений).

person Json on Linux Apache MySQL    schedule 19.08.2010
comment
сбой при резервном копировании, так как mysql восстанавливает представления на основе их имен, поэтому сначала он попытается восстановить v_latest_info и потерпит неудачу, потому что v_max_year еще не существует ... - person Apolo; 29.05.2015
comment
Приведет ли создание этих нескольких представлений к проблемам с производительностью? - person Annie; 15.02.2017

arrow_upward
5
arrow_downward

Кажется, это известная проблема.

http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html

http://bugs.mysql.com/bug.php?id=16757

Многие запросы IN можно переписать как (левое внешнее) соединение и какое-то значение IS (NOT) NULL. Например

SELECT * FROM FOO WHERE ID IN (SELECT ID FROM FOO2)

можно переписать как

SELECT FOO.* FROM FOO JOIN FOO2 ON FOO.ID=FOO2.ID

or

SELECT * FROM FOO WHERE ID NOT IN (SELECT ID FROM FOO2)

возможно

SELECT FOO.* FROM FOO 
LEFT OUTER JOIN FOO2 
ON FOO.ID=FOO2.ID WHERE FOO.ID IS NULL
person Nikki9696    schedule 15.10.2008
comment
Но как бы вы переписали запрос в предложении FROM? Например, как я могу переписать этот запрос ?: SELECT temp.UserName FROM (SELECT u1.name as UserName, COUNT (m1.UserFromId) as SentCount FROM Message m1, User u1 WHERE u1.uid = m1.UserFromId Group BY u1. name HAVING SentCount ›3) as temp - person Daniel; 15.10.2008
comment
Я не думаю, что вы можете, но вы можете создать второе представление и выбрать из него вместо использования подвыбора, насколько мне известно. Если вы не возражаете против сохраненной процедуры, вы также можете использовать временные таблицы (при условии, что это достаточно свежая версия MySQL). - person Nikki9696; 16.10.2008

arrow_upward
5
arrow_downward

Вы можете обойти это, создав отдельный VIEW для любого подзапроса, который хотите использовать, а затем присоединиться к нему в VIEW, который вы создаете. Вот пример: http://blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/

Это очень удобно, так как вы, скорее всего, все равно захотите использовать его повторно, и это поможет вам сохранить свой SQL DRY.

person Gruff    schedule 27.01.2015
comment
Привет, Ninjabber - поделитесь, пожалуйста, любыми конкретными проблемами, с которыми вы столкнулись. Представления очень эффективны в MySQL, но да, вам нужно знать их все, чтобы защититься от проблем с производительностью, но, по моему опыту, их легко избежать. - person Gruff; 09.03.2016
comment
Боюсь, что не в mysql 5.0. У вас нет экземпляра с 5.0, но создайте его сами, поместите 3 подзапроса в представления и присоедините их к индексированным столбцам. Вы будете удивлены планом объяснения. С действительно сложными подзапросами, которые по отдельности работали нормально, у меня были проблемы даже в 5.6. 5.7 другое. Там обработка больше оракула - person ninjabber; 10.03.2016
comment
забыл добавить: Обновите машину до 5.7 и никаких ограничений нет. Даже AWS объявила о поддержке 5.7 в RDS. Единственная причина, по которой вы, возможно, не захотите обновляться, - это использование какого-либо сервера приложений (например, Hybris), который еще не поддерживает 5.7. Но это вопрос времени, поскольку 5.7 находится на другом уровне и, безусловно, лучший движок, который когда-либо был у MySQL. Самое главное, что мы, наконец, можем использовать более одного ядра и обрабатывать параллельные транзакции, что, в свою очередь, позволяет вам играть с кешем и, наконец, использовать его преимущества. - person ninjabber; 10.03.2016
comment
Спасибо, это полезно знать - у меня не было никаких серьезных проблем с 5.5, кроме представлений, где я не указывал слияние и случайно получил огромные материализованные представления. Я видел некоторые странные поведения, хотя даже с присоединениями к подзапросам (не представлениям), которые я работал с двумя отдельными запросами / временными таблицами. - person Gruff; 11.03.2016

arrow_upward
4
arrow_downward

создать представление для каждого подзапроса - вот способ. Получил, что это работает как шарм.

person Dexin Wang    schedule 11.04.2013