MySQL触发器INSERT因VALUES中SELECT *引发基数违规1241报错

问题现象 #

为MySQL业务表配置INSERT类型的审计触发器后，插入新记录时操作失败，返回错误信息：
Cardinality violation: 1241 Operand should contain 1 column(s)
该触发器的作用是将新插入的业务记录附加操作类型、版本号两个审计字段，写入对应的审计表。已验证错误和触发器INSERT语句VALUES()子句中嵌套的SELECT *写法直接相关：将该子查询替换为逐一枚举的NEW.列名列表后，触发器可正常运行。

相关环境代码 #

业务表（users） #

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL DEFAULT 0,
  `date_updated` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

审计表（users_audit） #

审计表比业务表多action、revision两个前置审计字段，后续字段和业务表完全对应：

CREATE TABLE `users_audit` (
  `action` varchar(8) NOT NULL,
  `revision` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `username` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `date_updated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

触发错误的触发器代码 #

CREATE TRIGGER users_ins AFTER INSERT ON users
FOR EACH ROW BEGIN
    INSERT INTO users_audit
    VALUES ('insert', 
            (SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id), 
            (SELECT b.* FROM users b WHERE b.id = NEW.id LIMIT 1)
           );    
END

错误排查过程 #

• 计算版本号的子查询(SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id)仅返回单个标量值，符合VALUES子句的参数要求，运行无异常。
• 最初怀疑是子查询返回多行导致错误，因此添加LIMIT 1限制返回行数（且id是users表主键，匹配条件本身就只会返回单行数据），但错误依然存在。
• 最终定位错误原因：SELECT b.*写法会返回多列数据，MySQL语法不支持在VALUES()的单个参数位置传入多列结果，这些列无法被自动解析为逗号分隔的独立值，最终导致传入值的数量和审计表列数不匹配，触发基数违规错误。
• 尝试移除第二个SELECT外层的括号修改写法，直接触发SQL语法错误，无法创建触发器，错误写法如下：

  CREATE TRIGGER users_ins AFTER INSERT ON users
  FOR EACH ROW BEGIN
      INSERT INTO users_audit
      VALUES ('insert', 
              (SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id), 
              SELECT b.* FROM users b WHERE b.id = NEW.id LIMIT 1
             );    
  END
  

可正常运行但存在冗余的写法 #

逐一枚举NEW对象的所有业务列时，触发器可正常工作，代码如下：

CREATE TRIGGER users_ins AFTER INSERT ON users
FOR EACH ROW BEGIN
    INSERT INTO users_audit 
    VALUES ('insert', 
            (SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id),
            NEW.id, NEW.username, NEW.email, NEW.admin, NEW.date_updated
           );    
END 

这种写法的问题在于需要手动列出业务表的所有列，批量为几十上百张表创建审计触发器时会产生大量重复的机械工作，需要确认是否存在无需手动枚举列的简化实现方案。

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