PHPMyAdmin中创建Accounts表审计触发器遇#1136错误求助

AI 小北

2026-8-25

问题排查与简化实现：Accounts表审计触发器

问题背景

我是数据库新手，在PHPMyAdmin中为accounts表创建审计触发器，参照教程编写代码后，测试编辑操作时触发错误：#1136 - Column count doesn't match value count at row 1，无法定位问题，请求排查；同时希望获取更简便的实现代码。

表结构说明

原表（accounts）结构

CREATE TABLE `accounts` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `account_no` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
 `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
 `initial_balance` double DEFAULT NULL,
 `total_balance` double NOT NULL,
 `note` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `is_default` tinyint(1) DEFAULT NULL,
 `is_active` tinyint(1) NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
)

审计表（accounts_audit_log）结构

CREATE TABLE `accounts_audit_log` (
 accounts_id bigint(20) NOT NULL,
 old_row_data JSON,
 new_row_data JSON,
 dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
 dml_timestamp TIMESTAMP NOT NULL,
 dml_created_by VARCHAR(255) NOT NULL,
 PRIMARY KEY (`accounts_id`,`dml_type`,`dml_timestamp`)
)

现有触发器代码

Insert触发器

INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        null,
        JSON_OBJECT(
            "account_no", NEW.account_no,
            "name", NEW.name,
            "initial_balance", NEW.initial_balance,
            "total_balance", NEW.total_balance,
            "note", NEW.note,
            "is_default", NEW.is_default,
            "is_active", NEW.is_active,
            "created_at", NEW.created_at,
            "updated_at", NEW.updated_at
        ),
        'INSERT',
        CURRENT_TIMESTAMP,
        @logged_user,
        @transaction_timestamp
    )

Update触发器

INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        JSON_OBJECT(
            "account_no", OLD.account_no,
            "name", NEW.name,
            "initial_balance", OLD.initial_balance,
            "total_balance", OLD.total_balance,
            "note", OLD.note,
            "is_default", OLD.is_default,
            "is_active", OLD.is_active,
            "created_at", OLD.created_at,
            "updated_at", OLD.updated_at
        ),      
        JSON_OBJECT(
            "account_no", NEW.account_no,
            "name", NEW.name,
            "initial_balance", NEW.initial_balance,
            "total_balance", NEW.total_balance,
            "note", NEW.note,
            "is_default", NEW.is_default,
            "is_active", NEW.is_active,
            "created_at", NEW.created_at,
            "updated_at", NEW.updated_at
        ),
        'UPDATE',
        CURRENT_TIMESTAMP,
        @logged_user,
        @transaction_timestamp
    )

Delete触发器

INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        OLD.id,
        JSON_OBJECT(
            "account_no", OLD.account_no,
            "name", OLD.name,
            "initial_balance", OLD.initial_balance,
            "total_balance", OLD.total_balance,
            "note", OLD.note,
            "is_default", OLD.is_default,
            "is_active", OLD.is_active,
            "created_at", OLD.created_at,
            "updated_at", OLD.updated_at
        ),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @logged_user,
        @transaction_timestamp
    )

错误原因排查

报错#1136的核心原因是INSERT语句的字段列表与VALUES值的数量不匹配：

审计表的字段列表一共6个，但每个触发器的VALUES里都多传了一个@transaction_timestamp，导致值的数量为7，和字段数不匹配；
Update触发器的old_row_data中，name字段错误使用了NEW.name，应该用OLD.name来记录修改前的旧值。

修复并简化后的触发器实现

基础修复版（兼容所有MySQL版本）

Insert触发器

DELIMITER //
CREATE TRIGGER accounts_after_insert
AFTER INSERT ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        NULL,
        JSON_OBJECT(
            "account_no", NEW.account_no,
            "name", NEW.name,
            "initial_balance", NEW.initial_balance,
            "total_balance", NEW.total_balance,
            "note", NEW.note,
            "is_default", NEW.is_default,
            "is_active", NEW.is_active,
            "created_at", NEW.created_at,
            "updated_at", NEW.updated_at
        ),
        'INSERT',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER()) -- 用USER()兜底，避免变量为空
    );
END //
DELIMITER ;

Update触发器

DELIMITER //
CREATE TRIGGER accounts_after_update
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        JSON_OBJECT(
            "account_no", OLD.account_no,
            "name", OLD.name, -- 修正为OLD.name
            "initial_balance", OLD.initial_balance,
            "total_balance", OLD.total_balance,
            "note", OLD.note,
            "is_default", OLD.is_default,
            "is_active", OLD.is_active,
            "created_at", OLD.created_at,
            "updated_at", OLD.updated_at
        ),      
        JSON_OBJECT(
            "account_no", NEW.account_no,
            "name", NEW.name,
            "initial_balance", NEW.initial_balance,
            "total_balance", NEW.total_balance,
            "note", NEW.note,
            "is_default", NEW.is_default,
            "is_active", NEW.is_active,
            "created_at", NEW.created_at,
            "updated_at", NEW.updated_at
        ),
        'UPDATE',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER())
    );
END //
DELIMITER ;

Delete触发器

DELIMITER //
CREATE TRIGGER accounts_after_delete
AFTER DELETE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        OLD.id,
        JSON_OBJECT(
            "account_no", OLD.account_no,
            "name", OLD.name,
            "initial_balance", OLD.initial_balance,
            "total_balance", OLD.total_balance,
            "note", OLD.note,
            "is_default", OLD.is_default,
            "is_active", OLD.is_active,
            "created_at", OLD.created_at,
            "updated_at", OLD.updated_at
        ),
        NULL,
        'DELETE',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER())
    );
END //
DELIMITER ;

简化版（MySQL 8.0+支持）

如果你的MySQL版本是8.0及以上，可以用JSON_OBJECT(*)快速将整行转成JSON，自动排除id字段，减少代码量：

Insert触发器简化版

DELIMITER //
CREATE TRIGGER accounts_after_insert
AFTER INSERT ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        NULL,
        JSON_REMOVE(JSON_OBJECT(*), "$.id"), -- 移除id字段，避免重复存储
        'INSERT',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER())
    );
END //
DELIMITER ;

Update触发器简化版

DELIMITER //
CREATE TRIGGER accounts_after_update
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        NEW.id,
        JSON_REMOVE(JSON_OBJECT(*), "$.id"),
        JSON_REMOVE(JSON_OBJECT(NEW.*), "$.id"),
        'UPDATE',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER())
    );
END //
DELIMITER ;

Delete触发器简化版

DELIMITER //
CREATE TRIGGER accounts_after_delete
AFTER DELETE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO accounts_audit_log (
        accounts_id,
        old_row_data,
        new_row_data,
        dml_type,
        dml_timestamp,
        dml_created_by
    )
    VALUES(
        OLD.id,
        JSON_REMOVE(JSON_OBJECT(*), "$.id"),
        NULL,
        'DELETE',
        CURRENT_TIMESTAMP,
        COALESCE(@logged_user, USER())
    );
END //
DELIMITER ;

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

PHPMyAdmin中创建Accounts表审计触发器遇#1136错误求助

问题背景 #

表结构说明 #

原表（accounts）结构 #

审计表（accounts_audit_log）结构 #

现有触发器代码 #

Insert触发器 #

Update触发器 #

Delete触发器 #

错误原因排查 #

修复并简化后的触发器实现 #

基础修复版（兼容所有MySQL版本） #

Insert触发器

Update触发器

Delete触发器

简化版（MySQL 8.0+支持） #

Insert触发器简化版

Update触发器简化版

Delete触发器简化版

问题背景

表结构说明

原表（accounts）结构

审计表（accounts_audit_log）结构

现有触发器代码

Insert触发器

Update触发器

Delete触发器

错误原因排查

修复并简化后的触发器实现

基础修复版（兼容所有MySQL版本）

简化版（MySQL 8.0+支持）