ip address(ipv4, ipv6)를 unsigned integer로 바꾸는 함수 (그 역도 가능하다)

• 너무 멍청하게도 127.0.0.1 이면 127001 로 저장하나? 했다…
• https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html#function_inet-aton

https://codingdog.tistory.com/entry/mysql-inetntoa-inetaton-함수-ipv4-주소를-어떻게-다룰까요 : 정리

inet_aton()

• IP a.b.c.d 입력하고 8비트씩 시프트하여 하나의 정수(A+B+C+D)로 결합
  • A = a * 256^3
  • B = b * 256^2
  • C = c * 256^1
  • D = d * 1 256^0

inet_ntoa()

• 결합된 정수(aton의 값, A+B+C+D)를 8비트씩 나눠서 각 옥텟을 추출하고, 점으로 구분된 문자열로 변환

• https://www.digikey.kr/ko/resources/conversion-calculators/conversion-calculator-number-conversion

  

mysql> SELECT INET_ATON('10.0.5.9');
        -> 167773449
mysql> SELECT INET_NTOA(167773449);
        -> '10.0.5.9'
        
        
mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'FDFE0000000000005A55CAFFFEFA9089'
mysql> SELECT HEX(INET6_ATON('10.0.5.9'));
        -> '0A000509'
        
        
mysql> SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'fdfe::5a55:caff:fefa:9089'
mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
        -> '10.0.5.9'

mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
        -> 'fdfe::5a55:caff:fefa:9089'
mysql> SELECT INET6_NTOA(UNHEX('0A000509'));
        -> '10.0.5.9'