如何在MariaDB(MySQL)中用MySQL或PHP查找非UTF-8字符

• 环境信息：
  • MySQL：Ver 15.1 Distrib 10.3.39-MariaDB, for Linux (x86_64) using readline 5.1
  • PHP：8.2.13 (cli) (built: Nov 24 2023 09:33:30) (NTS)
• 数据库现状：列已设置为utf8mb4字符集、utf8mb4_unicode_ci排序规则，但数据中存在Mojibake（乱码）、latin1编码的非有效UTF-8字符，数据来源为编码不统一的政府数据库。
• 已尝试但无效的方案：
  1. MySQL正则查询（具体代码见用户提供内容）
  2. PHP中使用preg_match("//u", $string)和mb_detect_encoding($string,"UTF-8",TRUE)检测

示例数据 #

DROP TABLE IF EXISTS sample; 
CREATE TABLE sample ( data text DEFAULT NULL ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 
INSERT INTO sample VALUES (
 'Mr. Geoffrey H. Yost, O�Melveny & Myers LLP')
 ,('Valero Refining Company � California')
 ,('El Paso Merchant Energy � Petroleum Company')
 ,('Papé Group/IFCO')
 ,('Huntons� SureCrop Farm Svc Inc dba SureCrop Farm')
 ,('developed, manufactured, marketed, tested, and sold the electronic diesel control that allowed Mercedes to manipulate emissions controls and that Bosch marketed ¢€š¬Ã…¡¬ÃƒÆ’‚¬¦¡€š¬Ã…¡¬Ãƒ€ ‚¬„¢‚¬Å¡¬¦¢€š¬Ã…¡¬ÃƒÆ’‚¬¦€š¬Ã…€œClean Diesel¢€š¬Ã…¡¬ÃƒÆ’‚¬¦¡€š¬Ã…¡¬€š¬Ã…¡<9d> to the public.'); 

MySQL检测方法 #

方法1：通过编码转换对比检测乱码 #

这类乱码本质是latin1编码的字节被错误当作utf8mb4存储，可以通过两次编码转换后对比原字段来筛选异常数据：

SELECT data, 
       CONVERT(CONVERT(data USING latin1) USING utf8mb4) AS corrected_data
FROM sample
WHERE data != CONVERT(CONVERT(data USING latin1) USING utf8mb4);

逻辑：正常utf8mb4字段转换后与原内容一致；若为乱码，转换后会还原为正确字符（比如�会变成’），此时与原字段不一致，即可被筛选出来。

方法2：匹配Mojibake特征字节 #

直接匹配latin1转utf8mb4产生的典型乱码字节模式：

SELECT data
FROM sample
WHERE CONVERT(data USING binary) RLIKE '[\xC2-\xC3][\x80-\xBF]|\xEF\xBF\xBD';

逻辑：latin1的扩展ASCII（0x80-0xFF）被错误存储为utf8mb4时，会被解析为以0xC2/0xC3开头的双字节序列，�对应字节0xEF 0xBF 0xBD，都是乱码的典型特征。

PHP检测方法 #

方法1：编码转换对比法 #

利用编码转换前后的内容差异判断乱码：

function hasMojibake($string) {
    // 将字符串按utf8解码后转latin1编码，再转回utf8解码
    $converted = utf8_decode(utf8_encode($string));
    // 转换后与原字符串不同，说明存在乱码
    return $string !== $converted;
}

// 测试示例
$testStrings = [
    'Mr. Geoffrey H. Yost, O�Melveny & Myers LLP',
    'Papé Group/IFCO',
    '正常UTF-8内容'
];

foreach ($testStrings as $str) {
    if (hasMojibake($str)) {
        echo "检测到乱码：{$str}\n";
    }
}

方法2：直接匹配乱码字节序列 #

针对常见乱码的字节模式做正则匹配：

function detectInvalidChars($string) {
    // 匹配latin1转utf8产生的双字节序列，以及替换字符�
    $pattern = '/[\xC2-\xC3][\x80-\xBF]|\xEF\xBF\xBD/';
    return preg_match($pattern, $string) > 0;
}

// 使用示例
$targetStr = 'Valero Refining Company � California';
if (detectInvalidChars($targetStr)) {
    echo "存在非有效UTF-8字符/乱码";
}

方法3：结合mb_check_encoding的严格检测 #

先验证表面合法性，再通过编码转换判断是否为乱码：

function isInvalidUtf8WithMojibake($string) {
    // 先检查是否为合法UTF-8（表面上的）
    if (!mb_check_encoding($string, 'UTF-8')) {
        return true;
    }
    // 转码后对比原内容
    $converted = mb_convert_encoding(mb_convert_encoding($string, 'latin1', 'utf8'), 'utf8', 'latin1');
    return $string !== $converted;
}

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