有什么方法可以检查 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`
很好，所以没有返回数据，因为 Table A 和 Table 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 的威力显露无遗.即使数据不匹配，外部连接也会返回数据.在这种情况下，我们将在 Table A 和 Table B 上执行 LEFT OUTER JOIN.由于表A是LEFT表，我们要求返回表A中的所有数据，即使它们与表中的任何内容都不匹配乙.运行以下查询.
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`;
您将看到一个结果集，其中 表 A 中的行现在插入到 表 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 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 时，它只会将 Table A 中的新行插入到 Table 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屋！
