20

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?

ughai
  • 9,830
  • 3
  • 29
  • 47
jimmy
  • 8,121
  • 11
  • 36
  • 40

4 Answers4

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

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

N.B.
  • 13,688
  • 3
  • 45
  • 55
  • 1
    What about UUID ? is that the same,, or we need to use the UUID function for that? – jimmy Nov 18 '11 at 09:16
  • 1
    `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. – N.B. Nov 18 '11 at 09:32
  • 1
    ok got it.. Thank you so much.. Does ROWGUIDCOL also work in the same way.. ? – jimmy Nov 18 '11 at 09:41
  • 1
    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). – N.B. Nov 18 '11 at 09:56
  • Thank you so much.. very much appriciated help – jimmy Nov 18 '11 at 10:45
21

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.

Joe Harris
  • 13,671
  • 4
  • 47
  • 54
5

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

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
Doolali
  • 956
  • 9
  • 13
2

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

Oliver
  • 76
  • 1