Процедура динамического SQL внутри функции

Я пытаюсь создать хранимую процедуру, которая, среди прочего, обновляет таблицу информацией из другой таблицы:

UPDATE table1 T1, table2 T2
set T1.rank = T1.rank + T2.rank
T1.tags = merge(T1.tags, T2.tags)
WHERE T1.id = T2.id

теги — это строка слов, разделенных запятыми, а слияние — это функция (а не процедура), которая разбивает строки на временные таблицы и возвращает новую строку с уникальными тегами, используя подготовленные операторы. Я знаю, что нельзя использовать подготовленные операторы внутри функций, но часть, которая их использует, находится внутри процедуры, которая вызывается внутри функции.

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

Любой возможный способ добиться этого?


mysql stored-procedures dynamic-sql prepared-statement stored-functions
person NeoZeroX21    schedule 28.03.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете сделать это (обновление с объединением тегов) в одном выражении с помощью чистого SQL. Хитрость заключается в использовании числовой таблицы, которая может быть создана и заполнена за считанные секунды (также с помощью одного оператора SQL).

Вот ваше UPDATE заявление

UPDATE table1 t1 JOIN table2 t2
    ON t1.id = t2.id JOIN
(
  SELECT id, GROUP_CONCAT(DISTINCT tag ORDER BY tag) tags
    FROM
  (
     SELECT a.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.id), ',', -1) tag
       FROM
    (
       SELECT t1.id, CONCAT(t1.tags, ',', t2.tags) tags
         FROM table1 t1 JOIN table2 t2
           ON t1.id = t2.id
    ) a CROSS JOIN tally n
     WHERE n.id <= 1 + (LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')))
       AND n.id < 100 -- change that number to accommodate max possible number of tags 
  ) b
   GROUP BY id
) c ON t1.id = c.id
   SET t1.tags = c.tags,
       t1.rank = t1.rank + t2.rank

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

CREATE TABLE tally (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally 
SELECT NULL
FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
      , (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
      , (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
      , (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
      , (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e
;

Вот демонстрация SQLFiddle.

person peterm    schedule 26.06.2013