MySql 用一句SQL语句查询IP地址是否在列表中（支持通配符）

来源：互联网发布：linux改变文件权限命令编辑：程序博客网时间：2024/05/22 15:39

在MySql中先创建一函数，目前MySql自定义函数只能返回一个值，无法以表形式返回

CREATE DEFINER = 'root'@'%' FUNCTION `split`(
f_string varchar(1000),
f_delimiter varchar(100),
f_order int
)
RETURNS varchar(1000) CHARSET utf8
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare result varchar(1000) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),reverse(f_delimiter),1));
if result="" then
set result="*";
end if;
return result;
END;

SQL查询语句：

Select * from (select IP,split(IP,'.',1) as IP1,split(IP,'.',2) as IP2,split(IP,'.',3) as IP3,split(IP,'.',4) as IP4 from wx_ip_list where VoteID=20 And IsEnabled=0 ) b where (split('172.30.30.17','.',1)=IP1 or IP1='*') and (split('172.30.30.17','.',2)=IP2 or IP2='*') and (split('172.30.30.17','.',3)=IP3 or IP3='*') and (split('172.30.30.17','.',4)=IP4 or IP4='*')

0 0