MySQL固定列交叉表查询部分多语言及罗马数字字符不显示问题

问题描述 #

我遇到了MySQL字符检索异常的问题，特来求助。我有一张存储各数字系统（如罗马、希伯来、泰文、高棉、老挝等）Unicode国际数字的表，表字符集为utf8，排序规则为utf8_general_ci。

表结构 #

每个数字系统共有18个数字，范围包含0到10，以及50、100、500、1000、10000。

原有查询语句 #

SELECT NUM_SYS_NAME,
       max(case
             when NUM_ID = 0 then
              TEXT
             else
              'N/A'
           end) as '0',
       max(case
             when NUM_ID = 1 then
              TEXT
             else
              'N/A'
           end) as '1',
       max(case
             when NUM_ID = 2 then
              TEXT
             else
              'N/A'
           end) as '2',
       max(case
             when NUM_ID = 3 then
              TEXT
             else
              'N/A'
           end) as '3',
       max(case
             when NUM_ID = 4 then
              TEXT
             else
              'N/A'
           end) as '4',
       max(case
             when NUM_ID = 5 then
              TEXT
             else
              'N/A'
           end) as '5',
       max(case
             when NUM_ID = 6 then
              TEXT
             else
              'N/A'
           end) as '6',
       max(case
             when NUM_ID = 7 then
              TEXT
             else
              'N/A'
           end) as '7',
       max(case
             when NUM_ID = 8 then
              TEXT
             else
              'N/A'
           end) as '8',
       max(case
             when NUM_ID = 9 then
              TEXT
             else
              'N/A'
           end) as '9',
       max(case
             when NUM_ID = 10 then
              TEXT
             else
              'N/A'
           end) as '10',
       max(case
             when NUM_ID = 50 then
              TEXT
             else
              'N/A'
           end) as '50',
       max(case
             when NUM_ID = 100 then
              TEXT
             else
              'N/A'
           end) as '100',
       max(case
             when NUM_ID = 500 then
              TEXT
             else
              'N/A'
           end) as '500',
       max(case
             when NUM_ID = 1000 then
              TEXT
             else
              'N/A'
           end) as '1000',
       max(case
             when NUM_ID = 10000 then
              TEXT
             else
              'N/A'
           end) as '10000'
  from numerals
 WHERE NUM_ID IN
       (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 50, 100, 500, 1000, 10000)
 GROUP BY NUM_SYS_NAME

异常现象 #

查询返回的交叉表格式符合预期，但罗马数字1到4、9、50、100、1000、10000无法正常显示，例如罗马数字5正常显示为V，罗马数字3则显示为N/A。如果我将罗马数字3的取值从III改为ZZZ，就能在查询结果中正常展示。

我测试了多种排序规则，也修改了罗马数字系统TEXT字段的取值，发现O到Z开头的字符都能正常显示，其余字符都会返回N/A，因此所有以I、L、D、C开头的罗马数字都无法检索到。我已经排查了2天仍未找到根因，怀疑该问题和排序规则、多语言混合有关，但很奇怪出问题的是基础拉丁字符而非特殊字符。

解决方案 #

根因定位 #

该问题和字符集、排序规则没有关联，是MAX()聚合函数的使用逻辑错误导致：
你在CASE WHEN分支中，未匹配到对应NUM_ID时返回固定字符串'N/A'，而MySQL的MAX()作用于字符串时，会按排序规则返回同分组下最大的字符串。大写字母的排序优先级为A< B < ... < N < O < ... < Z，'N/A'首字母为N，优先级高于所有A-M开头的字符串，低于O-Z开头的字符串，因此：

• I/C/D/L等A-M开头的罗马数字字符串优先级低于'N/A'，MAX()返回更大的'N/A'
• V等O-Z开头的字符串优先级高于'N/A'，可以正常返回
• 你将III改为ZZZ后优先级高于'N/A'，因此可以正常返回，完全匹配你观察到的现象。

修复代码 #

修改聚合逻辑，将CASE WHEN的ELSE分支返回值改为NULL，再通过IFNULL将未匹配到的空值转为'N/A'即可，修改后的查询语句如下：

SELECT NUM_SYS_NAME,
       IFNULL(MAX(CASE WHEN NUM_ID = 0 THEN TEXT END), 'N/A') AS `0`,
       IFNULL(MAX(CASE WHEN NUM_ID = 1 THEN TEXT END), 'N/A') AS `1`,
       IFNULL(MAX(CASE WHEN NUM_ID = 2 THEN TEXT END), 'N/A') AS `2`,
       IFNULL(MAX(CASE WHEN NUM_ID = 3 THEN TEXT END), 'N/A') AS `3`,
       IFNULL(MAX(CASE WHEN NUM_ID = 4 THEN TEXT END), 'N/A') AS `4`,
       IFNULL(MAX(CASE WHEN NUM_ID = 5 THEN TEXT END), 'N/A') AS `5`,
       IFNULL(MAX(CASE WHEN NUM_ID = 6 THEN TEXT END), 'N/A') AS `6`,
       IFNULL(MAX(CASE WHEN NUM_ID = 7 THEN TEXT END), 'N/A') AS `7`,
       IFNULL(MAX(CASE WHEN NUM_ID = 8 THEN TEXT END), 'N/A') AS `8`,
       IFNULL(MAX(CASE WHEN NUM_ID = 9 THEN TEXT END), 'N/A') AS `9`,
       IFNULL(MAX(CASE WHEN NUM_ID = 10 THEN TEXT END), 'N/A') AS `10`,
       IFNULL(MAX(CASE WHEN NUM_ID = 50 THEN TEXT END), 'N/A') AS `50`,
       IFNULL(MAX(CASE WHEN NUM_ID = 100 THEN TEXT END), 'N/A') AS `100`,
       IFNULL(MAX(CASE WHEN NUM_ID = 500 THEN TEXT END), 'N/A') AS `500`,
       IFNULL(MAX(CASE WHEN NUM_ID = 1000 THEN TEXT END), 'N/A') AS `1000`,
       IFNULL(MAX(CASE WHEN NUM_ID = 10000 THEN TEXT END), 'N/A') AS `10000`
FROM numerals
WHERE NUM_ID IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 50, 100, 500, 1000, 10000)
GROUP BY NUM_SYS_NAME

NULL值不会参与MAX的大小比较，只会取匹配到的TEXT值，只有当没有匹配到对应NUM_ID的记录时，才会返回N/A，完全符合业务需求。

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