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

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

An IPv4-mapped IPv6 address has 10 leading bytes of '00', followed by 2 bytes of 'ffff', then 4 bytes for the mapped IPv4 address. For example:

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

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

SELECT HEX(INET_ATON('10.0.5.9'));
  -- +----------------------------+
  -- | HEX(INET_ATON('10.0.5.9')) |
  -- +----------------------------+
  -- | A000509                    |
  -- +----------------------------+

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

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

Reference information of the IS_IPV4_MAPPED() function:

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

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-mapped.
  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_IPV6() - Detecting IPv6 Address

⇐ IS_IPV4_COMPAT() - IPv4-Compatible IPv6 Address

⇑ MySQL Functions on Miscellaneous Purposes

⇑⇑ MySQL Function References