有什么方法可以检查MySQL触发器中的查询是否返回空集? [英] Is there any way to check whether or not the query inside a MySQL trigger returns an empty set?
问题描述
将行插入表A之后，我需要触发器将其中的一些值插入到另一个表B中，但前提是表B尚未包含具有这种值组合的行.所以这就是我想出的:
After the row is inserted into the table A, I need the trigger to insert some values from it into the other table B, but only if table B doesn't already contain the row with such combination of values. So here's what I came up with:
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)对的表.有人可以帮我吗?
And it works, it inserts the row in the table. But it doesn't check whether it existed before, so I have many duplicates. Of course I can query this with DISTICT, but this is not what I want to achieve here. I want to have a table with only unique (id_category, id_manufacturer) pairs. Can anybody help me?
推荐答案
您可以同时做两件事会有所帮助.
Two things you can do both will help.
首先按照Devart的建议将唯一的索引/键添加到表中.
First add a unique index/key to the table as Devart suggested.
第二件事是，这是一个绝佳的地方，您可以在其中使用左右联接来获取不存在的数据.我将向您展示理论，然后您可以将其应用于练习中.
The second thing is that this is a great place where you can use right and left joins to get the data that does not exist. I am going to show you the theory then you can apply it to your answer as a exercise.
第1步:
创建一个名为test的数据库.
Create a database called test.
第2步:
在新创建的测试数据库中运行以下两个创建表脚本:
Run the following two create table scripts in the newly created test database:
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`
)
);
好的，所以我们有两个表，它们有一个共同的列，我们可以将它们连接在一起.
Right so we two tables that have have a columns in common that we can join on.
第3步:
将一些数据添加到category_has_manufacturer_a表
Add some data to the category_has_manufacturer_a table
INSERT INTO `test`.`category_has_manufacturer_a`
(
`id_category`,
`id_manufacturer`
)
VALUES
(
1,
1
);
很酷，因此表A 中有一些我们要添加到表B中的数据.
Cool so Table A has some data in that we want to add to Table B.
第4步:
让我们在两个表上进行内部联接，并通过以下查询查看结果.
Lets do a inner join on the two tables and look at the result run the following query.
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条件的行.
Great so there was no data returned as there was no direct match between the rows in Table A and Table B this is due to the fact that a inner join only returns rows that match the JOIN condition.
第5步:
OUTER JOINS的力量显现出来. OUTER联接将返回数据，即使它们不匹配也是如此.在这种情况下，我们将在表A 和表B 上进行左外部联接.由于 Table A 是 LEFT 表，因此我们要求返回 Table A 中的所有数据，即使它们与 Table中的任何内容都不匹配B .运行以下查询.
The power of OUTER JOINS revealed. A OUTER join will return data even if they dont match. In this case we are going to do a LEFT OUTER JOIN on Table A and Table B. As Table A is the LEFT table we are asking to return all the data from Table A even if they dont match anything in Table B. Run the following query.
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`
这将返回如下所示的记录集:
This will return a recordset that looks like this:
A_ID_CATEGORY|A_ID_MANAFACTURER|B_ID_CATEGORY|B_ID_MANAFACTURER|
1| 1| | |
此结果清楚地表明表A 中的行与表B 中的行不匹配.
This result clearly shows that Table A has rows in that dont match rows in Table B.
第6步:
现在，我们可以在第5步中使用SELECT语句来创建INSERT语句，以插入不存在的这些行.运行以下查询:
Now we can use the SELECT statement in Step 5 to create a INSERT statement to insert these rows that dont exist. Run the following query:
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`
让我们检查插入的内容是如何运行以下查询:
Lets check what the insert did run the following query:
SELECT
`category_has_manufacturer_b`.`id_category`,
`category_has_manufacturer_b`.`id_manufacturer`
FROM `test`.`category_has_manufacturer_b`;
您将看到一个结果集，其中 Table A 中的行现在插入到 Table B 中.
You will see a result set with the row from Table A now inserted into Table B.
第7步:
在这里我们添加最后一点，以确保只添加从表A 到表B 的新行.首先让我们添加更多数据.运行以下查询:
Here we add the last bit and pieces where we make sure we add only new rows from Table A to Table B. First lets add some more data. Run the following query:
INSERT INTO `test`.`category_has_manufacturer_a`
(
`id_category`,
`id_manufacturer`
)
VALUES
(
1,
2
);
现在使用左联接再次运行查询:
Now run the query with the left join in again:
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中匹配.如果您现在运行插入操作，则将同时插入两行，并且有主键冲突，或者在您的情况下有重复行.
You will see a resultset with the row that matches in both tables and the new row in table a only. If you ran the insert now you would insert both rows and have a primary key violation or in your case a duplicate row.
如果将上面的查询更改为以下查询并运行它:
If you change the query above to the following and run it:
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 中的新记录.
The record set will contain only the new record in Table A.
将insert语句更改为:
Change the insert statement to:
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 中.
When you run this INSERT it will only insert the new rows from Table A into Table B.
摘要: LEFT和RIGHT JOIN语句可用于获取两个表进行比较，并仅返回其中一个的新行.如果行是新行，尝试从一个表添加到另一表时，这是一种很棒的技术.它可以同时处理一行和多行，并且由于它是基于设置的(SQL很擅长)，所以它非常快.
Summary: LEFT and RIGHT JOIN statments can be used to take two tables compare them and return only the new rows from one. This is a great technique when trying to add from one table to the other table if the rows are new. It will work on one row and multiple rows at the same time and since it is set based (SQL is good at this) it is super fast.
我希望您了解正在发生的事情，并可以将其应用到触发器中.
I hope you understand what is happening and can apply this in your trigger.
提示: 在触发器中，您可以访问NEW表，可以加入该表以确定新行.
玩得开心！
这篇关于有什么方法可以检查MySQL触发器中的查询是否返回空集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
