MySQL Bugs: #1214: Unique row identifier data type for MySQL (like MSSQL uniqueidentifier)

Bug #1214	Unique row identifier data type for MySQL (like MSSQL uniqueidentifier)
Submitted:	6 Sep 2003 3:56	Modified:	9 Apr 2008 12:53
Reporter:	Deniz TATAR	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 think MySQL absolutely needs a unique indentifier column type and auto generated unique values across multiple MySQL servers.
   For example Microsoft SQL Server has a "uniqueidentifier" data type and a NEWID() function which generates global unique identifiers between different databases or servers. And this ids must never conflict you know.
   Is there any plan to add a MSSQL "uniqueidentifier" equivalent data type or NEWID() equivalent function to MySQL in future releases.
   If you have same tables on different MySQL servers and want to collect all of the rows into a central table with same primary key values you must have a column like MSSQLs uniqueidentifier. And also I think its very usefull for replication.

Thanks,

Deniz TATAR
SCPJ2 - SCWCD

How to repeat:
Not needed. Because this report is not a bug report :)

I agree that this would be very helpful, though I would also suggest that the precise format may be "up for grabs." 

Ideally, the SQL Standards folks should come up with a common unique id datatype and format. 

These quirks exist in all db platforms where both syntax and data formats are concerned -- they should be more closely aligned.

The UUID() function seems to be what you were looking for:

http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Is seems that UUID() function is OK. Thanks lot, but it does not yet work with replication :(

OK. So, let's make this a verified feature request - to replicate UUID(). It may be possible to implement this with row-based replication...

i use uuid but it is 128bit number and stored as 36 bytes in char. Good idea is special column type for storing this value because it using for many indexes.

You could use UNHEX(REPLACE(UUID(), '-','')) and store it in BINARY(16)

Yes. But back?

See bug #16191 for a bug report on UUID() not replicating. That one was closed as too much of a change in 5.0, but expected to work in 5.1 with mixed-mode or row-level replication.

Decision needed for this bug is if UUID() should be our answer to a unique identifier feature request in 5.1 and later. It's very non-optimal for InnoDB primary key use because it puts the most rapidly changing parts of the time first. That scatters updates all over the table and maximises the working set size for inserts, while slowing down UUID-based time sequence retrievals that are quite common, for the same reason.

You can do REVERSE(REPLACE(UUID(), '-','')) so the time part goes last. Keep in mind, if you store it in binary, that you might need to encode it, if you need to pass it in the query string of the page.

How would you use this in an insert statement (e.g., I need to transfer the following from MS SQL to MySQL):

CREATE TABLE foo(
  myid uniqueidentifier NOT NULL,
  barid uniqueidentifier NOT NULL,
  ....
)

Hello Bob,

You can use following statement to insert records into a MSSQL table.

INSERT INTO foo(myid, barid, ...) VALUES (NEWID(), NEWID(), ...)

The feature request should not just be to have UUID/GUID generation support, but to have a SQL data type for uniqueidentifiers that stores the identifier in a native 16 byte binary datatype, and indexes to match.  This is significantly more efficient than storing them in CHAR or VARCHAR fields.

The following two functions can be used to convert UUID() function results from their textual CHAR(36) representation to a 128bit binary string that fits into BINARY(16), and back:

CREATE FUNCTION uuid2bin(uuid CHAR(36)) RETURNS BINARY(16) DETERMINISTIC
BEGIN
  RETURN UNHEX(REPLACE(uuid, '-',''));
END

CREATE FUNCTION bin2uuid(bin BINARY(16)) RETURNS CHAR(36) DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);

  SET hex = HEX(bin);

  RETURN LOWER(CONCAT(LEFT(hex, 8),'-'
                          ,SUBSTR(hex, 9,4),'-'
                          ,SUBSTR(hex,13,4),'-'
                          ,SUBSTR(hex,17,4),'-'
                          ,RIGHT(hex, 12)
                          ));
END