[MDEV-15854] Implement uuid_to_bin, bin_to_uuid and is_uuid functions Created: 2018-04-12  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Anders Karlsson Assignee: Alexander Barkov
Resolution: Unresolved Votes: 10
Labels: Compatibility, beginner-friendly, compat80, contribution, foundation, over, patch, portability, upstream-fixed

Issue Links:
Blocks
is blocked by MDEV-4958 Adding datatype UUID Closed
PartOf
is part of MDEV-28906 MySQL 8.0 desired compatibility Open
Relates
relates to MDEV-27928 ERROR 1901 (HY000): Function or expre... Closed

 Description   

MySQL 8.0 introduces these functions, and they are really useful. See more information here: https://dev.mysql.com/blog-archive/mysql-8-0-uuid-support/

Work around is to use the 10.7 uuid type (MDEV-4958).

 Comments   
Comment by Manish Rao [ 2019-02-11 ]

Hey, I'd like to contribute to this feature, it'd be nice if you can assign me a mentor as I'm new to MariaDB.

Comment by Daniel Black [ 2019-02-11 ]

manish9803, Thanks for you interest. I've already implement this in the github pull requests (against active branches) under Development on the right hand side of this page. You are welcome to test these however the inbuilt tests from Oracle are quite thorough.

Comment by Manish Rao [ 2019-02-11 ]

Ohh, cool, Okay

Comment by Alexander Barkov [ 2019-05-13 ]

We're going to add a new data type UUID into 10.5. The following syntax will be used instead:

  • CAST(str AS UUID) – bin_to_uuid()
  • CAST(uuid AS BINARY) – uuid_to_bin()
  • CAST(str AS UUD) IS NOT NULL – is_uuid()

We can consider adding MySQL-8.0 syntax as well, but as a syntactic wrapper for these CAST() operators.

"MDEV-4958 Adding datatype UUID" needs to be implemented first.

Comment by Daniel Black [ 2020-08-15 ]

Sadly didn't make 10.5

Comment by Alexander Barkov [ 2020-08-15 ]

A working prototype for the UUID data type is available here:
https://github.com/MariaDB/server/commits/bb-10.5-bar-m4958
It needs to get reviewed. Hopefully, it will get to 10.6.

Comment by Oli Sennhauser [ 2021-04-07 ]

Related to MDEV-23748?
See also Isotopps blog about UUID for new ideas: https://blog.koehntopp.info/2021/04/06/mysql-and-uuids.html
Candidate for GSoC?

Comment by Bruno Leveque [ 2021-07-23 ]

Unfortunately it did not make it to 10.6 either

However, it looks like MDEV-4958 is now under review and scheduled for 10.7.
So hopefully this will unlock this issue and make it available in an upcoming release.

In the meantime I'm switching to MySQL 8.x, as I'd rather use SQL to generate my sequential v4 UUIDs than a PHP implementation.

Comment by Rick James [ 2021-07-24 ]

Bruno – MySQL implements the sequential version 1 (not 4) UUIDs. Ver 4 is random.
The convenience functions that are standard in MySQL 8.0 are provided (approximately) in http://mysql.rjweb.org/doc.php/uuid .

Comment by Rick James [ 2022-06-20 ]

Potential problem: What is the migration path to/from MySQL 8.0 for tables containing UUIDs? I see the following as grief for users wanting to (or needing to) switch between 10.7 and 8.0 (either way):

  • Different rearrangement of bits.
  • Existence of uuid datatype on side of the migration but not the other.
Comment by Trevor Gross [ 2023-09-29 ]

I have `uuid_to_bin` `uuid_from_bin`/`bin_to_uuid` and `uuid_is_valid` (`is_uuid`) available as UDFs for anyone who needs a workaround: https://github.com/pluots/udf-suite#uuid

Generated at Thu Feb 08 08:24:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.