MariaDB/MySQL中转换非合规Base64 varchar为HEX的正确方法

场景背景 #

• 表中某varchar类型列，预期存储Base64编码值，需转换为HEX格式存储
• 明确规则：有效Base64值长度必为12字符，但列中存在非Base64格式的12字符数据
• MariaDB的FROM_BASE64()函数特性：输入为null或无效Base64时返回null

可行的SELECT验证语句 #

以下SELECT语句可正确筛选12字符的有效Base64值并转换为HEX，执行符合预期：

SELECT column_name,
HEX(FROM_BASE64(column_name)) AS column_name_converted
FROM table_name tn 
WHERE CHAR_LENGTH(column_name) = 12 AND FROM_BASE64(column_name) IS NOT NULL

失败的UPDATE尝试 #

尝试两种UPDATE语句均抛出错误Bad base64 data as position 8（位置8字符为无效符号#），无法完成更新：

语句一：CASE分支判断 #

UPDATE table_name
SET column_name =
    CASE
        WHEN FROM_BASE64(column_name) IS NOT NULL
        THEN HEX(FROM_BASE64(column_name))
        ELSE column_name
    END
WHERE CHARACTER_LENGTH(column_name) = 12 

语句二：WHERE条件过滤 #

UPDATE table_name 
SET column_name  =
HEX(FROM_BASE64(column_name))
WHERE CHAR_LENGTH(column_name) = 12 AND FROM_BASE64(column_name) IS NOT NULL

注：即使WHERE条件包含FROM_BASE64(column_name) IS NOT NULL，执行时仍会对不符合条件的数据触发FROM_BASE64()的无效数据校验报错，且无法提前清理数据。

已实现的正则过滤方案 #

通过正则表达式提前验证Base64格式，成功完成需求，语句如下：

UPDATE table_name
SET column_name =
    CASE
        WHEN column_name REGEXP '^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=|[A-Za-z0-9+/]{4})$'
        THEN HEX(FROM_BASE64(column_name))
        ELSE column_name
    END
WHERE CHARACTER_LENGTH(column_name) = 12

疑问 #

是否存在更简洁的实现方式？

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