mysql - 使用另一个表中的值计算列

Question

create table kit(id int unsigned not null auto_increment, creator int unsigned not null, name varchar(16) not null, script longtext not null, tag as concat(select username from user where id = creator, '/', name), primary key (id));

不起作用，因为我正在尝试将标记作为计算列

我想要两个表，user 看起来像

+----+------------------+
| id | username         |
+----+------------------+
|  1 | 1234567890123456 |
+----+------------------+

和kit看起来像

+----+---------+--------+--------+-------------------------+
| id | creator |  name  | script |           tag           |
+----+---------+--------+--------+-------------------------+
|  1 |       1 | kitkit | long   | 1234567890123456/kitkit |
+----+---------+--------+--------+-------------------------+

标签列应根据创建者的用户名和套件名称自动计算。

我认为我的专栏声明会起作用:

tag as concat(select username from user where id = creator, '/', name)

但我想不是。

Answer 1

类似这样的东西(注意!未测试)

create table kit(
id int unsigned not null auto_increment, 
creator int unsigned not null, 
name varchar(16) not null, 
script longtext not null, 
tag varchar(33) not null, primary key (id));

DELIMITER //
CREATE TRIGGER contacts_before_insert
BEFORE INSERT
   ON contacts FOR EACH ROW
BEGIN
   DECLARE vtag varchar(33);
   -- Fill tag value
   select concat(username,'/',OLD.name) from user where id = OLD.creator INTO vtag;

   -- Update created_by field to the username of the person performing the INSERT
   SET NEW.tag = vtag;

END; //

DELIMITER ;