目录
问题描述:
对于如下表,统计它的bool值变化次数,即由0 -> 1的次数。(数据表会通过程序每隔一段时间从指定设备中存入一个bool状态量,如果设备中的值没有变化,那么读取的值也没有变化)
问题解答:
解法一: 通过group_contact和过程实现
1. group_contact()函数介绍
MySQL聚合函数 group_contact 的功能是可以将查询到的列字段值进行拼接,返回一个字符串,如下。
SELECT GROUP_CONCAT(value SEPARATOR'' ) as str_value from testcount
order by time desc ;
结果为:
PS :
在mysql中的group_concat函数默认支持的最大字符数为1024。当使用group_concat函数时,超出第1024字符的字符会全部丢失。
可以通过如下命令查看限制的最大长度
show variables like 'group_concat_max_len';
可以通过如下命令修改限制的最大长度
#修改成最大长度,自定义亦可
SET GLOBAL group_concat_max_len=4294967295;
SET SESSION group_concat_max_len=4294967295;
修改后重启服务生效
2 创建过程,加入group_contact实现统计功能
CREATE DEFINER=`root`@`localhost` PROCEDURE `countBool`
(IN `time_s` timestamp,OUT `countvalue` int)
BEGIN
#Routine body goes here...
#合并字段的长度限制,默认为1024
DECLARE str_1 varchar(1024);
DECLARE count_e int;
DECLARE count_str int;
#获取‘10’的单位长度
select length('10') INTO count_str;
SELECT
GROUP_CONCAT( testcount.`value` SEPARATOR'' ) into str_1
FROM
testcount
#限制条件,统计指定时间范围内的值
where testcount.time>time_s
ORDER BY time desc;
#获取去除掉‘10’后的字符串长度 并 求出 ‘10’ 的个数
select (length(str_1)-length(REPLACE(str_1,'10','')))/count_str into count_e;
select count_e into countvalue;
END
获取结果:
解法二: 通过触发器实现
在数据库中额外新建一个新表testcount2(可以手动建,也可以加入到触发器中,此处是手动),用于存放符合条件的记录。通过sql语句建立触发器,当旧表testcount中插入数据时,通过触发器的new对象进行value值的判断,符合要求则将记录插入,否则不做处理。
CREATE TRIGGER SumOfBool
AFTER INSERT ON testcount
FOR EACH ROW
if new.value=1 then
insert into testcount2(name,value,time) VALUES(new.name,new.value,new.time);
END IF;
之后进行结果测试,输入
insert into testcount(name,`value`,time) VALUES('hh','1',"2023-03-01 11:14:50");
insert into testcount(name,`value`,time) VALUES('hh','0',"2023-03-01 11:14:52");
只有value=1的记录被保存,之后可以通过统计testcount2表中的记录条数统计bool值的变化次数
select count(*) from testcount
PS:
需要注意的是,此处的sql语句都是基于mysql数据库语法所编写的,在mysql数据库中并没有inserted和deleted两张虚拟表,只有old和new两个记录对象,对象中保存的是一条对应的记录。