mysql设置列的默认值作为当前登录用户 [英] mysql Set default value of a column as the current logged in user

问题描述

如何设置要作为登录用户的列的默认值?

How do you set the default value of a column to be the logged in user?

我正在创建日志记录"表，其中一列应为登录用户

I am creating a "logging" table, and one of the columns should be the logged in user

(the output of "SELECT user();"). 

这可能吗?

编辑-我尝试过的操作:

create trigger logtrigger_test before insert on logging_test for each row set new.changed_by=current_user();

描述表格

+---------------+-------------+------+-----+-------------------+-----------------------------+
| Field         | Type        | Null | Key | Default           | Extra                       |
+---------------+-------------+------+-----+-------------------+-----------------------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment              |
| source_table  | varchar(50) | NO   |     | NULL              |                             |
| foreign_id    | int(11)     | NO   |     | NULL              |                             | 
| field_changed | varchar(30) | NO   |     | NULL              |                             | 
| changed_by    | varchar(30) | YES  |     | NULL              |                             | 
| changed_on    | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-------------+------+-----+-------------------+-----------------------------+

表中的示例条目

+----+---------------+------------+------------------+----------------------+---------------------+
| id | source_table  | foreign_id | field_changed    | changed_by           | changed_on          |
+----+---------------+------------+------------------+----------------------+---------------------+
| 0  | phone_numbers | 34         | home_phone       | jeff@localhost       | 2013-04-10 14:15:13 |
| 1  | contact_info  | 24         | first_name       | bob@localhost        | 2013-04-11 10:18:43 | 
| 2  | addresses     | 32         | home_address     | autoscript@localhost | 2013-04-12 11:10:37 |
| 3  | addresses     | 36         | business_address | bob@localhost        | 2013-04-12 14:56:17 |
| 4  | addresses     | 36         | business_address | jeff@localhost       | 2013-04-12 15:25:52 |
+----+---------------+------------+------------------+----------------------+---------------------+

推荐答案

好的，通常这不是创建审核表的方式.通常，当您要记录插入，删除和更新时，将执行以下操作:

OK, this is generally not the way one creates audit tables. Typically, when you want to log inserts, deletes, and updates, you would do something like this:

创建一个类似foo的表:

Create a table like foo:

create table foo (
    foo_id int not null auto_increment primary key,
    foo_data varchar(100) not null
    );

然后，您通常会这样制作一个审计表:

Then you usually make an audit table like so:

create table foo_audit (
    foo_audit_id not null auto_increment primary key,
    foo_id int,
    foo_data varchar(100),
    change_type char(1),
    change_timestamp timestamp default current_timestamp,
    change_login varchar(100)
    );

然后您在表上创建一个或多个触发器，如下所示:

Then you make a trigger or triggers on the table like so:

create trigger trg_foo_insert
    after insert on foo
    for each row 
    insert into foo_audit (
        foo_id,
        foo_data,
        change_type,
        change_login
        )
    values (
        new.foo_id,
        new.foo_data,
        'I',
        current_user
        );

您将使用"U"触发器进行更新，并使用"D"触发器进行删除.

You would make a "U" trigger for updates, and a "D" trigger for deletes.

我认为您遇到的主要问题是您试图做一个一刀切"的审计表；我认为这种模式会给您带来很多问题，您不一定拥有要查找的数据，并且仍然需要为要审核的每个表编写至少一个触发器.

I think the main problem you are having is you are trying to do a "one size fits all" audit table; I think this pattern will cause you a lot of issues, you don't necessarily have the data you're looking for, and you will still need to write at least one trigger for each table you are auditing.

但是，对于您的问题的实际答案是，您是在未插入表的触发器上设置触发器，还是试图更新不存在该列的表上的列.

The actual answer to your question, however, is that you were either setting a trigger on a table that was not being inserted to, or trying to update a column on a table where the column did not exist.

这篇关于mysql设置列的默认值作为当前登录用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！