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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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

Method 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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating