mysql - 使用触发器在插入时使用另一个表中的随机行集填充表

Question

我遇到了这个问题。我有一个带有以下表的 mysql 数据库:

USERS(Cod,Interests)
SURVEY(Cod,Target_Interests)
USERS_OF_A_SURVEY(CodSurvey,CodUser)

兴趣具有相同的Target_Interests值集。

我希望在我的数据库中有一个触发器，在对SURVEY进行插入后，以这种方式在USERS_OF_A_SURVEY上放置1000个条目:

例如:我将值 SURV001,MUSIC 输入到 SURVEY 中。

现在，触发器应在 USERS_OF_A_SURVEY 上放置 1000 个条目，其中 CodSurvey 固定为 SURV001，CodUser 随机从兴趣=音乐的用户。

不知道我是否正确解释了我的问题，我希望是的。帮助我!

谢谢

Answer 1

你在找这个吗？

CREATE TRIGGER tg_survey_after_insert
AFTER INSERT ON survey
FOR EACH ROW 
  INSERT INTO users_of_a_survey (codsurvey, coduser)
  SELECT NEW.cod, cod
    FROM users
   WHERE interests = NEW.target_interests
   ORDER BY RAND()
   LIMIT 1000
;

插入几行

INSERT INTO survey VALUES('SURV001', 'music');
INSERT INTO survey VALUES('SURV002', 'music');

插入后users_of_a_survey的内容

| CODSURVEY | CODUSER |-----------------------|   SURV001 |       3 ||   SURV001 |       1 | .....................|   SURV002 |       1 ||   SURV002 |       4 |......................

Here is SQLFiddle demo.

UPDATE:

And if i would like to do the inverse? Delete all users from "users_of_a_survey" when I delete their survey?

You can either go with a BEFORE DELETE trigger or with FOREIGN KEY ... ON DELETE CASCADE

A trigger may look like

CREATE TRIGGER tg_survey_after_delete
BEFORE DELETE ON survey
FOR EACH ROW 
  DELETE FROM users_of_a_survey 
   WHERE codsurvey = OLD.cod
;

这里是SQLFiddle 演示。