MySQL实现单表列数据自动复制的触发器方案请求

Got it, let's solve this. You need columnB (integer type) to automatically mirror the value of columnA (also integer) in the same table—no manual queries required. Triggers are perfect for this because they run automatically when specific table events happen (like inserting or updating rows).

Here's a step-by-step solution covering both insert and update scenarios, since you'll want columnB to stay in sync even when columnA changes later.

1. Trigger for Insert Operations #

This trigger sets columnB to match columnA whenever a new row is inserted:

DELIMITER //
CREATE TRIGGER sync_colB_on_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.columnB = NEW.columnA;
END //
DELIMITER ;

2. Trigger for Update Operations #

This trigger updates columnB only when columnA is modified (to avoid unnecessary updates if other columns change):

DELIMITER //
CREATE TRIGGER sync_colB_on_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    -- Only sync if columnA's value has changed
    IF NEW.columnA != OLD.columnA THEN
        SET NEW.columnB = NEW.columnA;
    END IF;
END //
DELIMITER ;

Key Notes: #

• Replace your_table_name with the actual name of your table.
• The DELIMITER // command is necessary because MySQL uses semicolons to end statements, and the trigger body contains semicolons—changing the delimiter temporarily lets MySQL recognize the entire trigger as a single statement.
• Using BEFORE INSERT/UPDATE ensures the columnB value is set before the row is written to the table, so the sync happens seamlessly.
• The conditional check in the update trigger (IF NEW.columnA != OLD.columnA) prevents redundant updates when other columns in the row are modified but columnA stays the same.

How to Test: #

• Insert a row with a value for columnA:

  INSERT INTO your_table_name (columnA) VALUES (123);
  

  Query the table—you’ll see columnB is also 123.
• Update columnA in an existing row:

  UPDATE your_table_name SET columnA = 456 WHERE id = 1;
  

  Check again—columnB should now be 456.

If you ever need to drop the triggers (e.g., to modify them), use:

DROP TRIGGER IF EXISTS sync_colB_on_insert;
DROP TRIGGER IF EXISTS sync_colB_on_update;

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