Convert a MySQLGuid (from Devart dotConnect for MySQL) to String in SQL

Question

I'm using Devart's DotConnect product to connect an .net application to a MySQL Database. Everything is working pretty well, but I have an issue when working with SQL in the database.

The application uses Guids for row id's in the database (having come originally from a SQL Server application) which are stored as BINARY(16) in the database (and use DevArt's MySQLGuid class when being accessed through the .net application)

The problem is that when I want to query the database for a specific row I can't just paste in a string representation of a GUID in the database, so I'm trying to work out a function to convert the binary representation of the GUID to a string.

At first, I though it would be a fairly simple matter of calling HEX(id) in the query and then if I wanted a friendly readable output all I would need to do is add -'s in the appropriate place.

The select statement could look like this (in practice, I'd wrap this as a function):

 LOWER(CONCAT(LEFT(HEX(theme_id), 8), '-', MID(HEX(theme_id), 9,4), '-', MID(HEX(theme_id), 13,4), '-', MID(HEX(theme_id), 17,4), '-', RIGHT(HEX(theme_id), 12)))

does not return quite the right GUID. For example, if I store d1dfd973-fa3d-4b90-a1eb-47217162cd40 then the above select statement returns 73d9dfd1-3dfa-904b-a1eb-47217162cd40

It looks like the first 8 bytes have had their order reversed in the group (taking the first part of the string GUID, we have 73d9dfd1 which from a byte order perspective (i.e. treating groups of two characters as one byte) is d1dfd973, which is the right output.

My question is this - is there an operation that I can do within MySQL's dialect of SQL that will allow me to reverse the byte order of the relevant sections? I could make a more complex LEFT/MID/RIGHT statement but that doesn't feel like a good way of doing it.

An suggestions would be very much appreciated.

Andrzej Reduta · Answer 1 · 2014-09-02T13:49:01.893

step 1)

I 'am creating table in Mysql

CREATE TABLE `table_code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(45) DEFAULT NULL,
  `guid` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

step 2)

I insert one row:

INSERT INTO table_code (code,guid) 
VALUES ('d1dfd973fa3d4b90a1eb47217162cd40',UNHEX('d1dfd973fa3d4b90a1eb47217162cd40'));

step 3)

I run

SELECT 
id,
code,
LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
LOWER(HEX(guid))

FROM test.table_code;

step 4) Result is:

1   d1dfd973fa3d4b90a1eb47217162cd40    d1dfd973-fa3d-4b90-a1eb-47217162cd40 d1dfd973fa3d4b90a1eb47217162cd40

Everything looks to be ok - on Mysql side.

So conclusion, are You sure that You are inserting 'd1dfd973fa3d4b90a1eb47217162cd40' (this should be done by using UNHEX('d1dfd973fa3d4b90a1eb47217162cd40') ) or other (reversed) value.

Anyway, to turn such string: "73d9dfd13dfa904ba1eb47217162cd40" into this one "d1dfd973-fa3d-4b90-a1eb-47217162cd40"

You can use such code:

LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-', 
RIGHT(HEX(guid), 12)))

I think Mysql does't give us simpler way, beacuse there is no such function operating on binary data.

To check this:

Step 5)

INSERT INTO table_code (code,guid) 
VALUES ('73d9dfd13dfa904ba1eb47217162cd40',UNHEX('73d9dfd13dfa904ba1eb47217162cd40'));

step 6)

    SELECT 
    id,
    code as `raw code`,
    LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
    LOWER(HEX(guid)) `decoded`,
LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-', 
RIGHT(HEX(guid), 12))) as switched

    FROM test.table_code;

step 7) It seems to be ok.

Thanks - I'm just checking the solution. The only thing that I'm concerned about is that REVERSE seems to be a string function, which would mean that all characters would be reversed, whereas what I want to do is reverse pairs of characters (or reverse bytes) — Richard Comish, Aug 31 '14 at 08:52
Unfortunately, this doesn't work - as I suspect it reverses the string, not the bytes. I can't find a byte level equivalent of REVERSE in MySQL. — Richard Comish, Sep 02 '14 at 12:11
Many thanks - that works well for me. Unfortunately I don't have control over how the GUID is serialized to MySQL (it's done by the DevArt MySQL provider. I'm not sure why they do it that way, but there is probably a reason). Anyway, thankyou. I'll mark this as the solution. — Richard Comish, Sep 02 '14 at 18:03

bigh_29 · Accepted Answer · 2015-01-08T19:53:30.770

For example, if I store d1dfd973-fa3d-4b90-a1eb-47217162cd40 then the above select statement returns 73d9dfd1-3dfa-904b-a1eb-47217162cd40

I think you are suffering from little endian/big endian issues with your GUIDs. See the section titled "Binary coding" on the wiki page for GUIDs : http://en.wikipedia.org/wiki/Globally_unique_identifier

GUIDs and UUIDs are 16 byte values broken into 4 blocks:

Data1 : 4 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data2 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data3 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data4 : 16 bytes   : stored in the same order for both UUIDs and GUIDs

GUIDs and UUIDs are typically writen as hexadecimal strings using hyphens to separate the data components. Here is the kicker, both UUIDs and GUIDs strings are written with Data 1-3 in big endian order. The same strings could be represented with different byte patterns depending on whether they are stored in a UUID or a GUID on a little endian platform.

Let's break down the four byte Data1 block of your sample GUID: d1dfd973. If this string represents a GUID on a microsoft/intel platform, then the bytes would appear in memory in this order:

guid[0] = 0x73 // LSB first
guid[1] = 0xd9
guid[2] = 0xdf
guid[3] = 0xd1 // MSB last

I think this is likely the byte order that Devart wrote the database. Your code then tries to extract this with

LEFT(HEX(theme_id), 8)

which is going to produce the string 73d9dfd1 because it simply uses the data in the order it is stored.

There is a clue that I am on the right track: your code reads Data4 in correct order (a1eb-47217162cd40 in your example GUID). Data4 is stored in the same byte order regardless of platform and whether we are talking about UUIDs or GUIDs.

You have a couple of options

Mandate that everything be stored in the database as little endian GUIDs. Use a conversion routine like Andrzej to convert back to the string representation.
Mandate that everything be stored is big endian UUIDs. Run a one time conversion to reorder the bytes of existing rows. You could use Andrzej's routines for the conversion, but after that, you could convert from binary to string form directly.

I think you're entirely right - many thanks, I can definitely make the changes and work with this. Many thanks for your help, and very very good spot! — Richard Comish, Jan 09 '15 at 00:18

Convert a MySQLGuid (from Devart dotConnect for MySQL) to String in SQL

2 Answers2