B6X0pqkY

1drop procedure if exists test_most_pop_field;
2DELIMITER $$
3CREATE PROCEDURE test_most_pop_field(IN tableName varchar(100))
4BEGIN
5
6DECLARE done INT DEFAULT 0;
7DECLARE sql_query VARCHAR(255);
8
9DECLARE cur CURSOR FOR 
10SELECT 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)
11FROM INFORMATION_SCHEMA.COLUMNS
12WHERE TABLE_SCHEMA = SCHEMA()
13AND TABLE_NAME = tableName;
14
15DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
16
17DROP TABLE IF EXISTS tmp_result;
18CREATE TEMPORARY TABLE tmp_result(columnName varchar(100), numberOfEmptyRows int);
19
20OPEN cur;
21
22REPEAT
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;
32UNTIL done END REPEAT;
33
34CLOSE cur;
35
36SELECT * FROM tmp_result ORDER BY numberOfEmptyRows DESC /*optionally LIMIT 1*/;
37
38END $$
39DELIMITER ;
40	
41CALL test_most_pop_field('yourTableName');
42	
43SELECT 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
49FROM table_name
50ORDER BY null_count DESC
51LIMIT 1;
52	
53SET GLOBAL group_concat_max_len = 4294967295;
54
55SELECT @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')
66FROM INFORMATION_SCHEMA.COLUMNS
67WHERE TABLE_SCHEMA = SCHEMA()
68      AND TABLE_NAME = 'table_name';
69
70PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;