stored-procedures - 如何对分层查询执行MySQL存储过程?

  显示原文与译文双语对照的内容
0 0

开始之前,我想说数据库结构已经设置好了,不能更改。 我必须用我所提供的结构。 所以请不要建议我更改数据库。 = )

我有一个在线游戏的数据库。 这个游戏有"技能树",其中每个技能都有"必备软件"技能,你必须获得它才能解锁。 ,技能可能有自己的必备技能,等等。 深度不固定,可以在 6或者 7级别上进行深度调整。

所以,我们有这个技能树,它需要消防雷雷:


Torpedos
|- Missile Launcher Operation 
|- Heavy Missiles
 |- Standard Missiles

( 它还包括一些冗余的prerews,如标准导弹需要导弹发射操作。 那些为了简单而遗漏

由于数据库的性质,我想要制作一个存储过程,通过技能树进行递归。 每个技能都有一个与之关联的ID和一些先决条件。 这是我当前的SQL查询:


SELECT
IFNULL(SkillName.valueInt,SkillName.valueFloat) AS SkillID,
items.typeName AS Skill
FROM dgmtypeattributes AS SkillName
INNER JOIN dgmtypeattributes AS SkillLevel ON SkillLevel.typeID = SkillName.typeID AND SkillLevel.attributeID IN (277, 278, 279, 1286, 1287, 1288)
INNER JOIN invtypes AS items ON IFNULL(SkillName.valueInt,SkillName.valueFloat) = items.typeID
WHERE SkillName.typeID = SKILLID AND
((SkillName.attributeID = 182 AND
SkillLevel.attributeID = 277) OR
(SkillName.attributeID = 183 AND
SkillLevel.attributeID = 278) OR
(SkillName.attributeID = 184 AND
SkillLevel.attributeID = 279) OR
(SkillName.attributeID = 1285 AND
SkillLevel.attributeID = 1286) OR
(SkillName.attributeID = 1289 AND
SkillLevel.attributeID = 1287) OR
(SkillName.attributeID = 1290 AND
SkillLevel.attributeID = 1288))

不用担心毛毛,这里的主要内容是 SKILLID 。 如果我将 3325 ( 鱼雷的标识) 放置在SKILLID位置,它将返回:


+---------+----------------------------+
| SkillID | Skill |
+---------+----------------------------+
| 3319 | Missile Launcher Operation |
| 3324 | Heavy Missiles |
+---------+----------------------------+

如果我放置 3324 ( 重导弹 ID ),它将返回:


+---------+----------------------------+
| SkillID | Skill |
+---------+----------------------------+
| 3321 | Standard Missiles |
+---------+----------------------------+

所以,基本上,我需要遍历这些查询,并在新查询中使用SkillID的前一个结果。 我还需要一个新的列 parent 来指定哪一个skillID是行的父列。

问题是,我不知道在存储过程中我所做的是什么。 我已经阅读过了,但我还不知道怎么做。

我可以通过PHP和几个SQL查询轻松地做到这一点,但是我想尝试我的步骤来改变速度。 谁能让我在这里 start? = )


更新 1

我有这个函数,但它显示了一个错误:


#1172 - Result consisted of more than one row

使用这里查询时:


SELECT test2(typeID) AS id, @level AS level
FROM (
 SELECT @start_with := 3325,
 @id := @start_with,
 @level := 0
) vars, dgmtypeattributes
WHERE @id IS NOT NULL

以下是函数:


DROP FUNCTION IF EXISTS test2;
CREATE FUNCTION test2(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
 DECLARE _id INT;
 DECLARE _parent INT;
 DECLARE _next INT;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

 SET _parent = @id;
 SET _id = -1;

 IF @id IS NULL THEN
 RETURN NULL;
 END IF;

 LOOP
 SELECT 
 MIN(valueInt) AS id, CONCAT(@path, ',', MIN(valueInt))
 INTO @id, @path
 FROM `dgmtypeattributes` 
 WHERE 
 typeID = _parent
 AND attributeID> 181 
 AND attributeID <185
 AND valueInt> _id;

 IF @id IS NOT NULL OR _parent = @start_with THEN
 SET @level = @level + 1;
 RETURN @id;
 END IF;
 SET @level := @level - 1;

 SELECT _parent, SUBSTRING_INDEX(@path, ',', -1)
 INTO _id, _parent
 FROM `dgmtypeattributes`
 WHERE typeID = _parent;
 END LOOP;
END

我相信第二个选择是以 SELECT _parent, SUBSTRING_INDEX(@path, ',', -1) 是返回多行的。 很可能我没有提供正确的查询,并把我的值混淆起来。 第二个选择- 什么是应该返回的?

表解释

这里外,由于很难使用虚拟数据,这里是我使用( 真正的数据 table 和一个 table,它使用名字来链接 id )的两个表的链接: http://www.2shared.com/file/17uFmKXc/sqlFile.html

我不希望任何人做数据的头或者尾。 大多数是额外的东西,甚至没有关于手工( 例如它包含的invTypes table --不仅包含'技能的问题。

Quick: dgmtypeattributes 是一个 table,它定义游戏中各种项目的属性。 这些属性包括使用所述项所需的'必备软件'技能,这些属性id为 182 - 184. 因此,如果在typeID中搜索带有属性范围从 182到 184的3325,则返回:


mysql> SELECT * FROM `dgmtypeattributes` WHERE typeID = 3325 AND attributeID> 181 AND attributeID <185;
+--------+-------------+----------+------------+
| typeID | attributeID | valueInt | valueFloat |
+--------+-------------+----------+------------+
| 3325 | 182 | 3319 | NULL |
| 3325 | 183 | 3324 | NULL |
+--------+-------------+----------+------------+
2 rows in set (0.00 sec)

valueInt下的两个值是prereq技能( 导弹发射操作和重导弹)的ID 。


解决方案

今天一直在玩,我觉得我最终发现了。 我基本上必须写出整个函数的流程,因为MySQL实际上没有提供任何方法来调试这些东西=/

我还在改进但这是我目前所做


CREATE FUNCTION test2(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN


 DECLARE _id INT;
 DECLARE _parent INT;
 DECLARE _next INT;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

 SET _parent = @id;
 SET _id = -1;

 IF @id IS NULL THEN
 RETURN NULL;
 END IF;

 LOOP
 SELECT 
 MIN(valueInt) AS id, IF(MIN(valueInt), CONCAT(@path, ',', _parent), @path)
 INTO @id, @path
 FROM `dgmtypeattributes` 
 WHERE 
 typeID = _parent
 AND attributeID> 181 
 AND attributeID <185
 AND valueInt> _id;

 IF @id IS NOT NULL OR _parent = @start_with THEN
 SET @level = @level + 1;
 SET @parent = _parent;
 RETURN @id;
 END IF;

 IF @path = '' THEN
 RETURN NULL;
 END IF;

 SET @level := @level - 1;

 SELECT _parent, SUBSTRING_INDEX(@path, ',', -1), SUBSTRING(@path, 1, (LENGTH(@path)-(LENGTH(SUBSTRING_INDEX(@path, ',', -1)) +1)))
 INTO _id, _parent, @path;
 END LOOP;
END

结果:


SELECT test2(typeID) AS id, @level AS level
FROM (
 SELECT 
 @start_with := 3325,
 @id := @start_with,
 @level := 0,
 @path := ''
) vars,dgmtypeattributes
WHERE @id IS NOT NULL

+------+-------+
| id | level |
+------+-------+
| 3319 | 1 |
| 3324 | 1 |
| 3319 | 2 |
| 3321 | 2 |
| 3319 | 3 |
| NULL | 1 |
+------+-------+
6 rows in set (0.09 sec)

再一次,一些事情需要调整( 空返回时返回,而我需要包含父列),但我所有的工作都是 !

时间: 原作者:

0 0

通常,你可以使用本文中介绍的方法:

请记住,先决条件是( 直观计数器) 子级,即 heavy missilesmissile launchers 都是 torpedoes的子级,而不是父级。

但是,你的模型有两个问题:

首先,你在两个表( 。itemsdgmtypeattributes 似乎是一个 EAV ) 中有你的关系。

这不是一个大问题,可以轻松解决。 Just


SELECT MIN(id)
INTO @id
FROM t_hierarchy
WHERE parent = _parent
 AND id> _id;

使用一个返回"。id的第一个子元素比 _id 大,以 id 顺序"的等价查询。

其次,你的血统不是树,那是一个项目可以有多个。

这是一个问题,因为过程并不保持递归堆栈,而只是在两个方向上遍历树。 由于可以有多个方向,因此等价于以下查询:


SELECT id, parent
INTO _id, _parent
FROM t_hierarchy
WHERE id = _parent;

不知道跟踪( 是指向 277 或者 278 或者其他属性中存储这里项的父项)的方向?

但是,可以通过将实际的递归路径存储在逗号分隔的会话变量中来重写过程。 只需将查询重写为如下所示:


SELECT MIN(id), CONCAT(@path, ',', MIN(id))
INTO @id, @path
FROM t_hierarchy
WHERE parent = _parent
 AND id> _id;

,并将第二个查询( 选择父对象) 替换为


SELECT _parent, SUBSTRING_INDEX(@path, ',', -1)
INTO _id, _parent

原作者:
0 0

代替存储父标识,尝试将树表示为嵌套集。 给它一个左和右的值。 现在你有一个有两个孩子B 和C的节点A,并且C 有两个孩子,D 和E 。 现在想象一下,访问每个节点两次并分配数字,一次向左,一次向右。 当你往右走,击中树枝,往下走。 叶节点得到两个连续编号。 然后再回到 root 直到你第二次击中。

left:1 右:8 B left:2 右:7 C left:3 右:4 D left:5 右:6

现在,为了找到一个叶,你只需要找到一个right-left=1的节点。 查找>的祖先,查找左 right-1 和左 <D.left 和右> D.right的节点


CREATE PROCEDURE getancestors (@nodename NVARCHAR(50))
 BEGIN
 SELECT nodeID, left, right
 FROM mytree
 WHERE left> right-1 AND left <(SELECT left FROM mytree WHERE nodeID=@nodename) 
 AND right> (SELECT right FROM mytree WHERE nodeID=@nodename) 
 ORDER BY left;
 END;

这将给你从父节点到你的节点的结果。 要从你的节点提升树,ORDER BY 右。 只需记住,在嵌套集合中,子值的所有左值都大于和右值小于父值。 这种技术的唯一问题是,如果你需要频繁插入。

假设上面的例子,要获取D的祖先,调用它作为:


CALL getancestors("D");

祝你好运和愉快;-}

...