如何在Synapse无服务器/PySpark中实现MySQL Hex编码逆解码？

先明确原编码逻辑 #

原MySQL编码是将原始SUBJECT_CODE的前16位按以下规则拆分转HEX后拼接：

• 第4、3、2、1位字符 → 转HEX作为第一部分
• 第6、5位字符 → 转HEX作为第二部分
• 第8、7位字符 → 转HEX作为第三部分
• 第9-10位字符 → 直接转HEX作为第四部分
• 第11-16位字符 → 直接转HEX作为第五部分
  最终用-连接五段HEX字符串。

注意：原始值中16位之后的内容未被编码，因此无法从当前编码值中恢复。

方案1：Synapse无服务器SQL实现 #

利用UNHEX、REVERSE和字符串分割函数还原原始前16位：

SELECT
    -- 恢复前4位：第一部分UNHEX后反转
    REVERSE(UNHEX(SUBSTRING_INDEX(encoded_subject_code, '-', 1)))
    -- 恢复第5-6位：第二部分UNHEX后反转
    || REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX(encoded_subject_code, '-', 2), '-', -1)))
    -- 恢复第7-8位：第三部分UNHEX后反转
    || REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX(encoded_subject_code, '-', 3), '-', -1)))
    -- 恢复第9-10位：第四部分直接UNHEX
    || UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX(encoded_subject_code, '-', 4), '-', -1))
    -- 恢复第11-16位：第五部分直接UNHEX
    || UNHEX(SUBSTRING_INDEX(encoded_subject_code, '-', -1))
    AS decoded_subject_code_prefix
FROM
    your_table;

测试验证 #

针对编码值34383963-7271-3261-3378-367273356F61：

1. 第一部分34383963 → UNHEX得489c → 反转后为c984
2. 第二部分7271 → UNHEX得rq → 反转后为qr
3. 第三部分3261 → UNHEX得2a → 反转后为a2
4. 第四部分3378 → UNHEX得3x
5. 第五部分367273356F61 → UNHEX得6rs5oa
   拼接结果为c984qra23x6rs5oa，与原始预期值的前16位完全匹配。

方案2：PySpark实现 #

通过split拆分编码字符串，结合unhex、reverse和concat完成还原：

from pyspark.sql import functions as F

# 处理数据框中的编码字段
df = df.withColumn("parts", F.split(F.col("encoded_subject_code"), "-")) \
       .withColumn("part1_reversed", F.reverse(F.unhex(F.col("parts")[0]))) \
       .withColumn("part2_reversed", F.reverse(F.unhex(F.col("parts")[1]))) \
       .withColumn("part3_reversed", F.reverse(F.unhex(F.col("parts")[2]))) \
       .withColumn("part4", F.unhex(F.col("parts")[3])) \
       .withColumn("part5", F.unhex(F.col("parts")[4])) \
       .withColumn("decoded_subject_code_prefix", 
                   F.concat(F.col("part1_reversed"), 
                            F.col("part2_reversed"), 
                            F.col("part3_reversed"), 
                            F.col("part4"), 
                            F.col("part5"))) \
       .drop("parts", "part1_reversed", "part2_reversed", "part3_reversed", "part4", "part5")

# 测试示例
test_df = spark.createDataFrame([("34383963-7271-3261-3378-367273356F61",)], ["encoded_subject_code"])
test_df = test_df.withColumn("parts", F.split(F.col("encoded_subject_code"), "-")) \
       .withColumn("part1_reversed", F.reverse(F.unhex(F.col("parts")[0]))) \
       .withColumn("part2_reversed", F.reverse(F.unhex(F.col("parts")[1]))) \
       .withColumn("part3_reversed", F.reverse(F.unhex(F.col("parts")[2]))) \
       .withColumn("part4", F.unhex(F.col("parts")[3])) \
       .withColumn("part5", F.unhex(F.col("parts")[4])) \
       .withColumn("decoded_subject_code_prefix", 
                   F.concat(F.col("part1_reversed"), 
                            F.col("part2_reversed"), 
                            F.col("part3_reversed"), 
                            F.col("part4"), 
                            F.col("part5")))

test_df.select("encoded_subject_code", "decoded_subject_code_prefix").show(truncate=False)

测试输出 #

+---------------------------------------+-------------------------+
|encoded_subject_code                   |decoded_subject_code_prefix|
+---------------------------------------+-------------------------+
|34383963-7271-3261-3378-367273356F61|c984qra23x6rs5oa         |
+---------------------------------------+-------------------------+

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