What is the replacement for uniqueidentifier in Mysql

Question

I have a query from SQL Server which I want to run in Mysql. but I cannot find any replacement for uniqueidentifier keyword from SQL Server script to MYSQL Script.

Here is the query

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

What will be the query in Mysql for the same above SQL Server script?

Wast

Answer 1

CREATE TABLE FOO (
myid CHAR(38) NOT NULL,
barid CHAR(38) NOT NULL
);

According to MS website, GUID's are 38 chars in length.

Mongolian

What about UUID ? is that the same,, or we need to use the UUID function for that? – Wast

UUID is a MySQL function, it returns the GUID - it doesn't do anything with storing it. Specifying myid CHAR(38) NOT NULL is a statement that creates a column named myid and it's type is CHAR that's exactly 38 characters in length. Since GUID is effectively represented as a 38-byte string, you need to store it as such. That's why there are no "shortcuts" in MySQL, you need to explicitly set the column data type and then you can invoke internal functions whose results you store in your newly created table. – Mongolian

ok got it.. Thank you so much.. Does ROWGUIDCOL also work in the same way.. ? – Wast

If ROWGUIDCOL stores anything related to GUID, it means that it's definitely going to be represented as a string so - yes, in MySQL you'd save it as CHAR (or VARCHAR if the length is variable). – Mongolian

Thank you so much.. very much appriciated help – Wast

Answer 2

The accepted answer, although not exactly wrong, is somewhat incomplete. There certainly are more space efficient ways to store GUID/UUIDs. Please have a look at this question: "Storing MySQL GUID/UUIDs"

This is the best way I could come up with to convert a MySQL GUID/UUID generated by UUID() to a binary(16):

UNHEX(REPLACE(UUID(),'-',''))

And then storing it in a BINARY(16)

If storage space of the GUID/UUID is a primary concern this method will deliver significant savings.

Gad

Answer 3

According the MySQL website you should match it to VARCHAR(64)

UNIQUEIDENTIFIER,   VARCHAR(64)

http://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html

Bogoch

Answer 4

Remember also that a 16 byte value is represented in hex as 32 bytes. With the 4 dashes and the 2 curly braces, that gets us the 38 bytes in this format compatible with SQL Server with a 38 byte string. For example: {2DCBF868-56D7-4BED-B0F8-84555B4AD691}.

Zsolway