CODEWITHSUNDEEP
mysqlsql-serveruniqueidentifier
jimmy
jimmy

Reputation: 8391

What is the replacement for uniqueidentifier in Mysql

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?

Upvotes: 20

Views: 42126

Answers (4)

Doolali
Doolali

Reputation: 1006

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

Upvotes: 6

Joe Harris
Joe Harris

Reputation: 14045

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.

Upvotes: 21

Oliver
Oliver

Reputation: 76

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}.

Upvotes: 2

N.B.
N.B.

Reputation: 14091

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

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

Upvotes: 23

Related Questions