MySQL error 1093 и 1235

Дата: 25.10.2013

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

В статье рассмотрены ошибки, возникающие из-за ограничений сервера MySQL на выполнение некоторых видов подзапросов. Даны рекомендации по их исправлению с примерами. Материал ориентирован на новичков.

MySQL error 1093

В MySQL нельзя изменять данные и одновременно делать выборку из той же таблицы в подзапросе. Запросы вида

Есть два варианта решения проблемы:

1. Универсальный способ, рекомендуемый в документации, - использовать вложенный подзапрос.

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

2. Для запросов UPDATE и DELETE можно использовать многотабличную форму. Например, для UPDATE запрос выше примет вид:

По сути это тот же метод, что и предыдущий - подзапрос переносится в часть перечисления таблиц. Но кроме чуть более компактной записи многотабличная форма операторов UPDATE/DELETE в некоторых случаях позволяет вообще обойтись без подзапроса.

Примеры:

• мы хотим удалить из первой таблицы строки, не имеющие соответствия (по id) во второй.
  DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
  Для сравнения через подзапрос:
  DELETE FROM t1 WHERE id IN (SELECT * FROM (SELECT t1.id FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL) as t_x);
• удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id
  DELETE t1 FROM t t1 JOIN t t2
  ON t1.col = t2.col AND t1.id < t2.id;
  через подзапрос
  DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;
• а вот задача пометить важными статьи авторов, имеющих более 10 публикаций, без подзапроса не решается:
  UPDATE articles SET important=1 WHERE author IN (SELECT * FROM (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t);
  или
  UPDATE articles, (SELECT author FROM articles GROUP BY 1 HAVING count(*)>10) t SET important=1  WHERE author = t.author;
• в завершение рассмотрим пример, когда подзапрос находится в части SET. Например, для строк с id>10 мы хотим установить значение поля col равное значению этого поля для строки с id равным 2.
  UPDATE t as t1 JOIN t as t2 ON t2.id=2 SET t1.col = t2.col WHERE t1.id > 10;
  через подзапрос
  UPDATE t
      SET col = (SELECT * FROM (SELECT col FROM t WHERE id = 2) AS t1)
      WHERE id = >10;

MySQL error 1235

В MySQL не реализована возможность использования IN/ALL/ANY/SOME вместе с подзапросом, содержащим LIMIT. Попытка выполнить такой запрос приведет к ошибке:

Данное ограничение обходится путем переписывания запроса через JOIN с нужным подзапросом. Например:
SELECT * FROM t1 WHERE t1.col1 IN (SELECT col2 FROM t2 WHERE x);
можно переписать как
SELECT * FROM t1 JOIN (SELECT DISTINCT col2 FROM t2 WHERE x) t ON t1.col1=t.col2;
Если t2.col2 - уникальный ключ, то DISTINCT не потребуется и от подзапроса можно избавиться:
SELECT * FROM t1 JOIN t2 ON t1.col1=t.col2 WHERE t2.x;

На практике IN подзапросы более распространены и в большинстве случаев указанного правила будет достаточно. Некоторые виды ALL/ANY подзапросов могут потребовать более сложных преобразований, но переписать через JOIN можно любой их них.

Например, мы хотим найти страны в Европе, у которых каждый из трех самых крупных городов имеют население свыше миллиона. Если бы не запрет на использование LIMIT в ALL подзапросах, то решением было:

через JOIN данный запрос примет вид:

Замечание: Использовать вложенный подзапрос при mysql error 1235 является плохой идеей:
1. этот прием сработает только в случае независимых подзапросов;
2. на старых версиях (до MariaDB5.3/MySQL5.6) будет иметь худшую производительность, а в новых оптимизатор сам перепишет запрос через JOIN

P.S. Если после прочтения статьи ваш вопрос с MySQL Error 1093 или 1235 остался нерешенным, то задавайте его на форуме SQLinfo

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.