MySQL Bugs: #99422: Feature request: better UUID support

Bug #99422	Feature request: better UUID support
Submitted:	3 May 2020 11:25	Modified:	11 Jun 2020 13:23
Reporter:	Rick Mann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I'm using an ORM that prefers primary keys be UUIDs, and it uses 16-byte binary column types for this. MySQL has some support for UUIDs, providing bin_to_uuid() and uuid_to_bin(), but it's still pretty cumbersome to query and insert into a table using the mysql command line client. You can't, for example, just write `select * from Users;` and get nicely formatted UUIDs. Instead you get garbage, and it usually corrupts the column widths.

The ideal solution would be to support a `UUID(16)` column type that automatically converts to/from the hex string in the mysql client. Alternatively, I'd love a mechanism by which I can tell the mysql client to convert representations of certain columns when reading or writing to the table. I'm not sure how one would specify the latter.

Thanks!

How to repeat:
Create a table with varbinary(16) primary key column type, populate it with some data, then do `select * from <table>`. Not the non-human-readable result.

Suggested fix:
Introduce a `UUID(<length>)` column type that renders as hex strings in the mysql client, and accepts hex strings for insert, but otherwise behaves like `BINARY(<length>)` to other clients (like programmatic clients).

Hi Mr. Mann,

Thank you for your feature request.

I think that it makes sense. The only downside is that we doubt that it will be high on our list of new features.

Verified as a feature request.

See 5 JUN 2020 comment on bug 98086.  Automatically storing this internally as a Binary in a UUID(16) datatype with the high/low time parts automatically swapped behind the scenes would solve SO MANY issues.  PLEASE explore this in more depth and see if you can see to float this issue up to a higher importance!