MariaDB 10.6使用JSON_TABLE关联表出现排序规则冲突如何解决

MariaDB 中JSON解析生成的字符串字段默认继承 collation_connection 会话变量的排序规则，而非数据库/表的默认排序规则。即使你全局配置了默认排序规则为utf8mb4_unicode_520_ci，如果会话的collation_connection仍为默认的utf8mb4_general_ci，JSON_TABLE返回的字段就会用后者，和users表的字段排序规则冲突。

下面三种方案按实现复杂度从低到高排序，可按需选择：

方案1：关联时临时指定排序规则 #

直接在关联条件中对JSON_TABLE返回的字段强制指定和业务表一致的排序规则，改动最小，适合仅单一场景使用JSON_TABLE的情况：

SET @json='[ "license:4b5ef761bfcc3be1e86c3bf16e33f5432417ca6b", "license:4faca2e6f3fe74881e5fb91c9a02bbcfe1d3bb83" ]';

SELECT accounts
FROM users u
INNER JOIN
(
  SELECT * FROM json_table(@json, '$[*]'
    COLUMNS(
      identifier varchar(100) PATH '$' 
    )
  ) AS t1
) AS players
  ON u.identifier = players.identifier COLLATE utf8mb4_unicode_520_ci;

方案2：查询JSON_TABLE时统一转排序规则 #

在提取JSON_TABLE字段时就转换排序规则，后续临时表可多次复用，不需要每次关联都加转换规则：

SELECT accounts
FROM users u
INNER JOIN
(
  SELECT identifier COLLATE utf8mb4_unicode_520_ci AS identifier 
  FROM json_table(@json, '$[*]'
    COLUMNS(
      identifier varchar(100) PATH '$' 
    )
  ) AS t1
) AS players
  ON u.identifier = players.identifier;

方案3：修改会话级别排序规则（全局生效） #

如果你的存储过程中会大量用到JSON相关操作，可在存储过程开头直接设置当前会话的连接排序规则，后续所有JSON解析返回的字符串都会自动使用对应排序规则：

-- 存储过程开头先执行
SET collation_connection = 'utf8mb4_unicode_520_ci';

-- 后续JSON_TABLE查询不需要额外加转换规则
SET @json='[ "license:4b5ef761bfcc3be1e86c3bf16e33f5432417ca6b", "license:4faca2e6f3fe74881e5fb91c9a02bbcfe1d3bb83" ]';
SELECT accounts
FROM users u
INNER JOIN
(
  SELECT * FROM json_table(@json, '$[*]'
    COLUMNS(
      identifier varchar(100) PATH '$' 
    )
  ) AS t1
) AS players
  ON u.identifier = players.identifier;

如果想要永久生效，可直接修改my.ini配置文件中的collation_connection配置项，重启数据库后所有新连接都会默认使用该排序规则。

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