有没有办法检查MySQL触发器中的查询是否返回空集？

2022-11-30 08:52:28

将行插入表A后,我需要触发器将一些值插入到另一个表B中,但前提是表B尚未包含具有此类值组合的行.所以这就是我提出的：

CREATE TRIGGER upd_manufactorer_in_category AFTER INSERT ON product
FOR EACH ROW 
BEGIN
  DECLARE id_category INT; 
  DECLARE id_manufacturer INT;
  DECLARE id_category_test INT; 

  SET id_category=NEW.id_category;
  SET id_manufacturer=NEW.id_manufacturer;

  SET id_category_test=(SELECT 'id_category'
                FROM category_has_manufacturer
                WHERE 'id_category'=id_category
                AND 'id_manufacturer'=id_manufacturer);

  IF id_category_test IS NULL
  THEN
    INSERT INTO category_has_manufacturer
    SET category_has_manufacturer.id_category = id_category, 
    category_has_manufacturer.id_manufacturer = id_manufacturer;
  END IF;
END

它工作,它在表中插入行.但它没有检查它是否存在,所以我有很多重复.当然我可以用DISTICT来查询,但这不是我想要在这里实现的.我想要一个只有唯一(id_category,id_manufacturer)对的表.有谁能够帮助我？

解决方法:

你可以做的两件事都会有所帮助.

首先像Devart建议的那样向表中添加唯一索引/键.

第二件事是,这是一个很好的地方,你可以使用左右连接来获取不存在的数据.我将向您展示理论,然后您可以将它作为练习应用于您的答案.

第1步：

创建一个名为test的数据库.

第2步：

在新创建的测试数据库中运行以下两个create table脚本：

CREATE  TABLE `test`.`category_has_manufacturer_A` 
(
      `id_category` INT NOT NULL ,
      `id_manufacturer` INT NOT NULL ,
       PRIMARY KEY (
                     `id_category`, 
                     `id_manufacturer`
                   ) 
);

CREATE  TABLE `test`.`category_has_manufacturer_B` 
(
      `id_category` INT NOT NULL ,
      `id_manufacturer` INT NOT NULL ,
       PRIMARY KEY (
                     `id_category`, 
                     `id_manufacturer`
                   ) 
);

是的,所以我们有两个表,我们可以加入一个共同的列.

第3步：

将一些数据添加到category_has_manufacturer_a表中

INSERT INTO `test`.`category_has_manufacturer_a`
(
  `id_category`,
  `id_manufacturer`
)
VALUES
(
    1,
    1
);

很酷所以表A有一些我们想要添加到表B的数据.

第四步：

让我们在两个表上进行内连接,然后查看结果运行以下查询.

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
INNER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

很好,所以没有返回数据,因为表A和表B中的行之间没有直接匹配,这是因为内连接只返回与JOIN条件匹配的行.

第5步：

OUTER JOINS的力量透露出来. OUTER连接将返回数据,即使它们不匹配.在这种情况下,我们将在表A和表B上进行LEFT OUTER JOIN.由于表A是LEFT表,我们要求返回表A中的所有数据,即使它们与表B中的任何内容都不匹配.运行以下查询.

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

这将返回一个如下所示的记录集：

A_ID_CATEGORY|A_ID_MANAFACTURER|B_ID_CATEGORY|B_ID_MANAFACTURER|
            1|                1|             |                 |

该结果清楚地表明,表A中的行与表B中的行不匹配.

第6步：

现在我们可以使用步骤5中的SELECT语句创建一个INSERT语句来插入不存在的这些行.运行以下查询：

 INSERT INTO category_has_manufacturer_b
 SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER

 FROM category_has_manufacturer_a A  
 LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

让我们检查一下插件确实运行了以下查询：

SELECT
 `category_has_manufacturer_b`.`id_category`,
 `category_has_manufacturer_b`.`id_manufacturer`
FROM `test`.`category_has_manufacturer_b`;

您将看到一个结果集,其中表A中的行现已插入表B.

第7步：

在这里,我们添加最后一点和多个部分,我们确保只添加从表A到表B的新行.首先,我们可以添加更多数据.运行以下查询：

INSERT INTO `test`.`category_has_manufacturer_a`
(
   `id_category`,
   `id_manufacturer`
)
VALUES
(
    1,

    2
);

现在再次使用左连接运行查询：

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

您将看到一个结果集,其中包含两个表中匹配的行以及表a中的新行.如果您现在运行插入,则会插入两行并具有主键冲突,或者在您的情况下是重复的行.

如果您将上面的查询更改为以下内容并运行它：

 SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`
WHERE B.`id_category` IS NULL AND  B.`id_manufacturer` IS NULL

记录集将仅包含表A中的新记录.

将insert语句更改为：

INSERT INTO category_has_manufacturer_b
SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`
WHERE B.`id_category` IS NULL AND  B.`id_manufacturer` IS NULL

运行此INSERT时,它只会将表A中的新行插入表B.

摘要：
LEFT和RIGHT JOIN语句可用于将两个表进行比较,并仅从一个表中返回新行.如果行是新的,当尝试从一个表添加到另一个表时,这是一种很好的技术.它将同时在一行和多行上工作,因为它是基于它设置的(SQL很擅长),它非常快.

我希望你了解发生了什么,并可以在你的触发器中应用它.

提示：在触发器中,您可以访问NEW表,您可以加入此表以确定新行.

玩得开心！

码农公寓

相关文章