Mysql 需求实战 字段空置率统计

【对应需求】

需要统计一个schema中的所有的业务数据表中的相关的全表字段的空值率

【方法】
采用对应的存储过程进行相关的表统计并且插入数据至一个统计表
目的分析

1. 存储过程定义： ○ DELIMITER $$ 更改了MySQL的默认语句分隔符，以便于定义存储过程。 ○ CREATE PROCEDURE CollectTableStatistics
   定义了一个名为CollectTableStatistics的存储过程，它接受一个名为schemaName的输入参数，该参数用于指定要收集统计信息的数据库架构。
2. 局部变量和游标声明： ○ 定义了一些局部变量，如aTable、aSchema、columnName等，用于存储表和列的相关信息。 ○ 声明了两个游标cur和columnCur，分别用于选择指定架构的表和表的列信息。
3. 继续处理程序： ○ CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 定义了一个继续处理程序，当游标读取到末尾时，将done变量设置为TRUE。
4. 创建临时表： ○ 创建了一个名为TempColumnStats的临时表，用于存储中间统计数据。如果表已存在，则不创建，而是直接使用。
5. 初始化和打开主游标： ○ 使用OPEN cur;打开主游标，开始读取指定架构的表。
6. 主循环： ○ 使用LOOP和FETCH从cur游标中获取表信息。 ○ 对于每个表，计算表的总行数，并为表中的每一列计算空值率。
7. 计算行数： ○ 使用动态SQL（CONCAT和PREPARE语句）来构建并执行查询，以获取表的总行数。
8. 列信息循环： ○ 打开columnCur游标，获取当前表的列信息。 ○ 对于每一列，再次使用动态SQL计算空值率。
9. 插入统计数据： ○ 将收集到的统计数据（包括表名、列名、数据类型、列注释、总行数、空值率和运行日期时间）插入到TempColumnStats临时表中。
10. 关闭游标： ○ 在处理完所有表和列后，关闭cur和columnCur游标。
11. 插入最终统计数据： ○ 将TempColumnStats临时表中的数据插入到一个名为schema_table_statistics的永久表中。
12. 清理临时表： ○ 使用DROP TEMPORARY TABLE IF EXISTS TempColumnStats;删除临时表，以释放资源。
13. 结束存储过程： ○ END$$标志着存储过程的定义结束。 ○ DELIMITER ;将语句分隔符重置为默认的分号。 整个存储过程的目的是自动化地收集特定数据库架构中所有表的统计信息，包括每列的空值率，并将这些信息存储在一个永久表中，以便于分析和审计。使用游标和动态SQL使得这个过程非常灵活，可以适应各种不同的表结构。

【存储过程SQL】

DELIMITER $$
CREATE PROCEDURE CollectTableStatistics (
	IN schemaName VARCHAR ( 64 )) BEGIN
	DECLARE
		done INT DEFAULT FALSE;
	DECLARE
		aTable,
		aSchema,
		columnName,
		dataType,
		columnComment CHAR ( 64 );
	DECLARE
		totalRows BIGINT;
	DECLARE
		nullRate FLOAT;
/* 游标和处理结束游标的声明 */
	DECLARE
		cur CURSOR FOR SELECT
		table_schema,
		table_name 
	FROM
		information_schema.TABLES 
	WHERE
		table_schema = schemaName 
		AND table_type = 'BASE TABLE';
	DECLARE
		columnCur CURSOR FOR SELECT
		COLUMN_NAME,
		DATA_TYPE,
		COLUMN_COMMENT 
	FROM
		INFORMATION_SCHEMA.COLUMNS 
	WHERE
		TABLE_SCHEMA = aSchema 
		AND TABLE_NAME = aTable;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET done = TRUE;-- 创建临时表来存储中间统计数据
	CREATE TEMPORARY TABLE
	IF
		NOT EXISTS TempColumnStats (
			db_name VARCHAR ( 64 ),
			table_name VARCHAR ( 64 ),
			column_name VARCHAR ( 64 ),
			data_type VARCHAR ( 64 ),
			column_comment VARCHAR ( 255 ),
			total_rows BIGINT,
			null_rate FLOAT,
			run_datetime DATETIME 
		);
	TRUNCATE TABLE TempColumnStats;-- 打开主游标
	OPEN cur;
	read_loop :
	LOOP
			FETCH cur INTO aSchema,
			aTable;
		IF
			done THEN
				LEAVE read_loop;
			
		END IF;-- 对当前表计算行数
		
		SET @rowCountQuery = CONCAT( 'SELECT COUNT(*) FROM `', aSchema, '`.`', aTable, '` INTO @totalRows' );
		PREPARE stmt 
		FROM
			@rowCountQuery;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		
		SET totalRows = @totalRows;
		
		SET done = FALSE;-- 在这里声明每列信息的游标
		OPEN columnCur;
		column_loop :
		LOOP
				FETCH columnCur INTO columnName,
				dataType,
				columnComment;
			IF
				done THEN
					LEAVE column_loop;
				
			END IF;-- 计算当前列的空值率
			
			SET @nullRateQuery = CONCAT( 'SELECT 100 * SUM(CASE WHEN `', columnName, '` IS NULL THEN 1 ELSE 0 END) / ', totalRows, ' INTO @nullRate FROM `', aSchema, '`.`', aTable, '`' );
			PREPARE nullStmt 
			FROM
				@nullRateQuery;
			EXECUTE nullStmt;
			DEALLOCATE PREPARE nullStmt;
			
			SET nullRate = @nullRate;-- 将收集到的统计数据插入临时表
			INSERT INTO TempColumnStats ( db_name, table_name, column_name, data_type, column_comment, total_rows, null_rate, run_datetime )
			VALUES
				(
					aSchema,
					aTable,
					columnName,
					dataType,
					columnComment,
					totalRows,
					nullRate,
				NOW());
			
			SET done = FALSE;
			
		END LOOP column_loop;
		CLOSE columnCur;-- 更新完成标志的状态以结束外部循环
		
		SET done = FALSE;
		
	END LOOP read_loop;
	CLOSE cur;-- 将最终的统计数据插入永久表格并清理
	INSERT INTO schema_table_statistics SELECT
	* 
	FROM
		TempColumnStats;
	DROP TEMPORARY TABLE
IF
	EXISTS TempColumnStats;
	END$$
DELIMITER ;

【SQL表】对应的schema_table_statistics表结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for schema_table_statistics
-- ----------------------------
DROP TABLE IF EXISTS `schema_table_statistics`;
CREATE TABLE `schema_table_statistics`  (
  `db_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `table_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `column_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `data_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `column_comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `total_rows` bigint NULL DEFAULT NULL,
  `null_rate` float NULL DEFAULT NULL,
  `run_datetime` datetime NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;