MySQL: Хранимые процедуры и динамический SQL

    Если кто-либо из вас пытался сделать вроде бы очевидную вещь, а именно, создать sql запрос внутри процедуры передав ей имя таблицы, пользователя и т.п., то скорее всего натыкались на ошибку, о том, что нельзя использовать динамический sql.

    SET @mytable='users';
    SELECT * FROM @mytable;

    Такая конструкция работать не будет. А что же делать, чтобы она заработала?

    Для примера напишем процедуру архивации любой таблицы в БД.
    Процедура будет принимать название таблицы в качестве параметра и создавать другую таблицу с используя engine=ARCHIVE

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `create_archive`$$
    CREATE PROCEDURE `create_archive`(IN current_table VARCHAR(50)
    )
    BEGIN
    DECLARE template,archive_template VARCHAR(50);

    -- Если название таблицы было mydata
    -- то получаем название архивной таблицы mydata_20090226

    SET archive_template=replace(curdate(),"-","");
    SET template=CONCAT(current_table,"_",archive_template);

    -- Эта конструкция формирует запрос который будет выглядить так
    -- CREATE TABLE mydata_20090226 ENGINE_ARCHIVE
    -- AS (SELECT * FROM mydata);

    SET @archive_query:=CONCAT("CREATE TABLE ",template," ENGINE=ARCHIVE AS
    (SELECT * FROM ",current_table," )");

    PREPARE archive_query FROM @archive_query;
    EXECUTE archive_query;
    DEALLOCATE PREPARE archive_query;

    END$$

    DELIMITER ;

    Для того чтобы составить динамический запрос нужно сначала собрать его через CONCAT() и далее выполнить используя PREPARE,EXECUTE. Такой метод очень часто применим для построения сложных запросов в процедурах. Надеюсь, кому-нибудь пригодится.

    UPD: Поправил опечатки в тексте, спасибо Goganchic

    Поделиться публикацией
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 26
      +1
      Все логично. В Oracle тоже запрос с использованием динамических названий таблиц или других объектов сначала надо собрать в виде текста, а потом исполнить его в процедуре EXECUTE_IMMEDIATE.
        +4
        Да я согласен, ничего хитрого тут нет, но такие вопросы возникают с завидной регулярностью, по-этому решил описать.
          +1
          Создание динамических запросов оправдано только если они выполняются редко или вообще один раз. Иначе это неоправданная нагрузка на парсер. К тому же динамические запросы являются потенциальной дыркой для SQL injection.
            +13
            Ну если инъекция добралась до автособираемого sql-я, то это дыра в разработчике.
              +1
              Для MS SQL, например, это не так. Его оптимизатор позволяет сохранять планы даже для динамического SQL. План, грубо говоря — это распарсенное до атомарных операций выражение SQL, готовое к выполнению. После первого парсинга план помещается в кэш, после чего для его использования не нужно нагружать парсер, достаточно просто взять из кеша нужный запрос и подставить параметры.

              Понятно, что параметризовать именем таблицы нельзя, зато можно держать планы для всех таблиц в БД (их редко бывает больше сотни-другой) для среднего проекта.

              С другой стороны, наличие динамического sql, как правило, говорит о том, что что-то не так в датском королевстве вы используете БД не по назначению, заставляя ее выполнять задачи сервера приложений.
                0
                Вон оно как.

                А я тупой думал, что когда из клиента запрос на сервер шлю — сервер его сначала парсит и только потом выполняет. И вообще сдуру думал, что любой запрос, если он не в хранимой процедуре, для сервера динамический и его парсить надо.

                Спасибо за просветление.

                Так все таки, вообще нельзя с клиента запросы слать или они пока до сервера доходят волшебным образом парсятся?
              • НЛО прилетело и опубликовало эту надпись здесь
                  –1
                  вы еще скажите сэндмэйл )
                    0
                    Это насчет archive_prefix
                    0
                    Согласен, незаметил.
                      +1
                      а то даже может и суффикс =)
                      –1
                      > DECLARE template,archive_template VARCHAR(50);

                      тут надо archive_prefix вместо archive_template, опечатка вроде :)
                        0
                        и еще,
                        SET @archive_query:=CONCAT
                        («CREATE TABLE »,template," ENGINE=ARCHIVE AS
                        (SELECT * FROM ",current_table," )");

                        Тут обязательно после CONCAT-а нужно поставить открывающую скобу на этой же строке, иначе mysql будет ругаться, что мол нет такой процедуры CONCAT
                          0
                          Спасибо, поправил.
                        0
                        Народ, если не сложно, помогите плз разобраться как правильно сделать выборку, в инете своего случа не нашёл:

                        Рабочий пример:
                        SELECT column_name(s)
                        FROM table_name1
                        LEFT JOIN table_name2
                        ON table_name1.column_name = table_name2.column_name
                        WHERE table_name1.column_description LIKE table_name2.column_description2

                        Нада в LIKE добавить частичное включение, т.е. LIKE %table_name2.column_description2%
                          +8
                          Если у нас таблички t1, t2 для наглядности их JOIN без условия
                          select name1,d1,name2,d2 from t1 left join t2 on t1.name1=t2.name2

                          +---------+------+---------+----------+
                          | name1   | d1   | name2   | d2       |
                          +---------+------+---------+----------+
                          | name1   | d1   | name1   | x_d2_x   |
                          | name11  | d11  | name11  | x_d11_x  |
                          | name111 | d111 | name111 | x_d111_x |
                          +---------+------+---------+----------+
                          

                          То запрос будет выглядить так
                          select name1,d1,name2,d2 from t1 left join t2 on t1.name1=t2.name2 where LOCATE(d1,d2)

                          Логика тут такая LOCATE() возвращает позицию вхождения подстроки. Если внутри d2 содержится d1, то мы получим положительное число.
                          Получим соотвественно:
                          +---------+------+---------+----------+
                          | name11  | d11  | name11  | x_d11_x  |
                          | name111 | d111 | name111 | x_d111_x |
                          +---------+------+---------+----------+
                          

                          То есть запись где было d2=x_d2_x была отброшена.
                            0
                            спасибо, наглядно и доходчиво)
                          0
                          Вот если бы результат исполнения хранимых процедур можно было бы использовать в том же запросе, которых их вызывает, в виде таблицы, например, то это было бы дело.

                          А так приходится использовать для сложной логики выборок хранимые функции, которые замедляют исполнение запросов примерно в 10 раз.
                            0
                            Можно дергать процедуру и выгружать данные в темповую таблицу, конечно не очень красиво, но вполне рабочий вариант.
                              0
                              А какие проблемы.
                              Если в результате выполнения запроса получается выборка с одним полем — GROPUP_CONCAT на него INTO myvar.
                              Дальше собрать запрос CONCAT'ом и выполнить.
                              Если одно значение просто его в переменную.
                              Если набор строк с несколькими полями, то IMHO лучше на клиента вернуть и там обработать, а по результатам новый запрос отправить. Тут нередко и кэш может помочь.

                              С темповой таблицей поаккуратнее, она ведь может и на диске образоваться, естественно в самый подходящий момент, в неподходящий, когда нагрузки нет — ей в оперативке места хватит. :)
                            • НЛО прилетело и опубликовало эту надпись здесь
                                0
                                Ну их нафиг такие извраты, я лучше в php напишу что нибудь вроде: query(«SELECT FROM ?t WHERE ....», $table) :) Всяко удобнее))
                                  +1
                                  Действительно удобней. Но здесь задача стояла как такое сделать средствами MySQL
                                  0
                                  Так удобно, но все же надо стараться избавляться от динамических запросов. Их минус в том, что компилируются они при каждом вызове, тогда как обычные компилируются только при создании хранимой процедуры.
                                    0
                                    Ну почему же, они компилируются при PREPARE. Можно один раз PREPARE и кучу раз EXECUTE.

                                    Сам по себе это — инструмент, который можно использовать. И хорошо, что он есть. А уж как его использовать — решать разработчику.
                                  • НЛО прилетело и опубликовало эту надпись здесь

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

                                    Самое читаемое