MySQL触发器通过重新排序其值来更新列
对不起,如果标题错过了,我不能想出一个更好的与我的问题有关的问题。MySQL触发器通过重新排序其值来更新列
我一直在试图解决这一段时间,我找不到解决方案。
我有一个表categories
:
+----+--------+----------+
| ID | Name | Position |
+----+--------+----------+
| 1 | Dogs | 4 |
| 2 | Cats | 3 |
| 3 | Birds | 10 |
| 4 | Others | 2 |
+----+--------+----------+
我需要保持排名列,以便在某种程度上不会错过的价值观一样,所以最终的表应该是这样的:
+----+--------+----------+
| ID | Name | Position |
+----+--------+----------+
| 1 | Dogs | 3 |
| 2 | Cats | 2 |
| 3 | Birds | 4 |
| 4 | Others | 1 |
+----+--------+----------+
我试过的是在UPDATE和INSERT上创建一个触发器来试图防止这种情况。我创建的触发器(与INSERT相同):
DELIMITER //
CREATE TRIGGER sortPostions BEFORE UPDATE ON categories
FOR EACH ROW BEGIN
SET @max_pos = 0;
SET @min_pos = 0;
SET @max_ID = 0;
SET @min_ID = 0;
SELECT position, id INTO @max_pos,@max_ID FROM categories WHERE position = (SELECT MAX(position) FROM categories);
SELECT position, id INTO @min_pos,@min_ID FROM categories WHERE position = (SELECT MIN(position) FROM categories);
IF NEW.position >= @max_pos AND NEW.id != @max_ID THEN
SET NEW.position = @max_pos + 1;
END IF;
IF NEW.position <= @min_pos AND NEW.id != @min_ID THEN
SET NEW.position = @min_pos - 1;
END IF;
IF NEW.position < 0 THEN
SET NEW.position = 0;
END IF;
END//
DELIMITER ;
但不幸的是,它不能按预期工作。这不是修复缺失值,我认为这不是一个完美的解决方案。
我继续和创建的过程:
BEGIN
SET @n = 0;
UPDATE categories
SET position = @n:[email protected]+1
ORDER BY position ASC;
END
但我无法从触发器调用这个过程,因为它似乎是,MySQL不允许。我得到以下错误:
#1442 - Can't update table 'categories' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
的mysql -V输出:
mysql Ver 14.14 Distrib 5.5.57, for debian-linux-gnu (x86_64) using readline 6.3
什么是解决这个问题的完美解决方案? 非常感谢!
你不能在触发器中做到这一点。 MySQL不允许你在产生触发器的同一张表中对触发器(或触发器所调用的过程)执行INSERT/UPDATE/DELETE。
原因是它可能会导致无限循环(您的更新会产生触发器,它会更新表,这会再次产生触发器,从而再次更新表)。另外,如果这些请求中有多个请求同时发生,它可能会产生锁定冲突。
如果您必须重新编号行的位置,您应该在应用程序代码中执行此操作。在您的初始查询完成后,使用单独的语句执行此操作。
另一种选择是不必担心连续的职位。只要确保他们是在正确的顺序。然后,当您查询表格时,按需生成行号。
SELECT (@n:[email protected]+1) AS row_num, c.*
FROM (SELECT @n:=0 AS n) AS _init
CROSS JOIN categories AS c
ORDER BY c.position ASC;
+---------+----+--------+----------+
| row_num | id | name | position |
+---------+----+--------+----------+
| 1 | 4 | Others | 2 |
| 2 | 2 | Cats | 3 |
| 3 | 1 | Dogs | 4 |
| 4 | 3 | Birds | 10 |
+---------+----+--------+----------+
在MySQL 8.0中,你就可以使用ROW_NUMBER()更标准的语法来做到这一点。
SELECT ROW_NUMBER() OVER w AS row_num, c.*
FROM categories AS c
WINDOW w AS (ORDER BY c.position ASC);
给出与使用用户变量的查询相同的输出。
感谢您的回答!你认为我可以创建一个SELECT触发器来动态添加row_num列(您提供的查询)?我应该将它放在SELECT查询之后或之前?刚刚意识到你是Oracle主管,我很幸运:) – Lambasoft
没有SELECT触发器。可能你想要的是[VIEW](https://dev.mysql.com/doc/refman/5.7/en/create-view.html)。这是一种存储类似宏的复杂查询类型的方法。然后你可以像查看简单的表一样查询视图。但要小心阅读如何在视图中使用ORDER BY。 –
我不知道在调用存储过程从触发器的MySQL限制。 –
@GordonLinoff我收到以下错误:#1442 - 无法更新存储的函数/触发器中的表'类别',因为它已被调用此存储的函数/触发器的语句使用。 – Lambasoft
@GordonLinoff我认为这是因为触发器/过程进入一个无限循环,程序再次触发触发器。 – Lambasoft