MySql 查询以加入表之间不同存储的 uuid

Question

我有一个 uuid 作为人类可读的 guid 存储在一个表中，但在另一个表中它被分成高位和低位。如何编写查询来连接 uuid 上的表？

编辑:table2 将只有 1 个具有给定高位和低位的结果，因此希望效率不会太差，但请将其纳入答案。

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

我需要检索 table2.status 以及 table1.* 其中 table2.upper_bits + table2.lower_bits = table1.uuid (伪where 语句)，但我不知道如何对连接的 table2 上限和下限值求和，或者如何将 table1 的 uuid 转换为连接的位。

谢谢!

Answer 1

这样的事情可能会奏效……但显然效率很低。

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

...您可能会根据整理/比较强制使用大写/小写。

---

我倾向于“绝对有必要”更改您的数据库结构(以回应您对另一个答案的评论)。为了尽量减少对现有查询和逻辑的影响，您可以将其中一个表更改为具有与另一个表匹配的额外字段，并向表中添加触发器以自动填充/更新新字段；然后进行一次性更新以设置所有旧记录的值。

我会尝试先修改 t1，因为两个整数上的索引可能比一个字符串上的索引“更好”；但我不确定将字符串转换为高位和低位的方式有多直接。

修改 t2 会更容易，触发器会比 SET NEW.uuid = CONCAT(HEX(NEW.upper_bits), HEX(NEW.lower_bits)); .. .我说“多一点”是因为触发器最好也在预期点插入 -，这样连接条件就可以消除所有函数使用。

---

编辑:我找到了一种在纯 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
;

您可以在 t1 的触发器中使用类似这样的东西，并为新字段一次性更新 t1。

...它甚至可能有助于连接:

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

注意:是的，这两个中的过度转换似乎是必要的(至少在我测试计算所针对的旧版本的 MySQL 上是这样。)