MySQL GROUP_CONCAT中ISNULL替换NULL后排序异常求助

问题描述 #

使用GROUP_CONCAT拼接字段时，原本ORDER BY子句可正常排序，但通过IFNULL替换NULL值后，所有替换生成的"NULL"字符串都排在拼接结果的开头，未按promptID的预期顺序排列。

执行的SQL语句：

SELECT filledForm.creationDate AS date, GROUP_CONCAT(IFNULL(answers.answer, "NULL") ORDER BY answers.promptID) AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN  answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
GROUP BY filledForm.filledFormID;

得到的answers字段中，替换后的"NULL"全部位于开头，预期是按promptID对应的顺序排列（可参考移除GROUP BY后的查询结果）。

相关表结构：

CREATE TABLE prompts (
    promptID INT PRIMARY KEY AUTO_INCREMENT,
    formID INT NOT NULL,
    FOREIGN KEY (formID)
        REFERENCES form (formID)
        ON DELETE CASCADE,        
    hash VARCHAR(16) NOT NULL,
    prompt TEXT(1023) NOT NULL,
    changeDate TIMESTAMP,
    UNIQUE(formID, hash)
);


CREATE TABLE filledForm (
    filledFormID INT PRIMARY KEY AUTO_INCREMENT,
    formID INT NOT NULL,
    FOREIGN KEY (formID)
        REFERENCES form (formID)
        ON DELETE CASCADE,
    creationDate TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP),
    INDEX (formID)
);

CREATE TABLE answers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filledFormID INT NOT NULL, 
    FOREIGN KEY (filledFormID)
        REFERENCES filledForm (filledFormID)
        ON DELETE CASCADE,
    promptID INT NOT NULL,
    FOREIGN KEY (promptID)
        REFERENCES prompts (promptID)
        ON DELETE CASCADE,    
    answer TEXT(99999),
    INDEX (filledFormID)
);

SET SESSION group_concat_max_len = 1000000;

问题原因 #

排序字段使用了answers.promptID，而LEFT JOIN未匹配到answers记录时，answers.promptID为NULL。SQL中NULL在排序时默认排在最前列，导致对应的替换值"NULL"也被前置，破坏了预期的promptID顺序。

解决方法 #

将GROUP_CONCAT中的排序字段改为prompts.promptID（该字段因JOIN关联始终有有效值），修改后的SQL如下：

SELECT filledForm.creationDate AS date, GROUP_CONCAT(IFNULL(answers.answer, "NULL") ORDER BY prompts.promptID) AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN  answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
GROUP BY filledForm.filledFormID;

原理说明 #

filledForm与prompts是内关联，prompts.promptID在所有返回行中均不为NULL，以此作为排序依据，能确保拼接内容（包括替换后的"NULL"）严格按照prompt的原始顺序排列，符合预期。

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