Spanner supports the following MySQL utility user-defined functions.You need to implement the MySQL functions in yourSpanner database before you can use them. For more information oninstalling the functions, seeInstall MySQL functions.

Function list

mysql.BIN_TO_UUID

mysql.BIN_TO_UUID(bytes_expression)

Description

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

This function supports the following argument:

• bytes_expression: TheBYTES value representing the UUID. It must be 16 byteslong.

Return data type

STRING

Differences from MySQL

The MySQLBIN_TO_UUID function has an optional second argument to control thebyte order for time-based (Version 1) UUIDs. This function doesn't supportthat argument and is generally used with Version 4 UUIDs that are randomlygenerated.

Limitations

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

Example

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

SELECTmysql.BIN_TO_UUID(FROM_HEX('00112233445566778899AABBCCDDEEFF'))asuuid_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: TheSTRING 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 returnNULL or an error.

Example

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

SELECTmysql.INET_ATON('10.0.0.1')asip_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 itsstring representation in dot-decimal notation.

This function supports the following argument:

• numeric_expression: TheINT64 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 returnsNULL.

Example

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

SELECTmysql.INET_NTOA(167772161)asip_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 itsbinary representation asBYTES.

This function supports the following argument:

• string_expression: TheSTRING representation of the IPv6 or IPv4 address.

Return data type

BYTES

Example

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

SELECTTO_HEX(mysql.INET6_ATON('2001:db8::1'))asipv6_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 intoits standard string representation.

This function supports the following argument:

• bytes_expression: TheBYTES 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:

SELECTmysql.INET6_NTOA(FROM_HEX('20010DB8000000000000000000000001'))asipv6_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: TheSTRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid IPv4 addresses:

SELECTmysql.IS_IPV4('192.168.1.1')asexample1_is_ipv4,mysql.IS_IPV4('2001:db8::1')asexample2_is_ipv4,mysql.IS_IPV4('not-an-ip')asexample3_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: TheSTRING to check.

Return data type

BOOL

Differences from MySQL

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

Example

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

SELECTmysql.IS_IPV4_COMPAT('::192.0.2.128')asis_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: TheSTRING to check.

Return data type

BOOL

Example

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

SELECTmysql.IS_IPV4_MAPPED('::ffff:192.0.2.128')asis_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: TheSTRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid IPv6 addresses:

SELECTmysql.IS_IPV6('2001:db8::1')asexample1_is_ipv6,mysql.IS_IPV6('192.168.1.1')asexample2_is_ipv6,/* This is IPv4 */mysql.IS_IPV6('::ffff:192.0.2.128')asexample3_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 thestandard 8-4-4-4-12 hexadecimal format.

This function supports the following argument:

• string_expression: TheSTRING to check.

Return data type

BOOL

Example

The following example checks if strings are valid UUIDs:

SELECTmysql.IS_UUID('550e8400-e29b-41d4-a716-446655440000')asis_valid_uuid,mysql.IS_UUID('not-a-uuid')asis_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'sUUID function comply withRFC 4122. However, MySQLtypically 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:

SELECTmysql.UUID()asgenerated_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 binaryrepresentation.

This function supports the following argument:

• string_expression: TheSTRING representation of the UUID.

Return data type

BYTES

Differences from MySQL

MySQL'sUUID_TO_BIN function has an optional second argument to control byte order forVersion 1 UUIDs. This function doesn't support that optional argument.

Limitations

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

Example

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

SELECTTO_HEX(mysql.UUID_TO_BIN('00112233-4455-6677-8899-aabbccddeeff'))asuuid_bytes_hex;/*+----------------------------------+| uuid_bytes_hex                   |+----------------------------------+| 00112233445566778899AABBCCDDEEFF |+----------------------------------+*/