MariaDB视图中如何复用函数计算结果以避免重复调用？

可以通过子查询或**CTE（公共表表达式）**预先计算reportNumberCountFunction的结果，从而避免重复调用。以下是两种可行方案：

方案1：使用CTE（推荐，代码更清晰） #

利用WITH子句先算出每个message对应的REPORT_NUMBERS，后续直接引用该值即可：

create view resources_view as
WITH message_with_report AS (
    SELECT 
        message.*,
        user.nickname AS CREATOR,
        userAssignee.nickname AS ASSIGNEE,
        reportNumberCountFunction(message.id, 'message') AS REPORT_NUMBERS
    FROM message
    JOIN user ON message.creator_id = user.id
    LEFT JOIN user userAssignee ON message.assignee_id = userAssignee.id
)
SELECT 
    id AS ID,
    id AS SORT_ID,
    'MESSAGE' AS TYPE,
    description AS PREVIEW,
    created_at AS CREATED_AT,
    modified_at AS MODIFIED_AT,
    CREATOR,
    creator_id AS CREATOR_ID,
    IF(deleted = TRUE, 'DELETED', 
       IF(REPORT_NUMBERS > 0, 'REPORTED', 
          IF(verified_at IS NOT NULL, 'VERIFIED', 'NOT_VERIFIED'))) AS STATUS,
    ASSIGNEE,
    assignee_id AS ASSIGNEE_ID,
    REPORT_NUMBERS
FROM message_with_report;

方案2：使用子查询 #

将函数计算逻辑放在子查询中，主查询基于子查询结果构建视图：

create view resources_view as
SELECT 
    id AS ID,
    id AS SORT_ID,
    'MESSAGE' AS TYPE,
    description AS PREVIEW,
    created_at AS CREATED_AT,
    modified_at AS MODIFIED_AT,
    CREATOR,
    creator_id AS CREATOR_ID,
    IF(deleted = TRUE, 'DELETED', 
       IF(REPORT_NUMBERS > 0, 'REPORTED', 
          IF(verified_at IS NOT NULL, 'VERIFIED', 'NOT_VERIFIED'))) AS STATUS,
    ASSIGNEE,
    assignee_id AS ASSIGNEE_ID,
    REPORT_NUMBERS
FROM (
    SELECT 
        message.*,
        user.nickname AS CREATOR,
        userAssignee.nickname AS ASSIGNEE,
        reportNumberCountFunction(message.id, 'message') AS REPORT_NUMBERS
    FROM message
    JOIN user ON message.creator_id = user.id
    LEFT JOIN user userAssignee ON message.assignee_id = userAssignee.id
) AS temp;

两种方案都能确保reportNumberCountFunction仅被调用一次，既提升性能，也让代码更易维护。

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