如何用正则移除MySQL utf8_general_ci不兼容字符以避免插入报错

在大规模数据转换场景中，将包含特殊Unicode字符（例如「𝐖𝐚𝐥𝐠𝐫𝐞𝐞𝐧𝐬」这类数学粗体字母）的数据插入采用utf8_general_ci排序规则的MySQL列时，会触发SQL错误。这类字符属于Unicode字母类别（匹配\p{L}），导致参考Stack Overflow的常规过滤正则无法生效，最终插入失败。

当前使用的无效正则示例：

String walgreens = "\uD835\uDC16\uD835\uDC1A\uD835\uDC25\uD835\uDC20\uD835\uDC2B\uD835\uDC1E\uD835\uDC1E\uD835\uDC27\uD835\uDC2C";
// 包含多种Unicode类别但仍无法过滤4字节字符
String characterFilter = "[^\\p{L}\\p{M}\\p{N}\\p{P}\\p{Z}\\p{Cf}\\p{Cs}\\p{Sc}\\p{Punct}\\p{ASCII}\\™\\®\\©\\s]";
System.out.println(walgreens.replaceAll(characterFilter, "")); // 输出原字符串「𝐖𝐚𝐥𝐠𝐫𝐞𝐞𝐧𝐬」

及原Stack Overflow方案正则：

String walgreens = "\uD835\uDC16\uD835\uDC1A\uD835\uDC25\uD835\uDC20\uD835\uDC2B\uD835\uDC1E\uD835\uDC1E\uD835\uDC27\uD835\uDC2C";
String characterFilter = "[^\\p{L}\\p{M}\\p{N}\\p{P}\\p{Z}\\p{Cf}\\p{Cs}\\s]";
System.out.println(walgreens.replaceAll(characterFilter, "")); // 同样输出原字符串

报错信息：

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Incorrect string value: '\xF0\x9D\x99\x88\xF0\x9D...' for column

环境说明：MySQL 8.0.23、Java 8、EclipseLink（JPA），且因数据表规模过大无法修改列排序规则。

核心原因 #

MySQL的utf8_general_ci排序规则对应的是utf8mb3编码，仅支持最多3字节的Unicode字符（范围U+0000至U+FFFF）。而「𝐖𝐚𝐥𝐠𝐫𝐞𝐞𝐧𝐬」这类字符属于Unicode补充平面（U+10000及以上），需要4字节存储，因此插入时会被判定为无效字符。

解决方案 #

方案1：直接过滤所有4字节Unicode字符 #

4字节Unicode字符在UTF-16中以代理对形式存在（高代理范围\uD800-\uDBFF，低代理范围\uDC00-\uDFFF），可以通过正则匹配并移除：

String walgreens = "\uD835\uDC16\uD835\uDC1A\uD835\uDC25\uD835\uDC20\uD835\uDC2B\uD835\uDC1E\uD835\uDC1E\uD835\uDC27\uD835\uDC2C";
// 匹配所有UTF-16代理对（即4字节Unicode字符）
String filtered = walgreens.replaceAll("[\uD800-\uDBFF][\uDC00-\uDFFF]", "");
System.out.println(filtered); // 输出空字符串（若原字符串仅含4字节字符）

方案2：将特殊宽体字符转换为普通字符 #

如果希望保留字符语义而非直接删除，可以将数学粗体等特殊字母转换为对应的普通ASCII字母。以数学粗体大小写字母为例：

String walgreens = "\uD835\uDC16\uD835\uDC1A\uD835\uDC25\uD835\uDC20\uD835\uDC2B\uD835\uDC1E\uD835\uDC1E\uD835\uDC27\uD835\uDC2C";
StringBuilder result = new StringBuilder();

for (int i = 0; i < walgreens.length(); ) {
    char high = walgreens.charAt(i);
    // 检查是否为高代理字符
    if (Character.isHighSurrogate(high) && i + 1 < walgreens.length()) {
        char low = walgreens.charAt(i + 1);
        int codePoint = Character.toCodePoint(high, low);
        
        // 处理数学粗体大写字母（U+1D400 ~ U+1D433，对应A-Z）
        if (codePoint >= 0x1D400 && codePoint <= 0x1D433) {
            result.append((char) ('A' + (codePoint - 0x1D400)));
        }
        // 处理数学粗体小写字母（U+1D440 ~ U+1D473，对应a-z）
        else if (codePoint >= 0x1D440 && codePoint <= 0x1D473) {
            result.append((char) ('a' + (codePoint - 0x1D440)));
        }
        // 其他4字节字符直接跳过（或根据需求扩展处理逻辑）
        
        i += 2; // 代理对占2个char位置
    } else {
        // 普通3字节及以下字符直接保留
        result.append(high);
        i += 1;
    }
}

System.out.println(result.toString()); // 输出"Walgreens"

验证效果 #

处理后的字符串仅包含MySQL utf8mb3支持的字符，可正常执行插入/更新操作，不会触发之前的SQL错误。

内容的提问来源于stack exchange，提问作者J. LaF