CODEWITHSUNDEEP
mysqlsqluuidguid
Chad
Chad

Reputation: 1549

MySql query to join uuid that's stored differently between tables

I have a uuid that's stored in one table as a human-readable guid, but in another table it's split into upper and lower bits. How can I write a query to join the tables on the uuid?

Edit: table2 will only have 1 result with the given upper & lower bits, so hopefully efficiency shouldn't be too terrible, but please factor this into answers.

table1.uuid = 'b33ac8a9-ae45-4120-bb6e-7537e271808e'
table2.upper_bits = -5531888561172430560, table2.lower_bits = -4940882858296115058

I need to retrieve table2.status along with table1.* where table2.upper_bits + table2.lower_bits = table1.uuid (pseudo where statement) but I don't know how to either sum the table2 upper & lower values for the join, or how to convert table1's uuid to bits for the join.

Thanks!

Upvotes: 3

Views: 2686

Answers (3)

Uueerdo
Uueerdo

Reputation: 15961

Something like this might work... but would obviously be highly inefficient.

SELECT ...
FROM table1 AS t1 
INNER JOIN table2 AS t2 ON REPLACE(t1.uuid, '-', '') 
                         = CONCAT(HEX(t2.upper_bits), HEX(t2.lower_bits))
...

...you might to force upper/lower case depending on collation/comparison.

I'd kind of lean toward it being "absolutely necessary" to change your database structure (to respond to a comment you made on another answer). To minimize the impact on existing queries and logic, you could change one of the tables to have additional matching fields to the other, and add triggers to the table to automatically populate/update the new fields; and then do a one time update to set all the old records' values.

I'd try to go with modifying t1 first, since an index on two ints is likely "better" than on one string; but I'm not sure how straight-forward converting the string to the upper and lower bits would be.

Modifying t2 would be easier, the triggers would be little more than SET NEW.uuid = CONCAT(HEX(NEW.upper_bits), HEX(NEW.lower_bits)); ...I say "little more than" because it would be best for the trigger to also insert the -'s at the expected points as well so the join condition could eliminate all function use.

Edit: I found a way to calculate the bits in pure SQL:

SELECT @uuid := REPLACE('b33ac8a9-ae45-4120-bb6e-7537e271808e', '-', '') AS uuid
   , -1 * CAST((~CAST(CONV(SUBSTRING(@uuid, 1, 16), 16, 10) AS SIGNED) + 1) AS SIGNED) AS upper_bits
   , -1 * CAST((~CAST(CONV(SUBSTRING(@uuid, 17, 16), 16, 10) AS SIGNED) + 1) AS SIGNED) AS lower_bits
;

You may be able to use something like this in triggers for t1, and a one time update for t1 for the new fields.

...it might even help with the join:

ON -1 * CAST((~CAST(CONV(SUBSTRING(REPLACE(t1.uuid, '-', ''), 1, 16), 16, 10) AS SIGNED) + 1) AS SIGNED) 
  = t2.upper_bits
AND -1 * CAST((~CAST(CONV(SUBSTRING(REPLACE(t1.uuid, '-', ''), 17, 16), 16, 10) AS SIGNED) + 1) AS SIGNED) 
  = t2.lower_bits

Note: Yes, the excessive casting in both of these appears to be necessary (at least on the older version of MySQL I tested the calculation against.)

Upvotes: 4

mwp
mwp

Reputation: 8467

Here's a variation of @Uueerdo's solution that should be more efficient (sort of a decorate-join-undecorate), but I haven't run the EXPLAINs to know for sure:

SELECT t1.*, t2.status
FROM (
  SELECT UUID_TO_BIN(uuid) AS tmpid, *
  FROM table1
) AS t1 INNER JOIN (
  SELECT UUID_TO_BIN(CONCAT(HEX(upper_bits), HEX(lower_bits))) AS tmpid, status
  FROM table2
) AS t2 ON t1.tmpid = t2.tmpid

It might use a bit more memory, which is something to keep in mind if the tables have many rows and/or if table1 is very wide.

If you only need the records from table1 and table2 matching a single UUID, you should just execute two queries, not a join:

SELECT *
FROM table1
WHERE UUID_TO_BIN(uuid) = UUID_TO_BIN(?)

SELECT status
FROM table2
WHERE UUID_TO_BIN(CONCAT(HEX(upper_bits), HEX(lower_bits))) = UUID_TO_BIN(?)

If upper_bits and lower_bits are indexed, this would be a better way to query table2:

SET @tmpid = UUID_TO_BIN(?);

SELECT status
FROM table2
WHERE upper_bits = @tmpid >> 64, lower_bits = _binary X'FFFFFFFFFFFFFFFF' & @tmpid

And you could apply similar logic to my first solution (I think):

SELECT t1.*, t2.status
FROM (
  SELECT 
    UUID_TO_BIN(uuid) >> 64 AS upper_bits,
    _binary X'FFFFFFFFFFFFFFFF' & UUID_TO_BIN(uuid) AS lower_bits,
    *
  FROM table1
) AS t1 INNER JOIN (
  SELECT upper_bits, lower_bits, status
  FROM table2
) AS t2 ON t1.upper_bits = t2.upper_bits AND t1.lower_bits = t2.lower_bits

None of this is tested but hopefully it gives you some ideas to play around with.

Upvotes: 1

Eperbab
Eperbab

Reputation: 378

The easiest way is to store lower_bits & upper_bits in table1 together with the uuid. Then join the tables on lower_bits & upper_bits.

Edit: If you need only a single row, and you are sure, that there will be a single matching row in the other table, then calculate the uuid= @uuid, lower_bits =@lbits & upper_bits= @ubits, and then run the following: 

Select t1.*, t2.status  
From  
(select * from table1 where uuid = @uuid)  as t1
Cross join  
(select status from table2 where lower_bits =@lbits and upper_bits= @ubits) as t2;

Upvotes: 0

Related Questions