本文介绍了MySQL:另一个表列中列的默认值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

考虑到您有表USER,该表在字段USER_ID上具有主键.并且数据库中的每个表都应包含具有实际用户ID的MODIFIED_BY字段.我的目的是在该字段上指定默认值,该默认值等于具有适当名称的用户ID. F.E.您有一个名称为system的用户,并且您希望每个表的每个记录在MODIFIED_BY字段中都具有该用户的ID.同样,您希望所有新记录都满足此要求.

Consider you have table USER which has primary key on field USER_ID. And every table in your db should contain MODIFIED_BY field with id of actual user. My aim is to specify default value on that field which equals id of user with appropriate name. F.E. you have user with name system and you want every record of every table to have id of this user in MODIFIED_BY field. As well you want all the new records to satisfy this.

推荐答案

假设您所指的是除数据库用户(例如注册用户")以外的其他类型的用户,则可能是触发器解决了您的问题.有两个触发器,一个在插入时触发,一个在更新时触发,您可以使用自定义代码指定modified_at列的值,例如select user_id from ... where ...

Assuming that you are referring to other sorts of users than database users (e.g. 'registered users'), maybe triggers are the solution to your problem. With two triggers, one fired when inserting and one when updating, you can specify the value of a modified_at-column using custom code, e.g. select user_id from ... where ...

请检查以下示例代码,这些示例代码应直接回答您的问题;希望它能解决您的问题.否则,请给我们更多信息/更多背景信息.

Please check the following sample code, which should directly answer your question; hope it solves your problem. Otherwise, please give us more information / some more context.

DELIMITER |

drop table if exists sometable
|
CREATE TABLE `sometable` (
  `somecolumn` int(11) DEFAULT NULL,
  `modified_by` int(11) DEFAULT NULL
)
|
drop table if exists registered_user
|
CREATE TABLE registered_user (
  user_id integer primary key,
  name varchar(50)
)
|
drop trigger if exists sometable_inserted
|
drop trigger if exists sometable_modified
|
CREATE TRIGGER sometable_inserted BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
SET new.modified_by := (select user_id from registered_user where name=@name);
END;
|
CREATE TRIGGER sometable_modified BEFORE UPDATE ON sometable
FOR EACH ROW
BEGIN
SET new.modified_by := (select user_id from registered_user where name=@name);
END;
|

DELIMITER ;

insert into registered_user values (5, 'joe');
insert into registered_user values (6, 'sam');

set @name='sam';
insert into sometable(somecolumn) values (1);
insert into sometable(somecolumn) values (2);
set @name='joe';
update sometable set somecolumn = 3 where somecolumn = 2;

这篇关于MySQL:另一个表列中列的默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 09:56
查看更多