Count how many fields are empty on a MYSQL line

Question

I have a registration table with 53 columns (address, phone, etc).

I wanted to set up a consultation that would bring me how many of these columns are empty or NULL, how can I do that?

Answer 1

I’m not sure I quite understand, but I believe that if you count the records using the clause COUNT(*) and where the columns are null IS NULL and empty ='', you can find the expected. For example:

SELECT COUNT(*) FROM tabela WHERE (coluna1 IS NULL or coluna1 = '') 
AND (coluna2 IS NULL or coluna2 = '') 
AND (coluna3 IS NULL or coluna3 = '') ... -- AND a quantidade de colunas que você desejar adiocionar na sua query.

I hope I’ve helped!

Answer 2

Based on what I understand (how many fields of each user were not filled in), I made the following code (take a look at this functional fiddle):

create table tabela_colunas (id int, coluna varchar(100));
CREATE TABLE tabela_contador (id_na_tabela int(10), total int(1));

SET @id_tabela:=1, @total:=(SELECT COUNT(*) FROM tabela_colunas);

insert into tabela_contador (id_na_tabela, total)
select id, 0 as total from tabela;

WHILE @id_tabela <= @total DO

    set @sql := concat('update tabela_contador tc join tabela t on tc.id_na_tabela = t.id set tc.total = tc.total + 1 where ', (select coluna from tabela_colunas where id = @id_tabela), ' is null or ', (select coluna from tabela_colunas where id = @id_tabela), ' = \'\'');

    PREPARE myquery FROM @sql;
    EXECUTE myquery;

    SET @id_tabela = @id_tabela + 1;    
END WHILE;

select * from tabela_contador;

I followed the following logic:

• I created a temporary table to store column names;
• I created another temporary table to store the totals of each occurrence of the original table;
• for each of the columns, one is added to the total (when id is the same and the field has no value);
• finally, consult the data table.