Utility functions

Spanner supports the following MySQL utility user-defined functions. You need to implement the MySQL functions in your Spanner database before you can use them. For more information on installing the functions, see Install MySQL functions.

Function list

Name Summary
mysql.BIN_TO_UUID Converts a binary UUID representation to a STRING representation.
mysql.INET_ATON Returns the numeric value of an IP address.
mysql.INET_NTOA Returns the IP address from a numeric value.
mysql.INET6_ATON Returns the numeric value of an IPv6 address.
mysql.INET6_NTOA Returns the IPv6 address from a numeric value.
mysql.IS_IPV4 Returns whether the input parameter is an IPv4 address.
mysql.IS_IPV4_COMPAT Returns whether the input parameter is an IPv4-compatible address.
mysql.IS_IPV4_MAPPED Returns whether the input parameter is an IPv4-mapped address.
mysql.IS_IPV6 Returns whether the input parameter is an IPv6 address.
mysql.IS_UUID Returns whether the input parameter is a valid UUID.
mysql.UUID Returns a Universal Unique Identifier (UUID).
mysql.UUID_TO_BIN Converts a string representation of UUID to a binary representation.

mysql.BIN_TO_UUID

mysql.BIN_TO_UUID(bytes_expression)

Description

Converts a binary representation of a UUID (16 bytes) into its standard 36-character string format.

This function supports the following argument:

  • bytes_expression: The BYTES value representing the UUID. It must be 16 bytes long.

Return data type

STRING

Differences from MySQL

The MySQL BIN_TO_UUID function has an optional second argument to control the byte order for time-based (Version 1) UUIDs. This function doesn't support that argument and is generally used with Version 4 UUIDs that are randomly generated.

Limitations

This function doesn't support the two-argument version found in MySQL for swapping time parts. The input binary_uuid must be exactly 16 bytes; otherwise, an error occurs.

Example

The following example converts a binary UUID (represented by a hex string) to its string format:

SELECT mysql.BIN_TO_UUID(FROM_HEX('00112233445566778899AABBCCDDEEFF')) as uuid_string;

/*
+--------------------------------------+
| uuid_string                          |
+--------------------------------------+
| 00112233-4455-6677-8899-aabbccddeeff |
+--------------------------------------+
*/

mysql.INET_ATON

mysql.INET_ATON(string_expression)

Description

Converts a string representation of an IPv4 address (in dot-decimal notation) into its numeric equivalent, an integer.

This function supports the following argument:

  • string_expression: The STRING representation of the IPv4 address (for example, 192.168.1.1).

Return data type

INT64

Limitations

This function parses IPv4 addresses more strictly than the MySQL version might. Invalid IP address formats return NULL or an error.

Example

The following example converts the IP address 10.0.0.1 to its numeric value:

SELECT mysql.INET_ATON('10.0.0.1') as ip_numeric_value;

/*
+------------------+
| ip_numeric_value |
+------------------+
| 167772161        |
+------------------+
*/

mysql.INET_NTOA

mysql.INET_NTOA(numeric_expression)

Description

Converts a numeric representation of an IPv4 address (an integer) back into its string representation in dot-decimal notation.

This function supports the following argument:

  • numeric_expression: The INT64 numeric value of the IPv4 address.

Return data type

STRING

Limitations

If the input number is outside the valid range for an IPv4 address (0 to 4294967295), the function returns NULL.

Example

The following example converts the numeric value 167772161 to an IP address string:

SELECT mysql.INET_NTOA(167772161) as ip_address_string;

/*
+-------------------+
| ip_address_string |
+-------------------+
| 10.0.0.1          |
+-------------------+
*/

mysql.INET6_ATON

mysql.INET6_ATON(string_expression)

Description

Converts a string representation of an IPv6 address (or an IPv4 address) into its binary representation as BYTES.

This function supports the following argument:

  • string_expression: The STRING representation of the IPv6 or IPv4 address.

Return data type

BYTES

Example

The following example converts the IPv6 address 2001:db8::1 to its binary representation and displays it as a hex string:

SELECT TO_HEX(mysql.INET6_ATON('2001:db8::1')) as ipv6_bytes_hex;

/*
+----------------------------------+
| ipv6_bytes_hex                   |
+----------------------------------+
| 20010DB8000000000000000000000001 |
+----------------------------------+
*/

mysql.INET6_NTOA

mysql.INET6_NTOA(bytes_expression)

Description

Converts a binary representation of an IPv6 or IPv4 address (BYTES) back into its standard string representation.

This function supports the following argument:

  • bytes_expression: The BYTES representation of the IPv6 or IPv4 address.

Return data type

STRING

Example

The following example converts a binary IPv6 address (represented by a hex string) back to its string format:

SELECT mysql.INET6_NTOA(FROM_HEX('20010DB8000000000000000000000001')) as ipv6_string;

/*
+-------------+
| ipv6_string |
+-------------+
| 2001:db8::1 |
+-------------+
*/

mysql.IS_IPV4

mysql.IS_IPV4(string_expression)

Description

Checks if a given string is a valid IPv4 address.

This function supports the following argument:

  • string_expression: The STRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid IPv4 addresses:

SELECT
  mysql.IS_IPV4('192.168.1.1') as example1_is_ipv4,
  mysql.IS_IPV4('2001:db8::1') as example2_is_ipv4,
  mysql.IS_IPV4('not-an-ip') as example3_is_ipv4;

/*
+------------------+------------------+------------------+
| example1_is_ipv4 | example2_is_ipv4 | example3_is_ipv4 |
+------------------+------------------+------------------+
| true             | false            | false            |
+------------------+------------------+------------------+
*/

mysql.IS_IPV4_COMPAT

mysql.IS_IPV4_COMPAT(string_expression)

Description

Checks if a given string represents an IPv4-compatible IPv6 address. An IPv4-compatible address has the form ::a.b.c.d.

This function supports the following argument:

  • string_expression: The STRING to check.

Return data type

BOOL

Differences from MySQL

If you provide a NULL input, this function returns FALSE. In MySQL 5.7, IS_IPV4_COMPAT(NULL) returns 0 (false), and in MySQL 8.0, it returns NULL.

Example

The following example checks for an IPv4-compatible IPv6 address:

SELECT mysql.IS_IPV4_COMPAT('::192.0.2.128') as is_ipv4_compatible;

/*
+--------------------+
| is_ipv4_compatible |
+--------------------+
| true               |
+--------------------+
*/

mysql.IS_IPV4_MAPPED

mysql.IS_IPV4_MAPPED(string_expression)

Description

Checks if a given string represents an IPv4-mapped IPv6 address. An IPv4-mapped address has the form ::ffff:a.b.c.d.

This function supports the following argument:

  • string_expression: The STRING to check.

Return data type

BOOL

Example

The following example checks for an IPv4-mapped IPv6 address:

SELECT mysql.IS_IPV4_MAPPED('::ffff:192.0.2.128') as is_ipv4_mapped;

/*
+----------------+
| is_ipv4_mapped |
+----------------+
| true           |
+----------------+
*/

mysql.IS_IPV6

mysql.IS_IPV6(string_expression)

Description

Checks if a given string is a valid IPv6 address.

This function supports the following argument:

  • string_expression: The STRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid IPv6 addresses:

SELECT
  mysql.IS_IPV6('2001:db8::1') as example1_is_ipv6,
  mysql.IS_IPV6('192.168.1.1') as example2_is_ipv6, /* This is IPv4 */
  mysql.IS_IPV6('::ffff:192.0.2.128') as example3_is_ipv6; /* IPv4-mapped IPv6 */

/*
+------------------+------------------+------------------+
| example1_is_ipv6 | example2_is_ipv6 | example3_is_ipv6 |
+------------------+------------------+------------------+
| true             | false            | true             |
+------------------+------------------+------------------+
*/

mysql.IS_UUID

mysql.IS_UUID(string_expression)

Description

Checks if a given string is a valid universally unique identifier (UUID) in the standard 8-4-4-4-12 hexadecimal format.

This function supports the following argument:

  • string_expression: The STRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid UUIDs:

SELECT
  mysql.IS_UUID('550e8400-e29b-41d4-a716-446655440000') as is_valid_uuid,
  mysql.IS_UUID('not-a-uuid') as is_invalid_uuid;

/*
+---------------+-----------------+
| is_valid_uuid | is_invalid_uuid |
+---------------+-----------------+
| true          | false           |
+---------------+-----------------+
*/

mysql.UUID

mysql.UUID()

Description

Returns a Version 4 universally unique identifier (UUID) as a string.

This function doesn't support any arguments.

Return data type

STRING

Differences from MySQL

Both this function and MySQL's UUID function comply with RFC 4122. However, MySQL typically generates Version 1 UUIDs (based on current time and MAC address), while this function generates Version 4 UUIDs (based on random numbers).

Example

The following example generates a UUID:

SELECT mysql.UUID() as generated_uuid;

/*
+--------------------------------------+
| generated_uuid                       |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426614174000 |
+--------------------------------------+
*/

mysql.UUID_TO_BIN

mysql.UUID_TO_BIN(string_expression)

Description

Converts a UUID string (in standard 8-4-4-4-12 format) into its 16-byte binary representation.

This function supports the following argument:

  • string_expression: The STRING representation of the UUID.

Return data type

BYTES

Differences from MySQL

MySQL's UUID_TO_BIN function has an optional second argument to control byte order for Version 1 UUIDs. This function doesn't support that optional argument.

Limitations

This function doesn't use the optional second argument for swapping time-low and time-high parts of the UUID, as they do in standard MySQL. If the input string is not a valid UUID format, an error occurs.

Example

The following example converts a UUID string to its binary representation and displays it as a hex string:

SELECT TO_HEX(mysql.UUID_TO_BIN('00112233-4455-6677-8899-aabbccddeeff')) as uuid_bytes_hex;

/*
+----------------------------------+
| uuid_bytes_hex                   |
+----------------------------------+
| 00112233445566778899AABBCCDDEEFF |
+----------------------------------+
*/