How to detect an IPv6 Address that is IPv4 compatible using the IS_IPV4_COMPAT() function?

IS_IPV4_COMPAT(ip) is a MySQL built-in function that returns 1 if the given IPv6 address is IPv4-compatible, 0 otherwise. The IPv6 address must be given in number format.

An IPv4-compatible IPv6 address has 12 leading bytes of '00'. The last 4 bytes can form an IPv4 address. For example:

SELECT IS_IPV4_COMPAT('::10.0.5.9');
  -- +------------------------------+
  -- | IS_IPV4_COMPAT('::10.0.5.9') |
  -- +------------------------------+
  -- |                            0 |
  -- +------------------------------+

SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9'));
  -- +------------------------------------------+
  -- | IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9')) |
  -- +------------------------------------------+
  -- |                                        1 |
  -- +------------------------------------------+

SELECT HEX(INET_ATON('10.0.5.9')), HEX(INET6_ATON('::10.0.5.9'));
  -- +----------------------------+----------------------------------+
  -- | HEX(INET_ATON('10.0.5.9')) | HEX(INET6_ATON('::10.0.5.9'))    |
  -- +----------------------------+----------------------------------+
  -- | A000509                    | 0000000000000000000000000A000509 |
  -- +----------------------------+----------------------------------+

SELECT IS_IPV4_COMPAT(INET6_ATON('::a000:0509'));
  -- +-------------------------------------------+
  -- | IS_IPV4_COMPAT(INET6_ATON('::a000:0509')) |
  -- +-------------------------------------------+
  -- |                                         1 |
  -- +-------------------------------------------+

SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9'));
  -- +-----------------------------------------------+
  -- | IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9')) |
  -- +-----------------------------------------------+
  -- |                                             0 |
  -- +-----------------------------------------------+

Reference information of the IS_IPV4_COMPAT() function:

IS_IPV4_COMPAT(ip): boolean
  Returns TRUE if and only if the given IPv6 address is IPv4-compatible.

Arguments, return value and availability:
  ip: Required. The IPv6 address to be examined.
  boolean: Return value. 
    TRUE if and only if the given IPv6 address is IPv4-compatible.
  Available since MySQL 4.0.

Related MySQL functions:

• IS_IPV4() - Detecting IPv4 Address
• IS_IPV4_COMPAT() - IPv4-Compatible IPv6 Address
• IS_IPV4_MAPPED() - IPv4-Mapped IPv6 Address
• IS_IPV6() - Detecting IPv6 Address

⇒ IS_IPV4_MAPPED() - IPv4-Mapped IPv6 Address

⇐ IS_IPV4() - Detecting IPv4 Address

⇑ MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References