queryDeletedIds.sql

  1. /*
  2. version 2
  3.  
  4. Incrementa la velocidad de cálculo de queryDeletedIds
  5.  
  6. Ejemplo:
  7. CALL queryDeletedIds('nombre_tabla', 'campo_id');
  8.  
  9. */
  10. DELIMITER $$
  11. DROP PROCEDURE IF EXISTS queryDeletedIds$$
  12. CREATE PROCEDURE queryDeletedIds(
  13. IN inTableName VARCHAR(64),
  14. IN inIdField VARCHAR(32)
  15. ) READS SQL DATA
  16. BEGIN
  17. DROP TABLE IF EXISTS tmp_deleted_start;
  18. DROP TABLE IF EXISTS tmp_deleted_final;
  19.  
  20. SET @query := REPLACE(REPLACE(CONCAT_WS(' ',
  21. 'CREATE TABLE tmp_deleted_start AS',
  22. 'SELECT @position := @position + 1 AS position,',
  23. 't1.{id} + 1 AS start_id',
  24. 'FROM (SELECT {id} FROM {table} UNION SELECT 0 ORDER BY 1) t1',
  25. 'LEFT JOIN {table} t2 ON t1.{id} + 1 = t2.{id}',
  26. 'CROSS JOIN (SELECT @position := 0) t3',
  27. 'WHERE t2.{id} IS NULL'),
  28. '{id}', inIdField),
  29. '{table}', inTableName);
  30. PREPARE stmt FROM @query;
  31. EXECUTE stmt;
  32. DEALLOCATE PREPARE stmt;
  33.  
  34. SET @query := REPLACE(REPLACE(CONCAT_WS(' ',
  35. 'CREATE TABLE tmp_deleted_final AS',
  36. 'SELECT @position := @position + 1 AS position,',
  37. 't1.{id} - 1 AS final_id',
  38. 'FROM (SELECT {id} FROM {table} UNION SELECT 2147483648 ORDER BY 1) t1',
  39. 'LEFT JOIN {table} t2 ON t1.{id} - 1 = t2.{id}',
  40. 'CROSS JOIN (SELECT @position := 0) t3',
  41. 'WHERE t2.{id} IS NULL AND t1.{id} > 1'),
  42. '{id}', inIdField),
  43. '{table}', inTableName);
  44. PREPARE stmt FROM @query;
  45. EXECUTE stmt;
  46. DEALLOCATE PREPARE stmt;
  47.  
  48. SELECT t1.start_id, t2.final_id
  49. FROM tmp_deleted_start t1 INNER JOIN
  50. tmp_deleted_final t2 USING(POSITION)
  51. ORDER BY t1.start_id;
  52. END$$
  53. DELIMITER ;
  54.  

Proinf.net