mysql map_get function,用于解析map结构数据，根据key返回相对应value

1.目的

mysql的列是固定的，不支持存储如Map 结构的数据，但现在我们的需求是希望有一个 ext Map的扩展列，可以存储Map结构的数据，并且可以在mysql里面进行运算.(即schema free)。所以解决方案是创建一个map_get()函数，可以根据key得到对应的value

函数功能:

/*
 * 用于解析map结构的数据,根据传入的inputKey返回相对应的value
 * 
 * @params
 * map: 自定义键值对的Map数据格式，输入例子: username:badqiu,age:25,sex:F
 * key: 输入key
 * @return 返回key对应的value,如果没有值，则返回null 
 */
map_get(map varchar2,key varchar2)

 

2.实现

set global log_bin_trust_function_creators = 1;

/*
 map_get(map,inputKey)函数
 用于解析map结构的数据,根据传入的inputKey返回相对应的value
*/

DROP FUNCTION IF EXISTS map_get;
DELIMITER $$    -- 定义函数分隔符，必须要有，可以不是$$
CREATE FUNCTION map_get( map varchar(5000), inputKey varchar(300) )
RETURNS VARCHAR(255)          
BEGIN
	DECLARE rowSeperator char(1) default ',';  -- 行分隔符
	DECLARE fieldSeperator char(1) default ':';  --  键值对分隔符

	DECLARE tempMap varchar(255) default map;
	DECLARE isEnd boolean default false;

	DECLARE rowIndex int default 0;
	DECLARE pair varchar(255);
	DECLARE pairIndex varchar(255);
	DECLARE strKey varchar(255);
	DECLARE strValue varchar(255);

	WHILE isEnd = false do
		
		set rowIndex = locate(rowSeperator,tempMap);
		if rowIndex > 0 then
			set pair = substring(tempMap,1,rowIndex-1);
			set tempMap = substring(tempMap,rowIndex+1,9999999);
		else
			set pair = tempMap;
			set isEnd = true;
		end if;

		set pairIndex = locate(fieldSeperator,pair);
		if pairIndex > 0 then
			set strKey = substring(pair,1,pairIndex-1);
			set strValue = substring(pair,pairIndex+1,9999999);
			if inputKey = strKey then
				return strValue;
			end if;
		end if;
		
	END WHILE;

	return null;                              
END $$
DELIMITER;                                          

DROP FUNCTION IF EXISTS map_get_number;
DELIMITER $$    -- 定义函数分隔符，必须要有，可以不是$$
CREATE FUNCTION map_get_number( map varchar(5000), inputKey varchar(300) )
RETURNS DECIMAL
BEGIN
return cast(map_get(map,inputKey) AS DECIMAL );
END $$
DELIMITER;

 

3.测试

select map_get('username:badqiu','username')
union all
select map_get('username:badqiu,age:100','not exist')
union all
select map_get_number('username:badqiu,age:200','age')
union all
select map_get_number('username:badqiu,age:200','agexxxxx')
union all
select map_get('username:badqiu,age:100','age');