Come usi la clausola “WITH” in MySQL?

Sto convertendo tutte le mie query SQL Server in MySQL e le mie query che hanno WITH in esse falliscono tutte. Ecco un esempio:

 WITH t1 AS ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid = article.article_ownerid INNER JOIN category ON article.article_categoryid = category.catid WHERE article.article_isdeleted = 0 ) SELECT t1.* FROM t1 ORDER BY t1.article_date DESC LIMIT 1, 3 

MySQL non supporta la clausola WITH (CTE in linguaggio di SQL Server, Factoring di subquery in Oracle), quindi non ti resta che usare:

  • Tavoli TEMPORANEI
  • Tavoli DERIVED
  • viste in linea (effettivamente ciò che rappresenta la clausola WITH – sono intercambiabili)

La richiesta per la funzione risale al 2006.

Come accennato, hai fornito un esempio negativo: non è necessario eseguire una sottoselezione se non si modifica in alcun modo l’output delle colonne:

  SELECT * FROM ARTICLE t JOIN USERINFO ui ON ui.user_userid = t.article_ownerid JOIN CATEGORY c ON c.catid = t.article_categoryid WHERE t.published_ind = 0 ORDER BY t.article_date DESC LIMIT 1, 3 

Ecco un esempio migliore:

 SELECT t.name, t.num FROM TABLE t JOIN (SELECT c.id COUNT(*) 'num' FROM TABLE c WHERE c.column = 'a' GROUP BY c.id) ta ON ta.id = t.id 

Mysql Developers Team ha annunciato che la versione 8.0 avrà Common Table Expressions in MySQL (CTEs) . Quindi sarà ansible scrivere query come questa:

 WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<10 ) SELECT * FROM my_cte; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0,00 sec) 

In Sql l’istruzione with specifica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). Può essere utilizzato per query ricorsive, ma in questo caso, specifica come sottoinsieme. Se mysql consentisse sottoprogrammi, proverei

 select t1.* from ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid WHERE article.article_isdeleted = 0 ) t1 ORDER BY t1.article_date DESC Limit 1, 3 

La funzione ‘Common Table Expression’ non è disponibile in MySQL, quindi devi andare a fare una vista o una tabella temporanea da risolvere, qui ho usato una tabella temporanea.

La procedura memorizzata menzionata qui risolverà la tua necessità. Se voglio ottenere tutti i membri del mio team e i loro membri associati, questa stored procedure aiuterà:

 ---------------------------------- user_id | team_id ---------------------------------- admin | NULL ramu | admin suresh | admin kumar | ramu mahesh | ramu randiv | suresh ----------------------------------- 

Codice:

 DROP PROCEDURE `user_hier`// CREATE DEFINER=`root`@`localhost` PROCEDURE `user_hier`(in team_id varchar(50)) BEGIN declare count int; declare tmp_team_id varchar(50); CREATE TEMPORARY TABLE res_hier(user_id varchar(50),team_id varchar(50))engine=memory; CREATE TEMPORARY TABLE tmp_hier(user_id varchar(50),team_id varchar(50))engine=memory; set tmp_team_id = team_id; SELECT COUNT(*) INTO count FROM user_table WHERE user_table.team_id=tmp_team_id; WHILE count>0 DO insert into res_hier select user_table.user_id,user_table.team_id from user_table where user_table.team_id=tmp_team_id; insert into tmp_hier select user_table.user_id,user_table.team_id from user_table where user_table.team_id=tmp_team_id; select user_id into tmp_team_id from tmp_hier limit 0,1; select count(*) into count from tmp_hier; delete from tmp_hier where user_id=tmp_team_id; end while; select * from res_hier; drop temporary table if exists res_hier; drop temporary table if exists tmp_hier; end 

Questo può essere chiamato usando:

 mysql>call user_hier ('admin')// 

Ho seguito il link condiviso da lisachenko e ho trovato un altro link a questo blog: http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html

Il post espone i modi di emulare i 2 usi di SQL WITH. Davvero una buona spiegazione su come questi funzionano per fare una query simile a SQL WITH.

1) Utilizzare WITH in modo da non dover eseguire la stessa sub query più volte

 CREATE VIEW D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR); SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND FROM D AS D1, D AS D2 WHERE D1.YEAR = D2.YEAR-1; DROP VIEW D; 

2) Le query ricorsive possono essere eseguite con una stored procedure che rende la chiamata simile a una ricorsiva con query.

 CALL WITH_EMULATOR( "EMPLOYEES_EXTENDED", " SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS FROM EMPLOYEES WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL) ", " SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID GROUP BY M.ID, M.NAME, M.MANAGER_ID ", "SELECT * FROM EMPLOYEES_EXTENDED", 0, "" ); 

E questo è il codice o la stored procedure

 # Usage: the standard syntax: # WITH RECURSIVE recursive_table AS # (initial_SELECT # UNION ALL # recursive_SELECT) # final_SELECT; # should be translated by you to # CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT, # final_SELECT, 0, ""). # ALGORITHM: # 1) we have an initial table T0 (actual name is an argument # "recursive_table"), we fill it with result of initial_SELECT. # 2) We have a union table U, initially empty. # 3) Loop: # add rows of T0 to U, # run recursive_SELECT based on T0 and put result into table T1, # if T1 is empty # then leave loop, # else swap T0 and T1 (renaming) and empty T1 # 4) Drop T0, T1 # 5) Rename U to T0 # 6) run final select, send relult to client # This is for *one* recursive table. # It would be possible to write a SP creating multiple recursive tables. delimiter | CREATE PROCEDURE WITH_EMULATOR( recursive_table varchar(100), # name of recursive table initial_SELECT varchar(65530), # seed aka anchor recursive_SELECT varchar(65530), # recursive member final_SELECT varchar(65530), # final SELECT on UNION result max_recursion int unsigned, # safety against infinite loop, use 0 for default create_table_options varchar(65530) # you can add CREATE-TABLE-time options # to your recursive_table, to speed up initial/recursive/final SELECTs; example: # "(KEY(some_column)) ENGINE=MEMORY" ) BEGIN declare new_rows int unsigned; declare show_progress int default 0; # set to 1 to trace/debug execution declare recursive_table_next varchar(120); declare recursive_table_union varchar(120); declare recursive_table_tmp varchar(120); set recursive_table_next = concat(recursive_table, "_next"); set recursive_table_union = concat(recursive_table, "_union"); set recursive_table_tmp = concat(recursive_table, "_tmp"); # Cleanup any previous failed runs SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table, ",", recursive_table_next, ",", recursive_table_union, ",", recursive_table_tmp); PREPARE stmt FROM @str; EXECUTE stmt; # If you need to reference recursive_table more than # once in recursive_SELECT, remove the TEMPORARY word. SET @str = # create and fill T0 CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_SELECT); PREPARE stmt FROM @str; EXECUTE stmt; SET @str = # create U CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; SET @str = # create T1 CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; if max_recursion = 0 then set max_recursion = 100; # a default to protect the innocent end if; recursion: repeat # add T0 to U (this is always UNION ALL) SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; # we are done if max depth reached set max_recursion = max_recursion - 1; if not max_recursion then if show_progress then select concat("max recursion exceeded"); end if; leave recursion; end if; # fill T1 by applying the recursive SELECT on T0 SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_SELECT); PREPARE stmt FROM @str; EXECUTE stmt; # we are done if no rows in T1 select row_count() into new_rows; if show_progress then select concat(new_rows, " new rows found"); end if; if not new_rows then leave recursion; end if; # Prepare next iteration: # T1 becomes T0, to be the source of next run of recursive_SELECT, # T0 is recycled to be T1. SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp); PREPARE stmt FROM @str; EXECUTE stmt; # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next); PREPARE stmt FROM @str; EXECUTE stmt; # empty T1 SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next); PREPARE stmt FROM @str; EXECUTE stmt; until 0 end repeat; # eliminate T0 and T1 SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; # Final (output) SELECT uses recursive_table name SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; # Run final SELECT on UNION SET @str = final_SELECT; PREPARE stmt FROM @str; EXECUTE stmt; # No temporary tables may survive: SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table); PREPARE stmt FROM @str; EXECUTE stmt; # We are done :-) END| delimiter ; 

Quella funzione è chiamata un’espressione di tabella comune http://msdn.microsoft.com/en-us/library/ms190766.aspx

Non sarai in grado di fare esattamente la cosa in mySQL, la cosa più semplice sarebbe probabilmente creare una vista che rispecchia quel CTE e basta selezionare dalla vista. Puoi farlo con sottoquery, ma questo si realizzerà molto male. Se si esegue una CTE che esegue la ricorsione, non so come si sarebbe in grado di ricrearla senza utilizzare stored procedure.

EDIT: Come ho detto nel mio commento, l’esempio che hai pubblicato non ha bisogno di un CTE, quindi devi averlo semplificato per la domanda in quanto può essere semplicemente scritto come

 SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid WHERE article.article_isdeleted = 0 ORDER BY article_date DESC Limit 1, 3