关系:students (1 can have N) addresses
场景:学生可以有多个记录,但只有一个关联的记录必须将“current”字段设置为“Yes”(其他值为NULL),因此下面的查询应始终只返回每个学生一条记录。

SELECT * FROM address WHERE student_id = 5 AND current = 'Yes'

问题:
人们有时会在同一个学生的插入或更新后将多个记录标记为“是”,因此我需要避免这样做。在MySQL中使用触发器或存储过程的最佳方法是什么?
如果更新发生在“address”表上,那么应该在某个地方运行,将其他记录标记为空:UPDATE addresses SET current = NULL WHERE student_id = IN_student_id
如果在“address”表上发生INSERT,那么应该在某个地方运行,将其他记录标记为空:UPDATE addresses SET current = NULL WHERE student_id = IN_student_id AND id <> IN_inserted_id
提前谢谢

最佳答案

如果需要在修改数据后自动更新某些内容,正确的方法是触发。注意,触发器可能调用存储过程。
但是,您将无法在触发器中实现所描述的行为:
存储的函数或触发器无法修改调用该函数或触发器的语句已在使用(用于读取或写入)的表。
事实上,“Address X is the current Address”信息应该存储在students表的列中,作为address表的外键。因此,单一性是有保证的。
像这样的东西(because):

CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY,
  current_address INT,
  name VARCHAR(20)
);

CREATE TABLE address (
  id INT NOT NULL PRIMARY KEY,
  student_id INT NOT NULL,
  contents VARCHAR(50) NOT NULL,
  CONSTRAINT student_fk FOREIGN KEY student_fk_idx (student_id)
    REFERENCES student(id)
);

ALTER TABLE student
  ADD CONSTRAINT curraddr_fk_idx
    FOREIGN KEY curraddr_fk_idx (id, current_address)
    REFERENCES address(student_id, id);

注意这个结构允许插入没有“当前地址”的students。这是因为两个表中至少有一个表的外键必须允许NULL值(否则我们不能在两个表中添加一行)。如果这更有意义,那么让address.student_id变成NULL,并允许address成为nobody的地址,直到创建相应的student

关于mysql - UPDATE和INSERT之后在存储过程中更新同一表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17402762/

10-09 20:25
查看更多