mysql custom function (matching the IP interval to the database by IP to include the IP)

mysql custom string query function

A function is suitable for processing data formats (fields with','-partitioned data, as well as'-'connected intervals)

Example 1: Data "39570" needed to be judged for field "53678, 45672, 39543-39600" in the table
Example 2: Data "DD.B1.C1.A3,DD.B1.C1.A1-BA,DD.B1.C1.B0" needed to be judged in the field "DD.B1.C1.A3, DD.B1.A1-BA, DD.B1.C1.B0"

Scenarios for second use

1. The fields in the table store the interval between the channel number and the channel number to query the records in the table through a certain channel.
2. The fields in the table store the range of IP address and IP address to query the records in an IP table.
3....

Detailed Explanation of Three Specific Functions

The custom function uses three functions that MySQL comes with:
LENGTH(str) Gets the length of the str string and returns an integer
REPLACE(str,from_str,to_str). Returns the string str, and all occurrences of the string from_str are replaced by the string to_str.
SUBSTRING_INDEX(str,delim,count) Returns a substring after delim, which appears from the first count of the string str.
If count is a positive number, return all characters from the last separator to the left (from the left). If count is negative, return the last separator to all characters on the right (from the right)
REVERSE(str) Returns the string str that reverses the character order
REVERSE(str1,str2) Returns the string after str1 is connected to str2
       
a. Number of fields obtained by splitting the table with delimiters
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. String at the specified location after getting the field partitioned
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. Query string, whether return exists (1 exists 0 does not exist)
CREATE DEFINER=`root`@`localhost` FUNCTION `func_ip_search`(ip_string varchar(1000),ip varchar(20)) RETURNS int(11)
BEGIN
  -- Get the result of search.(The delimiter can also be passed in as a parameter.
	declare result varchar(50) default '';--Store the segmented strings
	declare ip_begin varchar(30) default '';--The left half of the interval
	declare ip_end varchar(30) default '';--The right half of the interval
	declare cnt int default 0;--Number of partitions
	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 --Is the judgment separate (non-interval)?
			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);--Splicing IP Top three addresses IP,You can modify it according to your needs.
			
			if(ip_begin <= ip && ip <= ip_end) then --Determine whether a string is in an interval
				return 1;
			end if;
		end if;
	end while;
  return 0;
END

Conclusion:

The first time I write a custom function, I always encounter related problems when I do a project. I have searched the Internet for half a day and I haven't found the relevant code. So I used a morning to write out all kinds of queries, which was relatively simple and did not optimize. When I had time to optimize this thing, I tried to adapt to all kinds of queries. At least at present, I can provide you with a solution.
Note: When you encounter problems, don't be afraid. Slowly analyze what you need. How much can you find on the Internet is related to your own needs. Then you can realize your own functional needs by combining all parties.

Keywords: MySQL

Added by bashaash on Sat, 25 May 2019 03:24:41 +0300