达梦 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}), 10002);
      ipwork := REGEXP_REPLACE(ipwork, (^|:)0+([[:xdigit:]]{4}),    12);

      -- 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