用于数据审计的MySQL Trigger或PHP？ [关闭]

I am looking to keep the history of every single update of the tables, on my application. I am using Laravel, I know there is some package that can help me but I am looking something clean and fast.

I have two options:

  • MySQL Trigger
  • Via PHP code

My first question is, which one is faster ?

My second question is: should I use a schema like this and store all my tables into it:

CREATE TABLE revisions (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `revisionable_type` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `revisionable_id` INT(11) NOT NULL,
    `user_id` INT(11) NULL DEFAULT NULL,
    `key` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `old_value` TEXT NULL COLLATE 'utf8_unicode_ci',
    `new_value` TEXT NULL COLLATE 'utf8_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
)

Or should I recreate a "duplicate" of every table like user_history and add the history on the respectives columns

Users: id, name, surname ...
Users_history: id, user_id, name, surname ...

Given this schema:

CREATE TABLE contacts (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_at` TIMESTAMP NULL DEFAULT NULL
)

I would create this schema as well:

CREATE TABLE contacts_audit (
    `id` INT(10) UNSIGNED NOT NULL,
    `first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `action` varchar(255) NOT NULL COLLATE 'utf8_unicode_ci'
)

Then use these triggers (hopefully my trigger syntax isn't too rusty):

CREATE TRIGGER contacts_audit_insert
AFTER INSERT
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'insert');
END;

CREATE TRIGGER contacts_audit_update
AFTER UPDATE
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'update');
END;

CREATE TRIGGER contacts_audit_delete
BEFORE DELETE
   ON users FOR EACH ROW
BEGIN
    INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (OLD.id, OLD.first_name, OLD.last_name, OLD.updated_by, OLD.updated_at, 'delete');
END;

Potential drawbacks

If you are using one MySQL username/password for database access and authenticating your users against a users table in MySQL then prior to performing any deletes, you will want to perform an update, within PHP, followed by the delete so that you can capture who deleted your record.

However if you are using MySQL for authentication of all users then you can have this in your triggers: Take notice to the use of USER()

INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, USER(), NEW.updated_at, 'insert');
  1. Time both solutions and you will see which one works faster for you. It is impossible to tell which will be faster in your application and environment.

  2. This is a design decision, so it is really up to you and your requirements. You have the following things to consider:

    • If a user changes a record in a transaction, all affected fields will be changed by the same user at the same time. With your suggested structure, all these changes will require a separate entry.

    • It is more difficult to determine how a certain record looked like at a given point of time with your proposed structure, since you basically have to replay all changes to that record from its insertion.

    • On the pro side, all changes are stored in a single table and even if you change the database schema, you do not have to change the structure of your logging.

    • You also have to consider if you want to log changes of all tables or just some selected ones.

    • You also need to consider how you log if the table being logged references other tables. For example, if you want to log transactions, then you may have a transaction type field and a separate transaction type table listing detailed descriptions and perhaps other information about each transaction type. If a transaction type is not used any longer, then it can be removed from the transaction type table or its parameters may change.