mysql自定义函数（通过IP去数据库匹配IP区间是否包含该IP）

mysql自定义字符串查询函数

一 函数适用处理数据格式(字段中有‘，’分割的数据，还有以’-‘连接的区间)

      例1：表中字段 "53678,45672,39543-39600"  需要判断的数据 "39570"

      例2：表中字段 "DD.B1.C1.A3,DD.B1.C1.A1-BA,DD.B1.C1.B0"  需要判断的数据 "DD.B1.C1.A7"

二 使用的场景

1.表中字段存储的是渠道号和渠道号的区间，去通过某一渠道查询表中的记录

2.表中字段存储的是IP地址和IP地址的区间，去通过某一IP查询表中的记录

3....

三 具体函数详解

该自定义函数用到了MySQL自带的三种函数：

LENGTH(str)      获取str字符串的长度，返回一个整数 

REPLACE(str,from_str,to_str)      返回字符串str，其字符串from_str的所有出现由字符串to_str代替

SUBSTRING_INDEX(str,delim,count)     返回从字符串str的第count个出现的分隔符delim之后的子串。

如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)

REVERSE(str)    返回颠倒字符顺序的字符串str

REVERSE(str1,str2)    返回str1与str2连接之后的字符串

       

a.通过分隔符分割表中字段得到的个数

CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_split_string_total`(ip_string varchar(1000),ip_delimiter varchar(5)) RETURNS int(11)
BEGIN
  -- Get the total number of given string.
  return 1+(length(ip_string) - length(replace(ip_string,ip_delimiter,'')));
END

b.获取字段分割之后，指定位置的字符串

CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_split_string`(ip_string varchar(1000),ip_delimiter varchar(5),ip_order int) RETURNS varchar(50) CHARSET utf8
BEGIN
  -- Get the separated number of given string.
  declare result varchar(50) default '';
  set result = reverse(substring_index(reverse(substring_index(ip_string,ip_delimiter,ip_order)),ip_delimiter,1));
  return result;
END

c.查询字符串，返回是否存在（1 存在 0 不存在）

CREATE DEFINER=`root`@`localhost` FUNCTION `func_ip_search`(ip_string varchar(1000),ip varchar(20)) RETURNS int(11)
BEGIN
  -- Get the result of search.（分隔符也可以作为参数传进来）
	declare result varchar(50) default '';--存储分割出来的字符串
	declare ip_begin varchar(30) default '';--区间的左半部分
	declare ip_end varchar(30) default '';--区间的右半部分
	declare cnt int default 0;--分割的个数
	declare i int default 0;

	set cnt = func_get_split_string_total(ip_string,',');
	while i < cnt
	do 
		set i = i + 1;
		set result = func_get_split_string(ip_string,',',i);
		
		if(length(result) = length(ip)) then --判断是是不是单独的（非区间）
			if(result = ip) then
				return 1;
			end if;

		else
			set ip_begin = func_get_split_string(result,'-',1);
			set ip_end = func_get_split_string(result,'-',2);
			set ip_end = concat(substring_index(ip_begin,'.',3),'.',ip_end);--拼接IP地址的前三位（适用于IP，可以根据需求自己修改）
			
			if(ip_begin <= ip && ip <= ip_end) then --判断字符串是否在区间内
				return 1;
			end if;
		end if;
	end while;
  return 0;
END

总结：

第一次写自定义函数，总要是做项目的时候遇到了相关的问题，网上查了半天没有查到相关的代码。于是自己就各种查询，用了一上午时间写出来的，写的比较简单，没有进行优化，等有时间把这个东西优化一下，争取能适应各种类型；目前至少能给大家提供一个解决的思路。

注：遇到问题的时候先不要害怕，慢慢分析自己需要什么，网上能查到多少跟自己需求相关的，然后自己再各方结合实现自己的功能需求。