如何编写MySQL查询关联存储格式不同的UUID表

嘿，这个场景我之前处理过，核心就是搞定UUID字符串和拆分的64位整数之间的转换，同时要注意索引优化来保证查询速度。我给你详细拆解一下：

核心转换逻辑 #

UUID是128位的标识符，你的table2把它拆成了两个64位的有符号整数（upper_bits和lower_bits）。要实现关联，我们需要把table1的字符串UUID转换成对应的高低位数值，再去匹配table2的字段——这种方式更利于利用索引，效率更高。

方法一：将UUID字符串转换为高低位关联（推荐） #

MySQL可以通过UNHEX、SUBSTRING和类型转换函数完成这个转换，直接用转换后的数值匹配table2的字段：

SELECT t1.*, t2.status
FROM table1 t1
INNER JOIN table2 t2 
  ON CAST(SUBSTRING(UNHEX(REPLACE(t1.uuid, '-', '')), 1, 8) AS SIGNED BIGINT) = t2.upper_bits
  AND CAST(SUBSTRING(UNHEX(REPLACE(t1.uuid, '-', '')), 9, 8) AS SIGNED BIGINT) = t2.lower_bits;

转换步骤解释：

1. REPLACE(t1.uuid, '-', '')：去掉UUID中的横杠，得到连续的32位十六进制字符串
2. UNHEX(...)：把十六进制字符串转换成16字节的二进制数据
3. SUBSTRING(..., 1, 8)：取前8字节（对应UUID的高位64位），SUBSTRING(..., 9, 8)取后8字节（低位64位）
4. CAST(...) AS SIGNED BIGINT：将二进制字节转换为有符号的64位整数，和table2的字段类型匹配

方法二：将高低位合并为UUID字符串关联（不推荐，效率较低） #

如果你想反过来把table2的高低位合并成UUID字符串去匹配table1，也可以实现，但这种方式需要对table2的每一行做转换，很难利用索引，仅适合小数据集：

SELECT t1.*, t2.status
FROM table2 t2
INNER JOIN table1 t1 
  ON t1.uuid = CONCAT(
    -- 处理高位字节，转换为UUID的前半部分
    SUBSTRING(HEX(REVERSE(UNHEX(LPAD(HEX(t2.upper_bits), 16, '0')))), 1, 8), '-',
    SUBSTRING(HEX(REVERSE(UNHEX(LPAD(HEX(t2.upper_bits), 16, '0')))), 9, 4), '-',
    SUBSTRING(HEX(REVERSE(UNHEX(LPAD(HEX(t2.upper_bits), 16, '0')))), 13, 4), '-',
    -- 处理低位字节，转换为UUID的后半部分
    SUBSTRING(HEX(REVERSE(UNHEX(LPAD(HEX(t2.lower_bits), 16, '0')))), 1, 4), '-',
    SUBSTRING(HEX(REVERSE(UNHEX(LPAD(HEX(t2.lower_bits), 16, '0')))), 5, 12)
  );

关键效率优化 #

因为题目提到table2中高低位对应的结果唯一，我们可以通过索引大幅提升查询速度：

• 给table2创建联合索引：

  CREATE INDEX idx_table2_upper_lower ON table2(upper_bits, lower_bits);
  

  这个索引会让方法一中的JOIN条件直接命中索引，避免全表扫描，查询效率会非常高。
• 确保table1的uuid字段有主键或唯一索引（通常UUID字段都会设置），这样遍历table1时也能快速定位数据。

验证转换正确性 #

你可以单独运行下面的SQL，验证示例UUID的转换结果是否和table2的数值一致：

SELECT
  CAST(SUBSTRING(UNHEX(REPLACE('b33ac8a9-ae45-4120-bb6e-7537e271808e', '-', '')), 1, 8) AS SIGNED BIGINT) AS upper_bits,
  CAST(SUBSTRING(UNHEX(REPLACE('b33ac8a9-ae45-4120-bb6e-7537e271808e', '-', '')), 9, 8) AS SIGNED BIGINT) AS lower_bits;

运行后会得到和你示例中一致的upper_bits = -5531888561172430560和lower_bits = -4940882858296115058。

内容的提问来源于stack exchange，提问作者Chad