Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop procedure if exists test_most_pop_field;
- DELIMITER $$
- CREATE PROCEDURE test_most_pop_field(IN tableName varchar(100))
- BEGIN
- DECLARE done INT DEFAULT 0;
- DECLARE sql_query VARCHAR(255);
- DECLARE cur CURSOR FOR
- 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)
- FROM INFORMATION_SCHEMA.COLUMNS
- WHERE TABLE_SCHEMA = SCHEMA()
- AND TABLE_NAME = tableName;
- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
- DROP TABLE IF EXISTS tmp_result;
- CREATE TEMPORARY TABLE tmp_result(columnName varchar(100), numberOfEmptyRows int);
- OPEN cur;
- REPEAT
- FETCH cur INTO sql_query;
- IF NOT done THEN
- BEGIN
- SET @sql = sql_query; /*this extra step is necessary, cause otherwise it's a syntax error, don't ask me why*/
- PREPARE stmt FROM @sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END;
- END IF;
- UNTIL done END REPEAT;
- CLOSE cur;
- SELECT * FROM tmp_result ORDER BY numberOfEmptyRows DESC /*optionally LIMIT 1*/;
- END $$
- DELIMITER ;
- CALL test_most_pop_field('yourTableName');
- SELECT id,
- (IF(col1 = NULL OR col1 = "", 1, 0) +
- IF(col2 = NULL OR col3 = "", 1, 0) +
- ...
- IF(coln = NULL OR coln = "", 1, 0)
- ) AS null_count
- FROM table_name
- ORDER BY null_count DESC
- LIMIT 1;
- SET GLOBAL group_concat_max_len = 4294967295;
- SELECT @query1 := CONCAT('SELECT id,
- (',
- GROUP_CONCAT(CONCAT('IF(',COLUMN_NAME,' IS NULL OR ',
- COLUMN_NAME,' = "", 1, 0
- )
- ')
- SEPARATOR ' + '),
- ') AS null_count
- FROM table_name
- ORDER BY null_count DESC
- LIMIT 1')
- FROM INFORMATION_SCHEMA.COLUMNS
- WHERE TABLE_SCHEMA = SCHEMA()
- AND TABLE_NAME = 'table_name';
- PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement