mysql自定义函数实现数据正则处理

数据格式

localhost 101_CPU_load 2019-01-24 10:06:36 load1=0.010;10.000;20.000;0; load5=0.020;10.000;20.000;0; load15=0.050;10.000;20.000;0;

192.168.*.* 103_Sys_users 2019-01-21 05:34:23 users=0;2;5;0

192.168.*.* 102_Disk_root 2019-01-23 08:05:18 /=125063MB;921142;951847;0;1023492

主要是对load1=0.010;10.000;20.000;0; load5=0.020;10.000;20.000;0; load15=0.050;10.000;20.000;0;处理

load1 相当于key

0.010;10.000;20.000;0;相当于value

首先创建两个函数一个是key处理，一个是value处理

key处理函数

CREATE DEFINER=`root`@`192.168.*.*` FUNCTION `getPerfKey`(perf_data text,perf_length int) RETURNS text CHARSET utf8
BEGIN
	#Routine body goes here...

DECLARE perfdata  text;
DECLARE perflength  INT;
DECLARE allresult text;

SET perfdata = perf_data;
SET perflength = perf_length;
IF perflength = 1 THEN 
SELECT 
	substring_index(
		substring_index(
			substring_index(
				perfdata,
				' ',
				1
			),
			';',
			1
		),
		'=' ,1
	) into allresult;

end if;
IF perflength = 2 THEN 
SELECT 
	substring_index(
		substring_index(
			SUBSTRING_INDEX(
				substring_index(
					perfdata,
					' ' ,2
				),
				' ',
				-1
			),
			';',
			1
		),
		'=' ,1
	) into allresult;

end if;
IF perflength = 3 THEN 
SELECT 
	substring_index(
		substring_index(
			SUBSTRING_INDEX(
				substring_index(
					perfdata,
					' ' ,3
				),
				' ',
				-1
			),
			';',
			1
		),
		'=' ,1
	) into allresult;

end if;

RETURN allresult;

END

value处理函数

CREATE DEFINER=`root`@`192.168.*.*` FUNCTION `getPerfValue`(perf_data text,perf_length int) RETURNS text CHARSET utf8
BEGIN
	#Routine body goes here...

DECLARE perfdata  text;
DECLARE perflength  INT;
DECLARE allresult text;

SET perfdata = perf_data;
SET perflength = perf_length;
IF perflength = 1 THEN 
SELECT 
	substring_index(
		substring_index(
			substring_index(
				perfdata,
				' ',
				1
			),
			';',
			1
		),
		'=' ,-1
	) into allresult;

end if;
IF perflength = 2 THEN 
SELECT 
	substring_index(
		substring_index(
			SUBSTRING_INDEX(
				substring_index(
					perfdata,
					' ' ,2
				),
				' ',
				-1
			),
			';',
			1
		),
		'=' ,-1
	) into allresult;

end if;
IF perflength = 3 THEN 
SELECT 
	substring_index(
		substring_index(
			SUBSTRING_INDEX(
				substring_index(
					perfdata,
					' ' ,3
				),
				' ',
				-1
			),
			';',
			1
		),
		'=' ,-1
	) into allresult;

end if;

RETURN allresult;

END

调用函数

SELECT
	nagios_hosts.display_name,
	nagios_services.display_name,
	nagios_servicechecks.end_time,
	getPerfKey(nagios_servicechecks.perfdata,1),
	getPerfValue(nagios_servicechecks.perfdata,1),
	getPerfKey(nagios_servicechecks.perfdata,2),
	getPerfValue(nagios_servicechecks.perfdata,2),
	getPerfKey(nagios_servicechecks.perfdata,3),
	getPerfValue(nagios_servicechecks.perfdata,3)
FROM
	nagios_hosts
LEFT JOIN nagios_services ON nagios_hosts.host_object_id = nagios_services.host_object_id
LEFT JOIN nagios_servicechecks ON nagios_services.service_object_id = nagios_servicechecks.service_object_id
WHERE
	nagios_hosts.display_name = '192.168.*.*'
AND nagios_services.display_name = '101_CPU_load';