count null values of table from information_schema tables

Question

Is there any way to count null values in a column by using information_Schema tables? EG:

 SELECT table_name, table_rows
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA ='myschema'
 and table_name='mytable'
 and table_rows is null;

Clarifications from comments:

I can not get the number of null values directly from the table as the table is large and not even returning the count of null values, so I thought information_schema would be the best option to get count null values from table. But I don't know how to do this. Is there a way?

I have a table named cal and one of its column contains null values. I just need to count the number of those null values. As table is large I cannot get result by using select count(*) from tablename where column_name is null.

So I need a query which counts null values from Information_Schema somehow.

About the suggestion to add index:

But can I add an index on a column if column_type = mediumtext?

MySQL version 5.1.73 and table is MyISAM.

Answer 1

No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls.

You should have an index on the column and use the simple query instead:

-- if there isn't an index in place, add one:
alter table my_table
  add index (my_column_idx)
    (my_column) ;

select count(*)
from my_table
where my_column is null ;

---

But can i add an index on a column if column_type = mediumtext?

Well, no.

MySQL version 5.1.73 and table is MyISAM.

OK, now we have 3 problems.

First, you are in a very old, unsupported version of MySQL. Upgrade. At least to 5.5, if not to 5.6 or 5.7. This is not urgent of course and only partially related to the problem. Even at 5.7, we still can't have an index on a mediumtext column.

Second, you are using MyISAM? Still, in 2016? I guess it matches well with 5.1. Anyway, convert the tables and application to use InnoDB. That's not urgent or related to the issue either but you'll get benefits (transactions, foreign keys, etc). But no, even with InnoDB, we still can't have an index on a mediumtext column.

So, what to do? various ways - thrown not in any specific order:

• Add a TINYINT computed (GENERATED) column on something like CASE WHEN column IS NOT NULL THEN 1 END and index it. Requires upgrade to 5.7

• Add a TINYINT computed (VIRTUAL) column, as the previous suggestions, and index it. Requires replacing MySQL 5.1 with MariaDB 5.2 or 5.3 (or upgrading to 5.5 and then replacing with MariaDB 5.5 or 10+)

• Add a TINYINT column (like the computed ones) but fill the values and keep them correct by your application or triggers. This does not require upgrading but enforcing integrity in the application is not recommended. Considering the use of MyISAM though, integrity does not seems a priority.

• Banish the Nulls! Split the table into two, moving only the mediumtext column into the 2nd table (and the primary key). The 2nd table will only have rows for the non-null values of the column. Then you only need to count rows of that table. You can use the information schema for that! (or just count, MyISAM is fast in counting the whole table.) Does not require any upgrade but your applications will have to change to deal with the new, slightly altered table structure.

• use a prefix index (thnx to @Michael-sqlbot for the suggestion). This does not require any upgrade or modifications at all:

  -- a prefix index on the mediumtext column:
  alter table my_table
    add index (my_column_1_idx)
      (my_column(1)) ;      -- one is enough
  

  I haven't tested this but should work as it is. It may require to use WHERE LEFT(my_column, 1) IS NULL in your count query but you can test.

Answer 2

If you really want to hunt down NULL columns in all table rows, there is something you can do.

SCENARIO

You have a table mydb.mytable that looks like this

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    col1 VARCHAR(30) DEFAULT NULL,
    col2 VARCHAR(30) DEFAULT NULL,
    col3 VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (id)
);

You want to find out how many columns in mydb.mytable are NULL. Let's choose col1.

SELECT
    COUNT(1) - COUNT(col) col1_null_count
FROM mydb.mytable;

Now, let's say you want that count for all columns

SELECT
    COUNT(1) - COUNT(col)id_null_count,
    COUNT(1) - COUNT(col) col1_null_count,
    COUNT(1) - COUNT(col) col2_null_count,
    COUNT(1) - COUNT(col) col3_null_count
FROM mydb.mytable;

Of course, id_null_count will be zero(0) since id is defined NOT NULL.

YOUR ACTUAL QUESTION

In order to nail down these counts dynamically, you need INFORMATION_SCHEMA.COLUMNS.

Let's start with a query that tells you which columns allow for NULL

SELECT column_name FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytable'
AND is_nullable='YES';

Using this query, construct the count query mentioned earlier:

SET @DB='mydb';
SET @TB='mytable';
SET group_concat_max_len = 104857600;
SELECT CONCAT('SELECT ',select_columns,' FROM ',table_schema,'.',table_name)
INTO @sql FROM (SELECT GROUP_CONCAT(CONCAT('COUNT(1)-COUNT(',column_name,') ',
column_name,'_null_count')) select_columns FROM information_schema.columns
WHERE table_schema=@DB AND table_name=@TB
AND is_nullable='YES') A,information_schema.tables B
WHERE B.table_schema=@DB AND B.table_name=@TB;
PREPARE s FROM @sql; EXECUTE s\G DEALLOCATE PREPARE s;

This will perform the following

• Collect all nullable columns
• Construct SQL to count each column for NULL
• Executes the generate SQL

All you need do is assign @DB and @TB

Please keep in mind that this will generate the count for null columns using a single query and perform only one full table scan.

GIVE IT A TRY !!!