MySQL触发器技术咨询：行转列及将旧字段存入另一表JSON列的可行性

Absolutely! You can absolutely meet both requirements with MySQL triggers. Let’s walk through a practical example to demonstrate how this works, using common table structures and trigger logic.

Example Setup #

First, let’s define sample tables to mirror your use case:

1. Source Table: The original table with row-based data that needs pivoting.
2. Pivoted Table: The target table where we’ll store the column-based (pivoted) data.
3. Audit Table: The table that will archive old row data in a JSON column.

-- 1. Source table (row-based data)
CREATE TABLE source_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entity_id INT,
    attribute VARCHAR(50),
    value VARCHAR(100),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Pivoted table (column-based output)
CREATE TABLE pivoted_table (
    entity_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. Audit table to store old data in JSON
CREATE TABLE audit_table (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    source_id INT,
    old_data JSON,
    audit_action VARCHAR(10),
    audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger Implementation #

We’ll create an AFTER UPDATE trigger (you can adapt this for INSERT or DELETE as needed) that:

1. Pivots rows to columns: Updates or inserts the pivoted data into pivoted_table based on entity_id.
2. Stores old data as JSON: Captures the old row values from source_table and inserts them into audit_table as a JSON object.

DELIMITER //

CREATE TRIGGER trigger_source_table_after_update
AFTER UPDATE ON source_table
FOR EACH ROW
BEGIN
    -- 1. Perform row-to-column pivot (upsert into pivoted_table)
    INSERT INTO pivoted_table (entity_id, name, email, phone)
    VALUES (NEW.entity_id,
        CASE WHEN NEW.attribute = 'name' THEN NEW.value ELSE name END,
        CASE WHEN NEW.attribute = 'email' THEN NEW.value ELSE email END,
        CASE WHEN NEW.attribute = 'phone' THEN NEW.value ELSE phone END)
    ON DUPLICATE KEY UPDATE
        name = CASE WHEN NEW.attribute = 'name' THEN NEW.value ELSE name END,
        email = CASE WHEN NEW.attribute = 'email' THEN NEW.value ELSE email END,
        phone = CASE WHEN NEW.attribute = 'phone' THEN NEW.value ELSE phone END,
        last_updated = CURRENT_TIMESTAMP;

    -- 2. Store old row data as JSON in audit_table
    INSERT INTO audit_table (source_id, old_data, audit_action)
    VALUES (OLD.id,
        JSON_OBJECT(
            'id', OLD.id,
            'entity_id', OLD.entity_id,
            'attribute', OLD.attribute,
            'value', OLD.value,
            'updated_at', OLD.updated_at
        ),
        'UPDATE');
END //

DELIMITER ;

Key Notes #

• Trigger Timing: Use AFTER triggers if you need to ensure the source table operation completes before modifying other tables. For INSERT, you’d use NEW values only (since there’s no OLD data), and for DELETE, you’d use OLD values exclusively.
• JSON Handling: MySQL’s JSON_OBJECT() function converts row values into a valid JSON object. You can extend this to include all fields from your source table.
• Pivoting Logic: The example uses CASE statements to map row attributes to columns. For more complex pivots, you might use conditional aggregation, but CASE works well for straightforward mappings.
• Performance: Triggers add overhead to write operations. If you’re working with high-volume data, test the trigger’s impact and consider batch processing alternatives if needed.
• Data Consistency: Ensure your pivoting logic handles edge cases (e.g., missing attributes) to avoid null values where unintended.

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