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

[6 Sep 2003 3:56] Deniz TATAR
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 :)
[5 May 2004 23:19] Stephen P. Malley
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.
[7 Oct 2005 19:08] Hartmut Holzgraefe
The UUID() function seems to be what you were looking for:

http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html
[8 Nov 2005 0:00] Bugs System
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".
[8 Nov 2005 11:09] Deniz TATAR
Is seems that UUID() function is OK. Thanks lot, but it does not yet work with replication :(
[26 Nov 2005 11:52] Valeriy Kravchuk
OK. So, let's make this a verified feature request - to replicate UUID(). It may be possible to implement this with row-based replication...
[2 Dec 2005 7:53] Nikolay Shestakov
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.
[2 Dec 2005 9:01] Sergei Golubchik
You could use UNHEX(REPLACE(UUID(), '-','')) and store it in BINARY(16)
[2 Dec 2005 14:28] Nikolay Shestakov
Yes. But back?
[4 Feb 2008 19:38] James Day
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.
[22 Feb 2008 20:07] Hernan Calabrese
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.
[8 Apr 2008 19:51] Bob Connell
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,
  ....
)
[9 Apr 2008 12:53] Deniz TATAR
Hello Bob,

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

INSERT INTO foo(myid, barid, ...) VALUES (NEWID(), NEWID(), ...)
[4 Dec 2009 18:53] Jeremiah Gowdy
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.
[22 Oct 2010 9:52] Hartmut Holzgraefe
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