How to convert an IPv6 address from string format to number using the INET6_ATON() function?

INET6_ATON(ip) is a MySQL built-in function that converts an IPv6 address from string format to number. But an IPv6 number is too big to be represented in decimal digits. So it is stored as a byte sequence, and you need to convert it in hexadecimal digits to see it. For example:

SELECT INET6_ATON('fdfe::5a55:caff:fefa:9089'),
  HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
  -- +-----------------------------------------+----------------------------------------------+
  -- | INET6_ATON('fdfe::5a55:caff:fefa:9089') | HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')) |
  -- +-----------------------------------------+----------------------------------------------+
  -- | ??      ZU??????                        | FDFE0000000000005A55CAFFFEFA9089             |
  -- +-----------------------------------------+----------------------------------------------+

INET6_NTOA also works on IPv4 addresses. For example:

SELECT INET6_ATON('10.0.5.9'), HEX(INET6_ATON('10.0.5.9'));
  -- +------------------------+-----------------------------+
  -- | INET6_ATON('10.0.5.9') | HEX(INET6_ATON('10.0.5.9')) |
  -- +------------------------+-----------------------------+
  -- |                        | 0A000509                    |
  -- +------------------------+-----------------------------+

Reference information of the INET6_ATON() function:

INET6_ATON(ip): bytes
  Converts an IPv6 address from string format to number.

Arguments, return value and availability:
  ip: Required. The IPv6 address in string format. 
  bytes: Return value. The IPv6 address as a number in bytes.
  Available since MySQL 4.0.

Related MySQL functions:

• INET_ATON() - IP Address String to Number
• INET_NTOA() - IP Address Number to String
• INET6_ATON() - IPv6 Address String to Number
• INET6_NTOA() - IPv6 Address Number to String

⇒ INET6_NTOA() - IPv6 Address Number to String

⇐ INET_NTOA() - IP Address Number to String

⇑ MySQL Functions on Miscellaneous Purposes

⇑⇑ MySQL Function References