MySQL BEFORE INSERT触发器问题：适配INSERT语句补全price字段

问题背景 #

现有abc表，初始创建及数据插入语句：

create table abc(id int, name varchar(20));
insert into abc values(1, "Tom");

执行以下语句新增price字段：

alter table abc add column price varchar(20) after id;

需求是编写一个BEFORE INSERT触发器，当INSERT语句未提供price值时，自动将其设为空字符串''，同时要兼容原有语句insert into abc values(1, "Tom");。但现有触发器执行该语句时提示列数不匹配错误，触发器代码如下：

DROP TRIGGER IF EXISTS triggerForAbc;
DELIMITER $$
    CREATE TRIGGER triggerForAbc BEFORE insert ON `abc`
    FOR EACH ROW BEGIN
      IF (NEW.price IS NULL OR NEW.price = '') THEN
            SET NEW.price = '';
      ELSE
            SET NEW.price = NEW.price;
      END IF;
    END$$

DELIMITER ;

错误原因 #

列数不匹配的问题和触发器无关——新增price字段后，表的列顺序变为id, price, name，原有INSERT语句没有指定列名，MySQL会按列的顺序匹配值：第一个值1对应id，第二个值"Tom"对应price，但缺少name列的对应值，因此抛出列数不匹配错误。

解决方案 #

要兼容原有INSERT语句并实现触发器需求，可按以下步骤操作：

1. 优化触发器逻辑 #

原有触发器无法处理列顺序变化导致的赋值错位，需要新增逻辑修正这种情况：当name为NULL时，说明原有语句的第二个值被误赋值给了price，此时把price的值转移到name，并将price设为空字符串；同时保留未提供price时设为空的逻辑。修正后的触发器代码：

DROP TRIGGER IF EXISTS triggerForAbc;
DELIMITER $$
CREATE TRIGGER triggerForAbc BEFORE INSERT ON `abc`
FOR EACH ROW BEGIN
    -- 修正原有无列名INSERT的赋值错位
    IF NEW.name IS NULL THEN
        SET NEW.name = NEW.price;
        SET NEW.price = '';
    END IF;
    -- 处理未提供price的情况，将NULL转为空字符串
    IF NEW.price IS NULL THEN
        SET NEW.price = '';
    END IF;
END$$
DELIMITER ;

2. 可选：为price字段设置默认值 #

额外给price字段设置默认空字符串，配合触发器形成双重保障，即使触发器未触发，MySQL也会自动填充默认值：

ALTER TABLE abc MODIFY COLUMN price varchar(20) AFTER id DEFAULT '';

3. 规范写法推荐（根源避免问题） #

如果可以修改原有INSERT语句，建议显式指定列名，这样无论后续表结构如何调整，都不会出现赋值错位的问题：

insert into abc(id, name) values(1, "Tom");

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