Mysql 触发器调用存储功能实现自动插入预统计功能

最新推荐文章于 2024-05-29 15:33:12 发布

今天只卖大白菜

最新推荐文章于 2024-05-29 15:33:12 发布

阅读量919

点赞数

分类专栏： Mysql 数据库文章标签：数据库

本文链接：https://blog.csdn.net/cai454692590/article/details/84257735

版权

数据库同时被 2 个专栏收录

17 篇文章

订阅专栏

Mysql

15 篇文章

订阅专栏

本文介绍如何使用Mysql触发器和存储过程实现自动化的数据统计功能，通过实例演示了学生成绩表与统计表的联动更新，包括存储过程的创建、变量使用及触发器的设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql 触发器调用存储功能实现自动插入预统计功能

最近项目需要做一些统计功能，由于原数据表A的数据量带大，所以直接放弃了实时对原数据表A执行count的sql方案，打算建多一个预统计表来实现统计功能。

原本我想着在今天凌晨对原数据表A执行count函数来统计昨天的数据，然后直接insert对应的统计数据到统计表B，后面发现执行效率太低了，一个count要将近一分钟才会出结果。

于是，我就想到了使用触发器+存储过程的方式，在对原数据表A插入成功后，数据库自己对应把统计表B的数据进行插入或者更新操作。

一、建表

test_student_grade：学生成绩表
test_student_grade_total: 学生成绩统计记录表
建表SQL：

DROP TABLE IF EXISTS `test_student_grade`;
CREATE TABLE `test_student_grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_id` int(11) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `china_num` int(11) DEFAULT NULL,
  `english_num` int(11) DEFAULT NULL,
  `data_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `test_student_grade_total`;
CREATE TABLE `test_student_grade_total` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `exam_id` int(11) DEFAULT NULL,
  `china_student_num` int(11) DEFAULT NULL,
  `english_student_num` int(11) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `modify_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里建立了两个表，一个是专门记录每一位学生考试的成绩记录表(test_student_grade)，一个是专门统计每次考试每门科目学生人数的统计表(test_student_grade_total)。

我们的目的就是，每录入(insert)一次学生成绩在test_student_grade表里的时候，test_student_grade_total自动新增或者更新对应的统计信息。

test_student_grade表中的字段china_num，是指某位学生的一次考试的语文成绩(english_num同理是英语成绩)。
test_student_grade表中的字段data_time，是指考试时间。

test_student_grade_total表中的字段china_student_num，是指某次考试参与语文考试的学生人数(english_student_num同理是参与英语考试的人数)。
test_student_grade_total表中的字段create_date，是指考试时间。

二、初写存储过程

我希望调用存储过程，能够在统计表(test_student_grade_total)里，根据考试时间以及考试id，把对应科目的统计人数的字段自动加1。
根据上述需求，这里我们需要建立一个名为 p_insert_total(pa_exam_id,pa_total_type,pa_data_time)的存储过程，其中：
pa_exam_id 考试主键
pa_total_type 统计类型枚举（0:全部都有成绩 1:只有语文有成绩 2:只有英语有成绩）
pa_data_time 考试时间

根据上面的分析，我们就建立一个简单的存储过程

DELIMITER $
CREATE PROCEDURE p_insert_total (
	# 函数入参
	IN pa_exam_id  INT,
	IN pa_total_type INT,
	IN pa_data_time DATE
)
BEGIN
	# 输入执行的SQL
	INSERT INTO test_student_grade_total (
		exam_id,
		china_student_num,
		english_student_num,
		create_date,
		modify_date
	)
	VALUES
	(1, 0, 0, date(now()), now());
END $

上面的sql，就是建立一个p_insert_total的存储过程，调用这个函数会自动插入一条数据到test_student_grade_total表里面。

注意，由于存储过程需要写很多sql，平常分割我们习惯使用的；号分割，所以开头需要使用“DELIMITER $”来声明存储过程的结束，使用$符号来标识整个存储过程的结束。（就是用$代替平时的;号），这样中间的多处SQL可以使用；号进行分割。

如果入参是varchar 则声明的时候主要要给出长度，例如：

DELIMITER $
CREATE PROCEDURE p_create_table_wim_veh (
	IN pa_table_name VARCHAR (30)
)
BEGIN
	……
END $

三、初次调用存储过程

既然写好了存储过程，我们先试试调用刚刚写的存储过程。使用命令CALL调用存储过程：

CALL p_insert_total(1,1,date(now()))

执行完以后，可以看看表test_student_grade_total里面是否已经插入了统计数据
在这里插入图片描述

四、使用变量

既然，我们初步会使用了存储过程了。我们接下来就是，调用变量来完善这个存储过程。（下面的sql是重新建立一个存储过程，如果之前已经建立了存储过程，需要删除之前的存储过程，再执行下面的SQL）

DELIMITER $
CREATE PROCEDURE p_insert_total (
	# 函数入参
	IN pa_exam_id INT,
	IN pa_total_type INT,
	IN pa_data_time DATE
)
BEGIN
	# 声明变量 re_china_student_num 和 re_english_student_num
	DECLARE re_china_student_num, re_english_student_num INT DEFAULT NULL;
	# 输入执行的SQL
	# 根据时间调用统计数据并赋值给变量
	SELECT
		china_student_num INTO re_china_student_num
	FROM
		test_student_grade_total
	WHERE
		exam_id = pa_exam_id
	AND
		create_date >= pa_data_time
	AND
		create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);

	SELECT
		english_student_num INTO re_english_student_num
	FROM
		test_student_grade_total
	WHERE
		exam_id = pa_exam_id
	AND	
		create_date >= pa_data_time
	AND
		create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);

	# 根据re_china_student_num判断是否已经存在统计数据，存在则update，不存在则insert
	IF re_china_student_num IS NULL THEN
		INSERT INTO test_student_grade_total (
				exam_id,
				china_student_num,
				english_student_num,
				create_date,
				modify_date
			)
		VALUES
			(pa_exam_id, 0, 0, pa_data_time, now());
	ELSE
		IF pa_total_type = 0 THEN
			UPDATE
				test_student_grade_total
			SET
				china_student_num = china_student_num + 1,
				english_student_num = english_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		ELSEIF pa_total_type = 1 THEN
			UPDATE
				test_student_grade_total
			SET
				china_student_num = china_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		ELSE
			UPDATE
				test_student_grade_total
			SET
				english_student_num = english_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		END IF;	
	END IF;			
			
	
END $

声明了两个空值的变量 re_china_student_num 、 re_english_student_num
使用select语句根据日期（pa_data_time）、考试主键（exam_id）查询对应的科目的考试人数并且分别赋值给变量 re_china_student_num 、 re_english_student_num
根据re_china_student_num变量是否为null，判断test_student_grade_total表里是否已经存在了统计信息。
存在统计信息，根据检索条件，以及枚举入参，自动加一更新操作。不存在统计信息则初始化统计信息。（上面Insert由于偷懒所以逻辑不严谨，理论上来说，初始化的值应该是1，还要根据pa_total_type判断）

再次执行存储过程看看，有什么变化

CALL p_insert_total(1,0,date(now()))；
CALL p_insert_total(1,1,date(now()))；
CALL p_insert_total(1,2,date(now()))；

五、使用触发器

现在存储过程写好了，接下来我们只要在test_student_grade表上面，增加一个触发器来调用我们刚刚写的存储过程函数就打工告成了。

DELIMITER $
CREATE TRIGGER `insert_total` AFTER INSERT ON `test_student_grade` FOR EACH ROW
BEGIN
	IF (new.china_num IS NOT NULL && new.english_num IS NOT NULL) THEN
			CALL p_insert_total(new.exam_id, 0,new.data_time);
		ELSE IF(new.china_num IS NOT NULL && new.english_num IS NULL) THEN
			CALL p_insert_total(new.exam_id, 1,new.data_time);
		ELSE
			CALL p_insert_total(new.exam_id, 2,new.data_time);
		END IF;
	END IF;
END $

创建触发器，定义名称为 insert_total
触发器条件，插入数据成功后调用
如果当前插入值china_num不是为null且 english_num不是为null 则调用存储过程 p_insert_total(new.exam_id, 0,new.data_time);
其他亦然

六、换个思路，使用唯一键异常

截止到第五步，我们已经实现了我们所需要的功能了，然后其实根据上面，我们还有一个思路，建立在test_student_grade_total表里面把exam_id和create_date构建唯一键，然后那就是在存储过程直接insert操作，捕捉得到唯一键异常则直接编写update操作。

构建唯一键SQL:

ALTER TABLE `test_student_grade_total`
ADD UNIQUE INDEX `u_exam_id_to_create_date` (`exam_id`, `create_date`) ;

捕捉唯一键异常的存储过程写法：

DELIMITER $
CREATE PROCEDURE p_insert_total_ex (
	# 函数入参
	IN pa_exam_id INT,
	IN pa_total_type INT,
	IN pa_data_time DATE
) 
BEGIN
	# 声明捕捉唯一键异常时候处理方法 1062唯一键异常错误代码
	DECLARE CONTINUE HANDLER FOR 1062
	BEGIN
		IF pa_total_type = 0 THEN
			UPDATE
				test_student_grade_total
			SET
				china_student_num = china_student_num + 1,
				english_student_num = english_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		ELSEIF pa_total_type = 1 THEN
			UPDATE
				test_student_grade_total
			SET
				china_student_num = china_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		ELSE
			UPDATE
				test_student_grade_total
			SET
				english_student_num = english_student_num + 1
			WHERE
				exam_id = pa_exam_id
			AND
				create_date >= pa_data_time
			AND
				create_date < DATE_ADD(pa_data_time,INTERVAL 1 DAY);
		END IF;	
	
	END;

	INSERT INTO test_student_grade_total (
				exam_id,
				china_student_num,
				english_student_num,
				create_date,
				modify_date
			)
		VALUES
		(pa_exam_id, 0, 0, pa_data_time, now());	
END $

创建触发器：

DELIMITER $
CREATE TRIGGER `insert_total` AFTER INSERT ON `test_student_grade` FOR EACH ROW
BEGIN
	IF (new.china_num IS NOT NULL && new.english_num IS NOT NULL) THEN
			CALL p_insert_total_ex (new.exam_id, 0,new.data_time);
		ELSE IF(new.china_num IS NOT NULL && new.english_num IS NULL) THEN
			CALL p_insert_total_ex (new.exam_id, 1,new.data_time);
		ELSE
			CALL p_insert_total_ex (new.exam_id, 2,new.data_time);
		END IF;
	END IF;
END $