计算表的每一列中的非空值 [英] Counting non-empty values in each column of a table

问题描述

我正在寻找数据迁移项目中丢失的数据，该报告将极大地帮助我.

I am looking for missing data in a data migration project, and this report will help me immensely.

给定一个MySQL表，我想计算该表每一行中的所有空值(NULL或'').输出将是列名的列表以及每列的空行或非空行的计数.我会手动将这些数据与源表进行比较-手动，因为我希望很少有计数与完全匹配，并且源表和导入表之间的列名完全不同.

Given a MySQL table, I would like to count all the empty (NULL or '') values in each row of that table. The output would be a list of column names and a count of empty or non-empty rows for each column. This data is something I would manually compare to the source tables - manually because I expect few counts to match up exactly and column names are completely different between the source and imported tables.

我大约有30张桌子要检查，有几张有100列.我可以从PC上直接访问MySQL，但是无权在带有数据库的服务器上运行任何脚本.

I have about 30 tables to check, a few with 100 columns. I have direct MySQL access from my PC, but no access to run any scripts on the server with the database.

表A的示例

Col1 Col2 Col3
'XX' NULL 'XX'
'XX' NULL ''
'XX' 'XX' 'XX'
'XX' ''   'XX'

我想要的报告是(对于带有"的计数为空的非空计数):

The report I would want is (for non-empty counts with '' counted as empty):

Col1: 4
Col2: 1
Col3: 3

推荐答案

您可以对每个表使用以下查询

You can use the following query for each table

SELECT COUNT(*), COUNT(col1) as col1, COUNT(col2) as col2
FROM TABLE1

要获取特定表的所有列，应运行查询

For getting all columns for a specific table you should run query

 select column_name from information_schema.columns where TABLE_NAME='TABLE1';

此查询的结果可用于自动生成查询，如第一个查询.

Results of this query you can use for auto generation queries like the first one.

这篇关于计算表的每一列中的非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！