如何通过SQL存储过程按JSON字段值将INPUT_T数据分存至COMMERCIAL_T和NGO_T

前提说明 #

假设目标表COMMERCIAL_T和NGO_T已创建，结构与INPUT_T一致（包含Time和JSON列）；若目标表结构不同，需调整INSERT语句中的字段映射关系。

MySQL版本存储过程 #

DELIMITER //

CREATE PROCEDURE SplitInputData()
BEGIN
    -- 将commercial类型数据插入COMMERCIAL_T
    INSERT INTO COMMERCIAL_T (Time, JSON)
    SELECT Time, JSON
    FROM INPUT_T
    WHERE JSON_EXTRACT(JSON, '$.metadata.product.type') = '"commercial"';

    -- 将ngo类型数据插入NGO_T
    INSERT INTO NGO_T (Time, JSON)
    SELECT Time, JSON
    FROM INPUT_T
    WHERE JSON_EXTRACT(JSON, '$.metadata.product.type') = '"ngo"';

    -- 可选：处理完成后删除INPUT_T中已分流的数据（避免重复处理）
    -- DELETE FROM INPUT_T
    -- WHERE JSON_EXTRACT(JSON, '$.metadata.product.type') IN ('"commercial"', '"ngo"');
END //

DELIMITER ;

• JSON_EXTRACT函数会返回带双引号的字符串值，因此判断条件需用'"commercial"'格式。
• 若需要避免重复处理数据，可启用注释中的DELETE语句。

SQL Server版本存储过程 #

CREATE PROCEDURE SplitInputData
AS
BEGIN
    SET NOCOUNT ON;

    -- 将commercial类型数据插入COMMERCIAL_T
    INSERT INTO COMMERCIAL_T (Time, JSON)
    SELECT Time, JSON
    FROM INPUT_T
    WHERE JSON_VALUE(JSON, '$.metadata.product.type') = 'commercial';

    -- 将ngo类型数据插入NGO_T
    INSERT INTO NGO_T (Time, JSON)
    SELECT Time, JSON
    FROM INPUT_T
    WHERE JSON_VALUE(JSON, '$.metadata.product.type') = 'ngo';

    -- 可选：处理完成后删除INPUT_T中已分流的数据
    -- DELETE FROM INPUT_T
    -- WHERE JSON_VALUE(JSON, '$.metadata.product.type') IN ('commercial', 'ngo');
END;

• JSON_VALUE函数返回不带双引号的字符串值，判断条件直接使用'commercial'格式。
• SET NOCOUNT ON用于屏蔽受影响行数的返回消息，更适合任务触发的场景。

任务触发配置 #

存储过程创建完成后，根据数据库系统配置定时任务：

• MySQL：通过EVENT调度器创建定时任务，执行CALL SplitInputData();。
• SQL Server：通过SQL Server Agent创建作业，步骤中执行EXEC SplitInputData;。
• PostgreSQL：使用pg_cron扩展创建定时任务，执行CALL SplitInputData();。

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