The Uniqueidentifier is a
data type in Microsoft SQL Server, which is used to store Globally Unique
Identifiers (GUIDs). A GUID (globally unique identifier) is a 16-bit
text string that represents an identification (ID). Organizations generate
GUIDs when a unique reference number is needed to identify a row.
The SQL Server globally unique identifier (GUID) data type is
represented by a unique identifier data type that stores a
16-byte binary value. The GUID is a binary number and is
primarily used as an identifier that needs to be unique on
a network with many computers in many places.
The GUID can be generated by calling the Transact-SQL NEWID function and is guaranteed to be globally unique.
For Example if you type the following statement
SELECT NEWID()
You will get a value similar to given below
54E95881-D3A2-4946-B138-DA730E286A89
If you execute the above function again you will get another unique value.
The GUID value is long and ambiguous, so it doesn't make sense to the user. A randomly generated GUID is used for the key value, and if you insert a lot of rows, you will get a random I / O in the index, which can have a negative impact on performance. The GUID is also relatively large compared to other data types. In general, GUIDs are only recommended for use in very narrow scenarios where other data types are not suitable.
In MySQL, there is no exact equivalent UNIQUEIDENTIFIER datatype, we have to use BINARY, VARBINARY or VARCHAR data type to store the GUID values.
To generate the GUID values we can use UUID() function. In addition to this function, MySQL also comes with UUID_TO_BIN and BIN_TO_UUID functions. These functions are used to convert UUID values to binary values
Just like MSSQL's NEWID() function we can use UUID function in MySQL like as shown below
SELECT UUID();
Then you will get a Globally unique identifier as shown below
If you execute the same statement again, you will get a different value, an example is shown below
The advantage of UUID is that it is unique across tables, databases and computers. But the disadvantage is that it is not in human readable format and if you want to store it in the database, it will occupy more space.
Most of the time-to save space-in the database we convert the UUID values to Binary format using UUID_TO_BIN function and then to re-convert it again we use BIN_TO_UUID functions.
When there is a need to convert MSSQL table with UNIQUEIDENTIFIER column to MySQL using Data Loader tool, the Data Loader will automatically match Uniqueidentifier datatype to Varchar(36) datatype. i.e. it will store GUID values as character in MySQL.
Let's see an example, suppose we have table in MS SQL Server with the following structure
We want to convert this table to MySQL using Data Loader.
To do that fire up Data Loader and choose the MS SQL Server as source and MySQL as target and then select this GTest table as from list of MS SQL Server tables and then select the MySQL target database, where we want this table to be copied.
After selecting the source and target, the Data Loader will show the Source and Destination tables screen. In this screen you can click Col. Mapping button to see the structure of the source MS SQL Table and also the matching MySQL table structure with matching datatypes which Data Loader is going to create as shown below
We will accept the default datatype mappings and click OK button to close Column mapping screen and then click Next button.
After following the wizard, you will finally get to the final screen. In the final screen click Start button to start the conversion. After the conversion is finished you will get the log as shown below
Now click Browse DB button to view the target MySQL table
So this is how easy it is to convert MS SQL Server table with UNIQUEIDENTIFIER datatype to MySQL using Data Loader.
Later on, if you want to save space, you can store UUID values in binary format, you can add a column to MySQL table and update this column with binary values using UUID_TO_BIN function and later on drop this column