SHARE
TWEET

Untitled

a guest Feb 21st, 2019 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. drop procedure if exists test_most_pop_field;
  2. DELIMITER $$
  3. CREATE PROCEDURE test_most_pop_field(IN tableName varchar(100))
  4. BEGIN
  5.  
  6. DECLARE done INT DEFAULT 0;
  7. DECLARE sql_query VARCHAR(255);
  8.  
  9. DECLARE cur CURSOR FOR
  10. SELECT CONCAT('INSERT INTO tmp_result(columnName, numberOfEmptyRows) SELECT "', COLUMN_NAME, '" AS columnName, SUM(IF(',COLUMN_NAME,' IS NULL OR ', COLUMN_NAME,' = "", 1, 0)) AS numberEmptyRows FROM ', TABLE_NAME)
  11. FROM INFORMATION_SCHEMA.COLUMNS
  12. WHERE TABLE_SCHEMA = SCHEMA()
  13. AND TABLE_NAME = tableName;
  14.  
  15. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  16.  
  17. DROP TABLE IF EXISTS tmp_result;
  18. CREATE TEMPORARY TABLE tmp_result(columnName varchar(100), numberOfEmptyRows int);
  19.  
  20. OPEN cur;
  21.  
  22. REPEAT
  23.   FETCH cur INTO sql_query;
  24.   IF NOT done THEN
  25.     BEGIN
  26.       SET @sql = sql_query; /*this extra step is necessary, cause otherwise it's a syntax error, don't ask me why*/
  27.       PREPARE stmt FROM @sql;
  28.       EXECUTE stmt;
  29.       DEALLOCATE PREPARE stmt;
  30.     END;
  31.   END IF;
  32. UNTIL done END REPEAT;
  33.  
  34. CLOSE cur;
  35.  
  36. SELECT * FROM tmp_result ORDER BY numberOfEmptyRows DESC /*optionally LIMIT 1*/;
  37.  
  38. END $$
  39. DELIMITER ;
  40.    
  41. CALL test_most_pop_field('yourTableName');
  42.    
  43. SELECT id,
  44.        (IF(col1 = NULL OR col1 = "", 1, 0) +
  45.         IF(col2 = NULL OR col3 = "", 1, 0) +
  46.         ...
  47.         IF(coln = NULL OR coln = "", 1, 0)
  48.        ) AS null_count
  49. FROM table_name
  50. ORDER BY null_count DESC
  51. LIMIT 1;
  52.    
  53. SET GLOBAL group_concat_max_len = 4294967295;
  54.  
  55. SELECT @query1 := CONCAT('SELECT id,
  56.                                  (',
  57.                          GROUP_CONCAT(CONCAT('IF(',COLUMN_NAME,' IS NULL OR ',
  58.                                                    COLUMN_NAME,' = "", 1, 0
  59.                                                 )
  60.                                            ')
  61.                                       SEPARATOR ' + '),
  62.                                 ') AS null_count
  63.                          FROM table_name
  64.                          ORDER BY null_count DESC
  65.                          LIMIT 1')
  66. FROM INFORMATION_SCHEMA.COLUMNS
  67. WHERE TABLE_SCHEMA = SCHEMA()
  68.       AND TABLE_NAME = 'table_name';
  69.  
  70. PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top