统计数据表各列非空值的数量


本文介绍如何统计数据表中每一列非空值的数量。

背景

Mysql数据中存在一张数据表A,A含有的数据列较多,例如包含100列,如何使用SQL统计出每一列中非空值的数量。

解决方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN
DECLARE done boolean default 0; /*标记循环结束*/

DECLARE field_name VARCHAR(30); /*数据表字段名称*/
DECLARE name VARCHAR(30); /*数据表名称*/
DECLARE cur_record CURSOR FOR SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = name; /*定义游标,数据集为数据表字段集合*/
#DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; /*定义循环结束条件*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /*定义循环结束条件-同上*/
SET name = 't_test_table'; /*数据表名称*/
/*临时数据表,存储数据表字段名称集合*/
CREATE TEMPORARY TABLE t_temp AS SELECT COLUMN_NAME AS field, 0 AS num FROM information_schema.COLUMNS WHERE table_name = name;

OPEN cur_record; /*打开游标*/
REPEAT
FETCH cur_record INTO field_name;
/*构造动态SQL*/
SET @sql = CONCAT("UPDATE t_temp SET num = (SELECT COUNT(*) FROM ", name, " WHERE ",field_name," !='' ) WHERE field = '", field_name, "'");
PREPARE stm FROM @sql;
EXECUTE stm;
DEALLOCATE PREPARE stm;
UNTIL done END REPEAT;
CLOSE cur_record; /*关闭游标*/

SELECT * FROM t_temp;
DROP TEMPORARY TABLE t_temp;
END

分析

思路
常规情况下统计数据表中某单个字段包含非空值的数量:

1
2
3
SELECT COUNT( * ) send_name
FROM `t_user_msg`
WHERE send_name != ''

◆ 取数据表所有字段;
◆ 构造SQL动态获取每个字段对应的空值数量;
基于以上的思路,使用存储过程,结合游标的使用完成对问题的解决。

注意事项

  • 如何获取数据表所有字段

    1
    SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = t_test_table;
  • DECLARE语句定义的局部变量必须在游标或句柄之前,而句柄的定义必须在游标定义之后,否则会产生错误提示。

  • Mysql不支持select @varColumnName from table 类似自定义变量作为字段名或者表名的操作,因此需要CONCAT连接查询语句与自定义变量,构造动态SQL语句:
    1
    2
    3
    4
    SET @sql = CONCAT("UPDATE t_temp SET num = (SELECT COUNT(*) FROM ", name, " WHERE ",field_name," !='' ) WHERE field = '", field_name, "'");        /*构造动态SQL*/
    PREPARE stm FROM @sql;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;

转载请注明出处:http://yurixu.com/blog/2016/09/02/FFmpeg音视频转换

— 完结 —

分享 本文总阅读量
< !-- add by yurixu 替换Google的jquery并且添加判断逻辑 -->