SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

MySQL error 1093 и 1235

Дата: 25.10.2013

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

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

MySQL error 1093

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

DELETE FROM t WHERE col = (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
приведут к ошибке
ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause.

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

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

DELETE FROM t WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS t1);
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS t1);
{INSERT|REPLACE} INTO t (SELECT * FROM (SELECT ... FROM t...) AS t1);

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

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

UPDATE t, (SELECT ... FROM t ...) t1 ... WHERE t.col=t1.col;

По сути это тот же метод, что и предыдущий - подзапрос переносится в часть перечисления таблиц. Но кроме чуть более компактной записи многотабличная форма операторов 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. Попытка выполнить такой запрос приведет к ошибке:

mysql> SELECT * FROM t1
    ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 '
LIMIT & IN/ALL/ANY/SOME subquery'

Данное ограничение обходится путем переписывания запроса через 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 подзапросах, то решением было:

SELECT name FROM country WHERE continent='Europe' AND
1000000 < ALL (SELECT population FROM city WHERE countrycode=code OREDR BY 1 DESC LIMIT 3);

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

SELECT country.name FROM country JOIN city ON countrycode=code
WHERE continent='Europe' AND city.population>1000000
GROUP BY 1 HAVING(count(*)>2);
Замечание: Использовать вложенный подзапрос при mysql error 1235 является плохой идеей:
1. этот прием сработает только в случае независимых подзапросов;
2. на старых версиях (до MariaDB5.3/MySQL5.6) будет иметь худшую производительность, а в новых оптимизатор сам перепишет запрос через JOIN

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

Дата публикации: 25.10.2013

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

Статьи :
 Установка и настройка MySQL
>Коды ошибок в MySQL
 Программирование в MySQL
 Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Услуги по оптимизации MySQL