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
总结:
第一次写自定义函数,总要是做项目的时候遇到了相关的问题,网上查了半天没有查到相关的代码。于是自己就各种查询,用了一上午时间写出来的,写的比较简单,没有进行优化,等有时间把这个东西优化一下,争取能适应各种类型;目前至少能给大家提供一个解决的思路。
注:遇到问题的时候先不要害怕,慢慢分析自己需要什么,网上能查到多少跟自己需求相关的,然后自己再各方结合实现自己的功能需求。