达梦 8.1.128_ent_x86_64_ctm_pack4 兼容mysql INET6_ATON

达梦8 对应于ipv4 ipv6的函数兼容基本很少 需要自定义函数进行处理

CREATE OR REPLACE FUNCTION "SYSTEST"."ipguess"("ip_string" IN VARCHAR(32767))
RETURN NUMBER
AUTHID DEFINER
AS
BEGIN
   IF    REGEXP_LIKE(ip_string, '\d{1,3}(\.\d{1,3}){3}')                       THEN RETURN 4;
   ELSIF REGEXP_LIKE(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}') THEN RETURN 6;
   ELSE                                                                             RETURN null;
   END IF;
END;

判断ipv4或ipv6函数

将ipv6转 varbinary二进制处理

CREATE OR REPLACE FUNCTION "SYSTEST"."INET6_ATON"("ip_string" IN VARCHAR2(128))
RETURN VARBINARY(1000)
AUTHID DEFINER
-- INTEGER only holds 126 binary digits, IPv6 has 128
AS
   iptype number := ipguess(ip_string);
   ip     VARCHAR2(32);
   ipwork VARCHAR2(64);
   d      INTEGER;
   q      VARCHAR2(3);
BEGIN
   if iptype = 6 THEN
      IF ip_string = '::' THEN RETURN LPAD('0', 32, '0'); END IF;

      -- Sanity check
      ipwork := REGEXP_SUBSTR(ip_string, '[[:xdigit:]]{0,4}(\:[[:xdigit:]]{0,4}){0,7}');
      IF ipwork IS NULL THEN RETURN null; END IF;

	  
      -- Replace leading zeros
      -- (add a bunch to all of the pairs, then remove only the required ones)
      ipwork := REGEXP_REPLACE(ipwork, '(^|\:)([[:xdigit:]]{1,4})', '\1000\2');
      ipwork := REGEXP_REPLACE(ipwork, '(^|\:)0+([[:xdigit:]]{4})',    '\1\2');

      -- Groups of zeroes
      -- (total length should be 32+Z, so the gap would be the zeroes)
      ipwork := REPLACE(ipwork, '::', 'Z');
      ipwork := REPLACE(ipwork, ':');
      
      ipwork := REPLACE(ipwork, 'Z', LPAD('0', 33 - LENGTH(ipwork), '0'));

      return  convert (varbinary(130) , ipwork);
   ELSE
      RETURN null;
   END IF;    
END INET6_ATON;

select  INET6_ATON('fdfe::5a55:caff:fefa:9089')

返回：0xFDFE0000000000005A55CAFFFEFA9089