Korzystam z rekurencyjnej procedury składowanej w MySQL do generowania tabeli tymczasowej o nazwie id_list, ale muszę użyć wyników tej procedury w kolejnym zapytaniu dotyczącym wyboru, więc nie mogę DROPtabeli tymczasowej w ramach procedury ...

BEGIN;

/* generates the temporary table of ID's */
CALL fetch_inheritance_groups('abc123',0);

/* uses the results of the stored procedure in the WHERE */
SELECT a.User_ID
FROM usr_relationships r 
INNER JOIN usr_accts a ON a.User_ID = r.User_ID 
WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list) 
GROUP BY r.User_ID;

COMMIT;

Podczas wywoływania procedury pierwsza wartość jest najwyższym identyfikatorem gałęzi, którą chcę, a druga to ta, tierktórej używa procedura podczas rekurencji. Przed pętlą rekurencyjną sprawdza, czy tier = 0i czy działa:

DROP TEMPORARY TABLE IF EXISTS id_list;
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;

Więc moje pytanie brzmi: jeśli nie DROPtymczasowy MEMORYstół pod koniec procedury lub w ramach mojej transakcji, jak długo ten stół będzie trwał w pamięci? Czy jest automatycznie usuwany po zakończeniu sesji, czy pozostanie w pamięci, dopóki połączenie będzie otwarte?

** NB Oczywistą odpowiedzią może być upuszczenie tabeli tymczasowej przed instrukcją zatwierdzenia, ale załóżmy przez chwilę, że nie mogę tego zrobić. *

EDYCJA : Aby być nieco bardziej precyzyjnym, co się stanie, jeśli zastosowane zostaną trwałe połączenia, czy tabela będzie utrzymywana przez wiele żądań? Jak dotąd wydaje się, że tak będzie i musielibyśmy wyraźnie usunąć tabelę tymczasową, aby zwolnić ten zasób.

AKTUALIZACJA : W oparciu o porady komentujących znalazłem sposób na dostosowanie mojej procedury składowanej, aby móc korzystać z tabeli TEMP MEMORY, ale móc ją jawnie DROPna końcu ...

Zamiast wywoływać procedurę przechowywaną i korzystać z pozostałej tabeli TEMP w celu zebrania wyników w rzeczywistym zapytaniu, zmieniłem CALLformat, aby użyć trzeciej OUTzmiennej, takiej jak:

CALL fetch_inheritance_groups('abc123','0',@IDS);

... następnie w ramach procedury składowanej dodałem drugi IF tier = 0na samym końcu z następującymi informacjami:

IF tier = 0
    THEN
    SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set;
    DROP TEMPORARY TABLE IF EXISTS id_list;
END IF;

Tak więc wynikiem procedury składowanej jest teraz rozdzielona przecinkami lista identyfikatorów, z którymi jest zgodny FIND_IN_SET, a więc końcowe zapytanie zostało zmodyfikowane, tak aby:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)

... jest teraz ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS)

Voila! Dzięki komentatorom za Twój wkład i za podanie mi powodu musiałem spróbować trochę mocniej :)

Zabawne w tabelach tymczasowych w procedurze przechowywanej jest nie tyle przejściowe istnienie tabeli (która zostaje porzucona po zakończeniu połączenia DB), ale zakres procedury przechowywanej.

Ktoś zadał to pytanie na StackOverflow: Zakres tabel tymczasowych utworzonych w procedurze przechowywanej MySQL . Minęło ponad rok i nikt nie odpowiedział na pytanie? Pozwól, że wyprostuję rekord. Faktem jest, że: tabela tymczasowa istnieje wewnątrz i na zewnątrz procedury składowanej, ale można wykonywać czynności z tabelą tymczasową tylko w zakresie uruchomionej procedury składowanej .

Według książki

Rozdział 5 zawiera podtytuł Powracanie zestawów wyników do innej procedury składowanej .

Mówi w ust. 2 na stronie 117:

Niestety, jedynym sposobem na przekazanie zestawu wyników z jednej procedury składowanej do drugiej jest przekazanie wyników za pomocą tabeli tymczasowej. Jest to niezręczne rozwiązanie b, a ponieważ tabela tymczasowa ma zasięg przez całą sesję, stwarza wiele takich samych problemów związanych z utrzymywalnością wywołanych przez użycie zmiennych globalnych. ale jeśli jeden zapisany program musi dostarczyć wyniki do innego zapisanego programu, wówczas najlepszym rozwiązaniem może być tymczasowa tabela.

Patrząc wstecz na pytanie StackOverflow , widzę kogoś o nazwie Procedura składowana z klienta mysql. Ponieważ klient mysql nie jest procedurą przechowywaną, wyników nie można manipulować na poziomie klienta mysql za pomocą DML, poza wykonaniem polecenia SELECT, aby wyświetlić wyniki. Ponieważ wywołujesz rekurencyjną procedurę składowaną, możesz mieć pewność, że tabela temp jest w pełni dostępna przez czas trwania połączenia DB .

Mam nadzieję, że to odpowiada na twoje pytanie.

AKTUALIZACJA 31.01.2014 11:26 EST

W swoim ostatnim komentarzu powiedziałeś

Jeśli zastosujemy trwałe połączenia, czy tabela MEMORY będzie trwała przez wiele WNIOSKÓW, i wydaje się, że tak będzie, więc ze względu na wydajność, zakładam, że użycie tej metody * WYMAGA nam jawnego OPUSZCZANIA tymczasowej tabeli MEMORY. Czy zakładam poprawnie?

Tak i nie. Mówię tak, ponieważ jest to jeden ze sposobów, aby to zrobić. Mówię „nie”, ponieważ innym sposobem jest:

CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Niezależnie od tego, który wybierzesz, operacja jest nadal taka sama, ponieważ TRUNCATE TABLE upuszcza i odtwarza tabelę. Nie zaszkodzi to innym połączeniom DB, ponieważ każde połączenie ma własną tabelę id_list.

W większości DBMS tabele tymczasowe zachowują się do końca bieżącego połączenia, chyba że określono inaczej lub chyba że istnieje jawne wycofanie transakcji (w niektórych systemach wycofanie może wpłynąć tylko na zawartość tabeli, pozostawiając sam obiekt do ponownego wypełnienia w razie potrzeby) . Tabela nie będzie (domyślnie) widoczna dla innych połączeń bez względu na to, jak długo trwa połączenie, które ją tworzy.

Szybkie skanowanie w Google wydaje się wskazywać, że tak działa mySQL.
_{( http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm stwierdza „domyślnie wszystkie tabele tymczasowe są usuwane przez MySQL po zakończeniu połączenia z bazą danych”)}

Istnieją jednak często sposoby zmiany tych zachowań. Na przykład w MS SQL Server możesz utworzyć tabelę tymczasową, która będzie widoczna dla wszystkich połączeń zamiast tylko bieżącej, nadając jej nazwę rozpoczynającą się od ##.

Zawsze upuszczam tabele tymczasowe, gdy tylko nie są już potrzebne, aby uniknąć możliwego zamieszania. Zostałem ugryziony wcześniej, gdy pula połączeń spowodowała utworzenie tabeli tymczasowej, powodując błędy, ponieważ tabela tymczasowa o tej samej nazwie została utworzona, ale nie została zniszczona w poprzedniej akcji, która korzystała z bieżącego połączenia.

CREATE TEMPORARY TABLE  IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT)
AS (
SELECT 
CONCAT(MONTHNAME(m1),' ',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence
FROM
(
SELECT 
('2014-01-01' - INTERVAL DAYOFMONTH('2014-01-01')-1 DAY) 
+INTERVAL m MONTH AS m1
FROM
(
SELECT @rownum:=@rownum+1 AS m FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
(SELECT @rownum:=-1) t0
) d1
) d2 
WHERE m1<= '2015-07-30'
ORDER BY m1
) ;

SELECT t.Months,A.OpenCount,A.CloseCount FROM Temp T
 LEFT JOIN ( SELECT  CONCAT(MONTHNAME(e.dtcdate),' ',YEAR(e.dtcdate)) AS Months,
 ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr 
 JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr='open' AND csr.dtcdate >='2014-01-01' AND csr.dtcdate <='2015-07-30' AND csr.ddlArea=e.ddlArea ) AS OpenCount
 ,
 ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr 
 JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr='Close' AND csr.dtcdate >='2014-01-01' AND csr.dtcdate <='2015-07-30' AND csr.ddlArea=e.ddlArea ) AS CloseCount

 FROM csrcrn_frmempengagreqs e 
 INNER JOIN master_detail m ON e.ddlcsstatus=m.masterDetailId 
  WHERE  e.dtcdate >='2014-01-01' AND e.dtcdate <='2015-07-30' 
 GROUP BY MONTH(e.dtcdate) ORDER BY e.dtcdate 
 ) A ON CONVERT(A.Months,CHAR(20))=CONVERT(T.Months,CHAR(20)) 
       ORDER BY T.Sequence; 
       DROP TEMPORARY TABLE  IF EXISTS temp;

/ * podane zapytanie daje wynik pomyślnie ... po umieszczeniu tego zapytania w USP, a następnie wyświetleniu błędu plz help me..proc podano poniżej * /

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_GetEngMonthlyChart_Test`$$

CREATE DEFINER=`root`@`%` PROCEDURE `usp_GetEngMonthlyChart_Test`(IN DateFrom DATE,IN DateTo DATE)
BEGIN
      -- SET @strWhere= CONCAT(' AND CSR.dtcInductionDate BETWEEN ''',CONVERT(DateFrom,DATE),''' AND ','''',CONVERT(DateTo,DATE),''''); 


    SET @strSql=CONCAT(' 

    CREATE TEMPORARY TABLE  IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT)
    AS (
    SELECT 
    CONCAT(MONTHNAME(m1),'' '',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence
    FROM
    (
    SELECT 
    (''',DateFrom,''' - INTERVAL DAYOFMONTH(''',DateFrom,''')-1 DAY) 
    +INTERVAL m MONTH AS m1
    FROM
    (
    SELECT @rownum:=@rownum+1 AS m FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
    (SELECT @rownum:=-1) t0
    ) d1
    ) d2 
    WHERE m1<= ''',DateTo,'''
    ORDER BY m1
    )' );   

         SET @strSql=CONCAT(@strSql,'; GO SELECT t.Months,A.OpenCount,A.CloseCount FROM Temp T
     LEFT JOIN ( SELECT  CONCAT(MONTHNAME(e.dtcdate),'' '',YEAR(e.dtcdate)) AS Months,
     ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr 
     JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr=''open'' AND csr.dtcdate >=''',DateFrom,
     ''' AND csr.dtcdate <=''',DateTo,''' AND csr.ddlArea=e.ddlArea ) AS OpenCount
     ,
     ( SELECT  COUNT(csr.ddlcsstatus) FROM csrcrn_frmempengagreqs csr 
     JOIN master_detail md ON md.masterDetailId=csr.ddlcsstatus WHERE md.abbr=''Close'' AND csr.dtcdate >=''',DateFrom,
     ''' AND csr.dtcdate <=''',DateTo,''' AND csr.ddlArea=e.ddlArea ) AS CloseCount

     FROM csrcrn_frmempengagreqs e 
     INNER JOIN master_detail m ON e.ddlcsstatus=m.masterDetailId 
      WHERE  e.dtcdate >=''',DateFrom,''' AND e.dtcdate <=''',DateTo,''' 
     GROUP BY MONTH(e.dtcdate) ORDER BY e.dtcdate 
     ) A ON CONVERT(A.Months,CHAR(20))=CONVERT(T.Months,CHAR(20)) 
           ORDER BY T.Sequence; 
           DROP TEMPORARY TABLE  IF EXISTS temp;'); 

    SELECT @strSql;
    PREPARE stmt FROM @strSql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END$$

DELIMITER ;

CALL usp_GetEngMonthlyChart_Test („2014-01-01”, „2015-07-30”)