MariaDB环境下JSON_CONTAINS()无法匹配含德语变音字符内容问题

• 应用栈：后端Laravel 7，前端原生HTML/JavaScript
• 表结构：flights表包含id、date、passengers、pilot_id、second_pilot_id、flight_data（JSON类型）等字段
• 问题现象：
  • 本地环境（MySQL 8.0.23）使用JSON_CONTAINS查询含德语变音字符（ö/ä/ü等）的姓名可正常返回结果
  • 生产环境（MariaDB 10.3.28）执行相同查询返回0条结果，仅替换变音为ASCII写法（如ü→ue）可命中
  • 两端存储的flight_data原始编码一致，均为UTF-8，转义后内容为{"pilotname":"J\u00fchn D\u00fce"}
  • 已尝试修改数据库/表/字段的字符集和校对规则，未解决问题

核心原因 #

MariaDB 10.3版本的JSON值比较默认使用二进制校对规则（utf8mb4_bin），对变音字符、大小写敏感；而MySQL 8.0默认使用utf8mb4_0900_ai_ci校对规则，支持变音、大小写不敏感匹配，这是两端表现不一致的根本原因

可行解决方法 #

1. 修改查询逻辑，显式指定校对规则（推荐）
   放弃直接使用JSON_CONTAINS，改为先提取JSON字段值，再显式指定校对规则做比较，同时改用参数绑定避免SQL注入风险。
   Laravel代码调整为：

->where(function($q) use ($r) {
    $q->whereRaw("
        IF(
            payee = 2,
            JSON_UNQUOTE(JSON_EXTRACT(flight_data, '$.second_pilotname')) = ? COLLATE utf8mb4_unicode_ci,
            JSON_UNQUOTE(JSON_EXTRACT(flight_data, '$.pilotname')) = ? COLLATE utf8mb4_unicode_ci
        )
    ", [$r, $r]);
})

直接执行SQL的写法为：

select id, flight_data, comments, updated_at from logbook 
where JSON_UNQUOTE(JSON_EXTRACT(flight_data, '$.pilotname')) = 'Jühn Düe' COLLATE utf8mb4_unicode_ci

2. 确认Laravel数据库连接配置
   检查config/database.php中MySQL连接的配置，确保字符集和校对规则设置正确：

'mysql' => [
    // 其他配置项
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
]

同时确认生产环境.env文件中没有覆盖DB_CHARSET、DB_COLLATION参数为其他值。

3. 升级MariaDB版本（可选）
   如果服务器支持升级，MariaDB 10.5及以上版本优化了JSON字段的校对规则处理，和MySQL 8.0的兼容性更高，升级后可直接兼容原有JSON_CONTAINS的写法。

4. 变音预处理规避方案（兜底）
   如果以上方案都无法落地，可以修改存储逻辑，在flight_data中额外存储一份经过变音转换的ASCII版姓名（如ü转ue、ä转ae、ö转oe、ß转ss），查询时先把输入的姓名做相同转换再匹配，可完全规避校对规则差异问题。

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