실제 IP 주소를 텍스트 또는 바이트 형식으로 저장하는 방법 어느 것이 더 좋을까요?

“텍스트”여기를 의미하기 때문에 VARCHAR(45)와 “바이트”를 의미하지 VARBINARY(16), 내가 말할 것이다 : 어느 쪽도 아니 .

다음 정보가 제공됩니다 ( IPv6의 Wikipedia 기사에서 ).

주소 표현
IPv6 주소의 128 비트는 각각 16 비트의 8 개 그룹으로 표시됩니다. 각 그룹은 4 개의 16 진수로 작성되며 그룹은 콜론 (:)으로 구분됩니다. 주소 2001 : 0db8 : 0000 : 0000 : 0000 : ff00 : 0042 : 8329가이 표현의 예입니다.

편의상 IPv6 주소는 가능한 경우 다음 규칙을 적용하여 더 짧은 표기법으로 축약 할 수 있습니다.

• 16 진 숫자 그룹에서 하나 이상의 선행 0이 제거됩니다. 이것은 일반적으로 선행 0을 모두 또는 전혀 수행하지 않습니다. 예를 들어 그룹 0042는 42로 변환됩니다.
• 연속적인 제로 섹션은 이중 콜론 (: :)으로 대체됩니다. 여러 번 사용하면 주소가 결정되지 않으므로 이중 콜론은 주소에서 한 번만 사용할 수 있습니다. RFC 5952는 생략 된 단일 섹션 0을 나타 내기 위해 이중 콜론을 사용하지 말 것을 권장합니다. [41]

이 규칙을 적용한 예 :

        초기 주소 : 2001 : 0db8 : 0000 : 0000 : 0000 : ff00 : 0042 : 8329
        각 그룹에서 모든 선행 0을 제거한 후 : 2001 : db8 : 0 : 0 : 0 : ff00 : 42 : 8329
        0의 연속 섹션을 생략 한 후 : 2001 : db8 :: ff00 : 42 : 8329

먼저 8 개의 VARBINARY(2)필드를 사용하여 8 개의 그룹을 나타냅니다. 그룹 5-8의 필드는 NULLIPv6 주소에만 사용되므로 필드 여야 합니다. 그룹 1-4의 필드는 NOT NULLIPv4 및 IPv6 주소 모두에 사용되는 필드 여야 합니다.

(A 하나에 그들을 결합 반대로 각 그룹의 독립을 유지함으로써 VARCHAR(45)하거나 VARBINARY(16)또는이 개 BIGINT두 가지 이점을 얻을 필드) :

1. 주소를 특정 표현으로 재구성하는 것이 훨씬 쉽습니다. 그렇지 않으면 연속적인 0 그룹을 (: :)으로 바꾸려면 파싱해야합니다. 그것들을 별개로 유지하면 이것을 용이하게하기 위해 간단한 IF/ IIF/ CASE문이 허용됩니다.
2. 당신도 가능하게하여 IPv6 주소 공간의 톤을 절약 할 수 ROW COMPRESSION또는 PAGE COMPRESSION. 두 가지 유형의 압축은 0x000 바이트를 차지하는 필드를 허용하므로 , 이러한 0 그룹은 모두 비용이 들지 않습니다. 반면에 위의 예제 주소를 위키피디아 따옴표로 저장 한 경우 중간에있는 모든 0의 3 개 세트는 전체 공간을 차지합니다 (작업을 수행하지 VARCHAR(45)않고 축소 표기법을 사용 하지 않는 한). 그러나 인덱싱에는 효과적이지 않을 수 있으며 전체 형식으로 재구성하기 위해 특별한 구문 분석이 필요하므로 옵션이 아니라고 가정하십시오 .-).

네트워크를 캡처해야하는 TINYINT경우 um, [Network]🙂 라는 필드를 작성하십시오.

네트워크 값에 대한 자세한 내용 은 IPv6 주소에 대한 다른 Wikipedia 기사의 정보입니다 .

네트워크

IPv6 네트워크는 2의 거듭 제곱 크기의 인접한 IPv6 주소 그룹 인 주소 블록을 사용합니다. 주소의 선행 비트 세트는 주어진 네트워크의 모든 호스트에 대해 동일하며 네트워크의 주소 또는 라우팅 접두어 라고합니다 .

네트워크 주소 범위는 CIDR 표기법으로 작성됩니다. 네트워크는 블록의 첫 번째 주소 (모두 0으로 끝남), 슬래시 (/) 및 접두사의 비트 단위 크기와 같은 10 진수 값으로 표시됩니다. 예를 들어 2001 : db8 : 1234 :: / 48로 작성된 네트워크는 2001 : db8 : 1234 : 0000 : 0000 : 0000 : 0000 : 0000에서 시작하여 2001 : db8 : 1234 : ffff : ffff : ffff : ffff에서 끝납니다. : ffff.

인터페이스 주소의 라우팅 접두사에는 CIDR 표기법으로 주소가 직접 표시 될 수 있습니다. 예를 들어 서브넷 2001 : db8 : a :: / 64에 연결된 2001 : db8 : a :: 123 주소의 인터페이스 구성은 2001 : db8 : a :: 123/64로 작성됩니다.

인덱싱의 경우 8 그룹 필드에 비 클러스터형 인덱스를 만들고, 포함하기로 결정한 경우 네트워크 필드를 만들 수 있습니다.

최종 결과는 다음과 같아야합니다.

CREATE TABLE [IPAddress]
(
  IPAddressID INT          NOT NULL IDENTITY(-2147483648, 1),
  Group8      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group7      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group6      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group5      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group4      VARBINARY(2) NOT NULL, -- both
  Group3      VARBINARY(2) NOT NULL, -- both
  Group2      VARBINARY(2) NOT NULL, -- both
  Group1      VARBINARY(2) NOT NULL, -- both
  Network     TINYINT      NULL
);

ALTER TABLE [IPAddress]
  ADD CONSTRAINT [PK_IPAddress]
  PRIMARY KEY CLUSTERED
  (IPAddressID ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

CREATE NONCLUSTERED INDEX [IX_IPAddress_Groups]
  ON [IPAddress] (Group1 ASC, Group2 ASC, Group3 ASC, Group4 ASC,
         Group5 ASC, Group6 ASC, Group7 ASC, Group8 ASC, Network ASC)
  WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE);

노트:

• BIGINTID 필드 에 사용할 계획 이지만 4,294,967,295 이상의 고유 한 값을 실제로 캡처 할 것으로 예상하십니까? 그렇다면 필드를 BIGINT로 변경하고 시드 값을 0으로 변경할 수도 있습니다. 그러나 그렇지 않으면 INT를 사용하고 최소값으로 시작하여 해당 데이터 유형의 전체 범위를 사용할 수 있습니다. .
• 원하는 경우 하나 이상의 NONpersisted Computed Columns를이 테이블에 추가하여 IPAddress의 텍스트 표현을 리턴 할 수 있습니다.
• Group * 필드는 테이블에서 의도적으로 8에서 1 로 내려 가서 SELECT *예상되는 순서로 필드를 리턴하도록 배열됩니다. 그러나 지수는 1에서 8 까지 올라가고 있습니다.

• 텍스트 형식으로 값을 나타내는 계산 열의 예 (완료되지 않은)는 다음과 같습니다.

  ALTER TABLE [IPAddress]
    ADD TextAddress AS (
  IIF([Group8] IS NULL,
      -- IPv4
      CONCAT(CONVERT(TINYINT, [Group4]), '.', CONVERT(TINYINT, [Group3]), '.',
        CONVERT(TINYINT, [Group2]), '.', CONVERT(TINYINT, [Group1]),
        IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')),
      -- IPv6
      LOWER(CONCAT(
        CONVERT(VARCHAR(4), [Group8], 2), ':', CONVERT(VARCHAR(4), [Group7], 2), ':',
        CONVERT(VARCHAR(4), [Group6], 2), ':', CONVERT(VARCHAR(4), [Group5], 2), ':',
        CONVERT(VARCHAR(4), [Group4], 2), ':', CONVERT(VARCHAR(4), [Group3], 2), ':',
        CONVERT(VARCHAR(4), [Group2], 2), ':', CONVERT(VARCHAR(4), [Group1], 2),
        IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')
       ))
     ) -- end of IIF
  );

  테스트:

  INSERT INTO IPAddress VALUES (127, 0, 0, 0, 4, 22, 222, 63, NULL); -- IPv6
  INSERT INTO IPAddress VALUES (27, 10, 1234, 0, 45673, 200, 1, 6363, 48); -- IPv6
  INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 2, 63, NULL); -- v4
  INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 137, 29, 16); -- v4
  
  SELECT [IPAddressID], [Group8], [Group1], [Network], [TextAddress]
  FROM IPAddress ORDER BY [IPAddressID];

  결과:

  IPAddressID   Group8   Group1   Network  TextAddress
  -----------   ------   ------   -------  ---------------------
  -2147483646   0x007F   0x003F   NULL     007f:0000:0000:0000:0004:0016:00de:003f
  -2147483645   0x001B   0x18DB   48       001b:000a:04d2:0000:b269:00c8:0001:18db/48
  -2147483644   NULL     0x003F   NULL     192.168.2.63
  -2147483643   NULL     0x001D   16       192.168.137.29/16

답변