带有条件的 Mysql 删除触发器中的语法错误

Question

我在MySQL中编写了一个小的触发器函数。这是我编写的触发器查询，并在代码中给出语法错误。

错误：

您的 SQL 语法有误;请查看手册 对应于您的MySQL服务器版本，以便使用正确的语法 靠近'CP哪里cp.customer_plan_id= first_id;

DELIMITER $$
CREATE TRIGGER delete_plan_on_delete_customer 
AFTER DELETE ON customers
FOR EACH ROW 
BEGIN
DECLARE active_plan INT;
DECLARE first_id INT;
SET active_plan = (SELECT is_active
FROM customer_plans 
WHERE customer_id=OLD.customer_id AND first_plan_id=1);
if(active_plan = 0)THEN
SET first_id = (SELECT customer_plan_id
FROM customer_plans 
WHERE customer_id=OLD.customer_id AND first_plan_id=1);
DELETE customer_plans cp WHERE `cp`.`customer_plan_id`= first_id;
END IF; 
END$$
DELIMITER ;

Answer 1

DELETE查询的语法，来自MySQL文档是：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

将触发器中的DELETE行更改为：

DELETE FROM customer_plans WHERE customer_plan_id = first_id;

旁注：根据我的个人经验，声明变量的默认值是一个好习惯。 例如：

DECLARE active_plan INT(11) DEFAULT 0;
DECLARE first_id INT(11) DEFAULT 0;