mysql - 如何将 SELECT 语句的返回值传递给存储过程中的 DELETE 查询？

Question

我有删除数据库中某些表的子数据(映射的外键约束)的要求。所以，实际上，我想将返回的 SELECT 查询的值传递到 DELETE 语句中。

以下是我创建的存储过程，请帮助我了解这一点，并提供一些从表中删除子行的示例。

delimiter //
create procedure deleteCustomerDeps(in emailAddr varchar(50)) 
begin 
select customer_id into custId
from customer
where customer_id=emailAddr;
set @custoId=custId;
delete from customer where customer_id='@custoId';
prepare stmt from @custoId;
execute stmt;
deallocate prepare stmt;
end //
delimiter ;

TIA....

Answer 1

假设您有以下架构

CREATE TABLE customers
(
  customer_id INT, 
  customer_email VARCHAR(17),
  PRIMARY KEY (customer_id)
);
CREATE TABLE child_table
(
  child_id INT,
  customer_id INT, 
  value INT,
  PRIMARY KEY (child_id),
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

现在要删除所有知道客户电子邮件的子记录，您可以使用多表删除语法

CREATE PROCEDURE deleteCustomerData(IN emailAddr VARCHAR(50)) 
  DELETE t
    FROM child_table t JOIN customers c 
      ON t.customer_id = c.customer_id
   WHERE c.customer_email = emailAddr;

这是 SQLFiddle 演示

---

...but if i want to pass the returned value of SELECT stmt to DELETE...

这正是您在上述示例中所做的。但是你总是可以这样重写它

DELETE t
  FROM child_table t JOIN 
(
  SELECT customer_id 
    FROM customers JOIN ...
   WHERE customer_email = emailAddr
     AND ...
) c
    ON t.customer_id = c.customer_id

或

DELETE 
  FROM child_table 
 WHERE customer_id IN 
(
  SELECT customer_id 
    FROM customers JOIN ...
   WHERE customer_email = emailAddr
     AND ...
)